I'm a bit rusty with SQL clustering so please bear with me, thanks.
I have a 2-server availabilty group. The other day it failed over during a patching reboot, and a Linked Server connection no longer worked while it was failed over to secondary. Initiating a manual failover back to the original primary resolved the issue but I'm trying to understand the issue so it doesn't happen again.
Server 1 has a SQL Login that is user mapped to a specific database with db_datareader
Server 2 has an identically named Login that is not user-mapped to that specific database
When I try and set the role memberships property for the login properties User Mapping in Server 2 I get a message "Failed to update database "my-dbname" because the database is read-only"
All databases are in state "Synchronized" according to the AG dashboard which is further confusing me as to why these Logins have different permissions
How can I resolve this? thanks.
https://docs.dbatools.io/Sync-DbaAvailabilityGroup
It is a common thing with SQL logins, but unfortunately just "creating" the login on the secondary replicas isn't sufficient enough to make a SQL login work.
The issue is that the SIDs do not match up so you have what is called an "orphaned user" where the SID in the database doesn't match the SID on the server.
There are a number of ways to fix this, but first you will need to delete the login from the secondaries.
Then you can use the "sp_help_revlogin" procedure on the primary replicas, from the first link above, which will script the logins from the primary replica, and then you can copy and paste this into the secondary, ensuring you copy across the SID and hashed password values.
If you are more PowerShell based, you can do this also with DBATools and Sync-DbaAvailabilityGroup which will do more than just the logins.
Only things at the database level will syncronise, stuff at the server level you need to manually do.
So any changes to linked servers, audits, logins etc all need to be done multiple times, which is where DBATools comes in handy to keep all your replicas in sync when things change.
March 8, 2023 at 3:13 pm
Brilliant, thanks. I've left the sp_help_revlogin SP on both servers in case they failover again in future and something else goes wrong. The cluster was built before I joined the company so I'm nervous about changing more than is necessary right now.
I now have the login on both servers, with the same SID and the correct User Mappings pulled in from the DBs
March 8, 2023 at 3:18 pm
Sounds good.
You need to get into a mindset that "If I create a new SQL login, I must run sp_help_revlogin, to script the login for the secondaries", adds an extra step to the process but that is what is needed unortunately.
Now if you move to SQL 2022 in the future you can look at a new featured called "contained availability groups" which takes some of this additional overhead away, but not all.
March 8, 2023 at 8:02 pm
Sounds good.
You need to get into a mindset that "If I create a new SQL login, I must run sp_help_revlogin, to script the login for the secondaries", adds an extra step to the process but that is what is needed unortunately.
Now if you move to SQL 2022 in the future you can look at a new featured called "contained availability groups" which takes some of this additional overhead away, but not all.
If you manage an AG - then it will be well worth the time and effort to script out the creation of SQL logins, or downloading dbatools and using those tools to create and copy logins.
One method is to use SQLCMD mode. Connect to the primary node - create the SQL Login if it doesn't exist, generate the SQL statement to create the login with the SID and password outputting that script to a temporary file. Then - connect to the secondary and execute the generated script. Delete the temporary file when completed.
You can include permissions in this script - something like this:
:connect {Primary Replica}
:out C:\Temp\LoginSID.sql
:setvar Login NewLoginHere
:setvar newPassword ^%$#@!NotNeededAccountAlreadyExists+_)(*&
:setvar uniquePassword +_)(*&ThisLoginWillNeverBeUsed!@#$%^&*()
:setvar databaseRoles ",db_datareader,Execute,View Definition,"
:setvar databaseList ",db1,db2,db3,"
Declare @sqlCommand nvarchar(max);
--==== Create New Login if it doesn't exist
If Not Exists (Select *
From sys.server_principals sp
Where sp.name = '$(Login)')
Begin
Set @sqlCommand = '
Create Login ' + quotename('$(Login)') + '
With Password = ' + quotename('$(uniquePassword)', char(39)) + '
, default_database = master
, check_expiration = Off
, check_policy = On;';
Execute sp_executeSQL @sqlCommand;
End
--==== If the user doesn't exist - create it, add user to specified roles
Use db1;
If Not Exists (Select *
From sys.database_principals dp
Where dp.name = '$(Login)')
Begin
Set @sqlCommand = 'Create User ' + quotename('$(Login)') + ' For Login ' + quotename('$(Login)');
Execute sp_executeSQL @sqlCommand;
End
If Exists (Select *
From sys.database_principals dp
Where dp.name = '$(Login)')
Begin
If charindex('db_datareader', '$(databaseRoles)', 0) > 0 Alter Role db_datareader Add Member [$(Login)];
If charindex('Execute', '$(databaseRoles)', 0) > 0 Grant Execute On Schema::dbo To [$(Login)];
If charindex('View Definition', '$(databaseRoles)', 0) > 0 Grant View Definition To [$(Login)];
End
Go
Use db2;
If charindex(db_name(), '$(databaseList)', 0) > 0
And Not Exists (Select *
From sys.database_principals dp
Where dp.name = '$(Login)')
Begin
Declare @sqlCommand nvarchar(max);
Set @sqlCommand = 'Create User ' + quotename('$(Login)') + ' With default_schema = dbo';
Execute sp_executeSQL @sqlCommand;
End
Go
If Exists (Select *
From sys.database_principals dp
Where dp.name = '$(Login)')
Begin
If charindex('db_datareader', '$(databaseRoles)', 0) > 0 Alter Role db_datareader Add Member [$(Login)];
If charindex('Execute', '$(databaseRoles)', 0) > 0 Grant Execute On Schema::dbo To [$(Login)];
If charindex('View Definition', '$(databaseRoles)', 0) > 0 Grant View Definition To [$(Login)];
End
Go
Use db3;
If charindex(db_name(), '$(databaseList)', 0) > 0
And Not Exists (Select *
From sys.database_principals dp
Where dp.name = '$(Login)')
Begin
Declare @sqlCommand nvarchar(max);
Set @sqlCommand = 'Create User ' + quotename('$(Login)') + ' With default_schema = dbo';
Execute sp_executeSQL @sqlCommand;
End
Go
If Exists (Select *
From sys.database_principals dp
Where dp.name = '$(Login)')
Begin
If charindex('db_datareader', '$(databaseRoles)', 0) > 0 Alter Role db_datareader Add Member [$(Login)];
If charindex('Execute', '$(databaseRoles)', 0) > 0 Grant Execute On Schema::dbo To [$(Login)];
If charindex('View Definition', '$(databaseRoles)', 0) > 0 Grant View Definition To [$(Login)];
End
Go
--==== If SQL login will only be used for read-only access on secondary replica
Use master;
Declare @sqlCommand nvarchar(max) = 'Alter Login ' + quotename('$(Login)') + ' DISABLE;';
Execute sp_executeSQL @sqlCommand;
Go
--==== Grab the Login SID and create the login on the destination - if it doesn't already exist
Set Nocount On;
Declare @LoginSID varbinary(85)
, @sqlCommand nvarchar(max);
Select @LoginSID = sp.[sid]
From sys.server_principals sp
Where sp.name = '$(Login)';
--==== Assume the existing login is correct
Set @sqlCommand = '
Set Nocount On;
If Not Exists (Select *
From sys.server_principals sp
Where sp.name = ' + + quotename('$(Login)', char(39)) + ')
Begin
Create Login ' + quotename('$(Login)') + '
With Password = ' + quotename('$(newPassword)', char(39)) + '
, SID = ' + convert(varchar(85), @LoginSID, 1) + '
, default_database = [master]
, check_expiration = Off
, check_policy = On;
Raiserror(''New Login has been created on %s'', -1, -1, @@servername) With nowait;
End
Declare @LoginSID varbinary(85) = ' + convert(varchar(85), @LoginSID, 1) + ';';
Select @sqlCommand;
Go
:out stdout
:connect {replica here}
:r C:\Temp\LoginSID.sql
Select sp.name
, sp.[sid]
, sp.create_date
, sp.modify_date
, sp.default_database_name
From sys.server_principals sp
Where sp.[sid] = @LoginSID;
Go
!!del c:\temp\LoginSID.sql
GoYou could modify the check for the login on the secondary - and instead just drop the login if it exists and create the login with the correct SID and password.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 10, 2023 at 6:19 am
What was the login error, and on which of the two SQL Server instances was it recorded? The client will only receive error 18456 and no other details, but the server logs the reason the login failed. Check the error logs on both SQL instances to see on which one it failed, and why. You're looking for Error 18456 - "Error: 18456, Severity: 14, State: XX", where XX will be a number that reflects the issue, which can be looked up here:
SQL 2019 will print the full reason on a separate line along with the 18456 info.
I encourage checking the connection from where this Linked Server connection originates to ensure it's targeting the Listener to ensure that the Linked Server connection followed the database.
Eddie Wuerch
MCM: SQL
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply