When changing a role of the Administration Service in Active Roles replication, the operation fails with the following error: "An alias cannot be used for replication. Use the name of the SQL Server instance."
MORE INFORMATION
This error message may appear due to the following reasons:
CAUSE 1. Loss of information which occurred in the system tables on the SQL Server.
CAUSE 2. The computer hosting the SQL Server has been renamed.
CAUSE 3. Alias has been used to connect to the SQL Server.
DIAGNOSTICS
To determine the actual cause of the error
1. Start SQL Server Management Studio and connect to the appropriate server.
2. Run the following tSQL statements
select @@servername
select serverproperty('ServerName')
NOTE: These statements require no substitutions and must be worded exactly as specified here.
3. The following results are possible:
* The first statement returns NULL. This means that the reason of the error message is: Cause 1.
* Both statements return different values and not NULLs. This means that the reason of the error message is: Cause 2.
* Both statements return the same values and not NULLs. This means that the reason of the error message is: Cause 3.
RESOLUTION
To fix Cause 1
1. Start SQL Query Analyzer and connect to the appropriate server.
2. Select the Master database.
3. Run the following Transact-SQL statements
declare @sn sysname
select @sn = cast(serverproperty('ServerName') as sysname)
exec sp_addserver @sn, 'local'
4. Restart the MSSQLSERVER service-for default instance, or the MSSQL$INSNAME service-for named instance.
5. Run the following Transact-SQL statements
select @@servername
select serverproperty('ServerName')
NOTE, Make sure that both statements return the same value and not NULL.
To fix Cause 2
1. Start SQL Query Analyzer and connect to the appropriate server.
2. Select the Master database.
3. Run the following Transact-SQL statements
exec sp_dropserver @@servername, 'droplogins'
declare @sn sysname
select @sn = cast(serverproperty('ServerName') as sysname)
exec sp_addserver @sn, 'local'
4. Restart the MSSQLSERVER service-for default instance, or the MSSQL$INSNAME service-for named instance.
5. Run the following Transact-SQL statements
select @@servername
select serverproperty('ServerName')
NOTE, Make sure that both statements return the same value and not NULL.
To fix Cause 3
* Do not use any aliases to connect to the SQL Server.
For more information on the Cause 3, see "When installing Administration Service, you must identify SQL Server by short NetBIOS name" (Solution 10803, https://support.quest.com/activeroles-server/kb/10803)
NOTE:
Upon running the following;
declare @sn sysname
select @sn = cast(serverproperty('ServerName') as sysname)
exec sp_addserver @sn, 'local'
... if you receive the following message similar to this;
Msg 15028, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 89 The server '{SERVERNAME.EN}' already exists.
Where SERVERNAME is the name of your SQL server
.... you are still required to restart the server as restarting SQL is mandatory when using the 'sp_addserver'
© 2021 One Identity LLC. ALL RIGHTS RESERVED. Feedback Terms of Use Privacy