AWS RDS Custom Server @@Servername and serverproperty('Servername') differ

I came across two customer issues like this for which the SQL Server Documentation has an answer. An error you might be getting (for example when setting up distribution) or SSIS, other services.

Could not connect to server 'RDSAMAZ-XXXXXXX' because '<user>' is not defined as a remote login at the server

Follow this [1] where is explains that we created the RDS Custom SQL Server from an AMI (hard disk image of another computer). The new instance created from the AMI has a different name, however the imaged computer is retained in the new installation (such as network name).

-- Use the Master database
USE master
GO

-- Declare local variables
DECLARE @serverproperty_servername varchar(100),
@servername varchar(100);

-- Get the value returned by the SERVERPROPERTY system function
SELECT @serverproperty_servername = CONVERT(varchar(100), SERVERPROPERTY('ServerName'));

-- Get the value returned by @@SERVERNAME global variable
SELECT @servername = CONVERT(varchar(100), @@SERVERNAME);

-- Drop the server with incorrect name
EXEC sp_dropserver @server=@servername;

-- Add the correct server as a local server
EXEC sp_addserver @server=@serverproperty_servername, @local='local';`

#### Restart the computer

Verify the operation was successful:
`SELECT @@SERVERNAME, SERVERPROPERTY('ServerName');

[1] https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-18483-database-engine-error?view=sql-server-ver16

[+] https://learn.microsoft.com/en-us/answers/questions/498598/invalid-urn-filter-on-server-level-sql-server-erro

Hi jReese.

Per Microsoft's documentation, using SERVERPROPERTY to return Servername will return both the Windows server and instance information associated with a specified instance of SQL Server.

Using @@servername will return the name of the local server that is running SQL Server. Therefore, you are correct, it will be the name of the RDS Custom instance.

You can rename a DB instance. Per the documentation:

You can rename a DB instance by using the AWS Management Console, the AWS CLI modify-db-instance command, or the Amazon RDS API ModifyDBInstance action. Renaming a DB instance can have far-reaching effects. The following is a list of considerations before you rename a DB instance.

• When you rename a DB instance, the endpoint for the DB instance changes, because the URL includes the name you assigned to the DB instance. You should always redirect traffic from the old URL to the new one.
• When you rename a DB instance, the old DNS name that was used by the DB instance is immediately deleted, although it could remain cached for a few minutes. The new DNS name for the renamed DB instance becomes effective in about 10 minutes. The renamed DB instance is not available until the new name becomes effective.
• You cannot use an existing DB instance name when renaming an instance.
• All read replicas associated with a DB instance remain associated with that instance after it is renamed. For example, suppose you have a DB instance that serves your production database and the instance has several associated read replicas. If you rename the DB instance and then replace it in the production environment with a DB snapshot, the DB instance that you renamed will still have the read replicas associated with it.
• Metrics and events associated with the name of a DB instance are maintained if you reuse a DB instance name. For example, if you promote a read replica and rename it to be the name of the previous primary DB instance, the events and metrics associated with the primary DB instance are associated with the renamed instance.
• DB instance tags remain with the DB instance, regardless of renaming.
• DB snapshots are retained for a renamed DB instance.

I hope this helps.