March 29, 2016 at 1:40 pm
Hello All,
I thought I'd share my solution to this problem after numerous searches on Google and not finding a solution to fit my particular needs.
Summary: We are not using contained databases (haven't even dug into this topic). We have two SQL servers, I'll call SQLA (Primary) and SQLB (Secondary) in an AlwaysOnAvailability Group cluster, called SQLCluster1. As we add databases to the cluster, we have SQL logins that need to be maintained between the two SQL servers. When we add our customer databases, I have a powershell script that automatically provisions the database and adds it to my SQLCluster1 availability group.
Problem: If you create a SQL login on SQLA, let's call it "sqluser", and then also create the same SQL login on SQLB, they will not be the same (they may appear to be). When we perform a failover to SQLB, the failed over database(s) that uses our "sqluser" SQL login would have permission issues when trying to authenticate. Why? Remember when I said they weren't the same, even though they appear to be?
If you run
use master;
select * from syslogins
against both SQLA and SQLB, and search for our "sqluser" under the name column, you'll notice that their sid values are completely different.
This problem is explained in more details in the links below:
https://www.sqlskills.com/blogs/jonathan/synchronize-availability-group-logins-and-jobs/
http://dba.stackexchange.com/questions/29903/logins-arent-syncing-across-availability-groups
My Resolution to this issue:
I used the solution provided by Microsoft in this article https://support.microsoft.com/en-us/kb/918992. It works nicely, because it iterates through all the SQL logins on SQLA and generates the CREATE statements I can run on SQLB to create the exact same SQL login with the exact same SID and Password . This is nice, but I wanted to make this somewhat automatic, so I simply modified some of the logic in the sp_help_revlogin.
Here's what I did:
As a prerequisite, I went to each SQL server and added the other SQL replica as a linked server named "PARTNER", which makes it easier to reuse code that referenced the other SQL instance.
I replaced the logic in sp_help_revlogin where it starts the IF (@login_name IS NULL). I commented out the original code and then put my own code in it's place. What this does is filters down the SQL logins I'm looking for to ONLY SQL logins. The original code also included Windows Logins and disabled logins, but I wasn't concerned with those, so I wanted to filter things down.
First, you'll want to make sure to declare a variable to hold some dynamic SQL, which will be used towards the end when we execute dynamic SQL against our PARTNER
DECLARE @SqlStmt varchar (MAX)
/*********************************************
Added the logic for the PARTNER linked server
**********************************************/
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM sys.syslogins as l LEFT JOIN
sys.server_principals as p ON
( l.name = p.name )
LEFT JOIN PARTNER.master.sys.syslogins as r
ON l.name=r.name
WHERE
p.type = 'S' AND p.name <> 'sa' AND is_disabled <> 1 AND
r.name IS NULL AND r.sid IS NULL AND l.isntuser <> 1 AND l.isntgroup <> 1
At the very end of the script, where it performs the last PRINT @tmpstr r, I changed the code to build dynamic SQL into a variable, and then executed that dynamnic SQL, targeted towards my "PARTNER" linked server.
SET @SqlStmt= 'USE master;' + @tmpstr
--execute the CREATE statement against our PARTNER, which is the secondary replica server.
exec (@SqlStmt) AT PARTNER
The end result?
What this does is allows me to execute the sp_help_revlogin stored procedure during my powershell provisioning script. The stored procedure has been modified to search for SQL logins that exist on SQLA but don't have an exact match on SQLB (based on name AND/OR SID), generate the CREATE T-SQL, and then execute the T-SQL against SQLB (our PARTNER from SQLA's perspective). At the end we would have our SQL logins on both SQLA and SQLB, and when we have a failover, we won't have any issues with permissions because the EXACT same SQL login now exists on both SQL servers (identical Names, SIDs, and Passwords).
What this doesn't do:
Unfortunately I haven't added the logic to check first to see if the SQL login may already exist (by name ) before I run my CREATE T-SQL. So this solution assumes that the SQL login simply just doesn't exist on SQLB.
March 29, 2016 at 6:16 pm
kyyo757 (3/29/2016)
Hello All,I thought I'd share my solution to this problem after numerous searches on Google and not finding a solution to fit my particular needs.
Summary: We are not using contained databases (haven't even dug into this topic). We have two SQL servers, I'll call SQLA (Primary) and SQLB (Secondary) in an AlwaysOnAvailability Group cluster, called SQLCluster1. As we add databases to the cluster, we have SQL logins that need to be maintained between the two SQL servers. When we add our customer databases, I have a powershell script that automatically provisions the database and adds it to my SQLCluster1 availability group.
Problem: If you create a SQL login on SQLA, let's call it "sqluser", and then also create the same SQL login on SQLB, they will not be the same (they may appear to be). When we perform a failover to SQLB, the failed over database(s) that uses our "sqluser" SQL login would have permission issues when trying to authenticate. Why? Remember when I said they weren't the same, even though they appear to be?
If you run
use master;select * from syslogins
against both SQLA and SQLB, and search for our "sqluser" under the name column, you'll notice that their sid values are completely different.
This problem is explained in more details in the links below:
https://www.sqlskills.com/blogs/jonathan/synchronize-availability-group-logins-and-jobs/
http://dba.stackexchange.com/questions/29903/logins-arent-syncing-across-availability-groups
My Resolution to this issue:
I used the solution provided by Microsoft in this article https://support.microsoft.com/en-us/kb/918992. It works nicely, because it iterates through all the SQL logins on SQLA and generates the CREATE statements I can run on SQLB to create the exact same SQL login with the exact same SID and Password . This is nice, but I wanted to make this somewhat automatic, so I simply modified some of the logic in the sp_help_revlogin.
Here's what I did:
As a prerequisite, I went to each SQL server and added the other SQL replica as a linked server named "PARTNER", which makes it easier to reuse code that referenced the other SQL instance.
I replaced the logic in sp_help_revlogin where it starts the IF (@login_name IS NULL). I commented out the original code and then put my own code in it's place. What this does is filters down the SQL logins I'm looking for to ONLY SQL logins. The original code also included Windows Logins and disabled logins, but I wasn't concerned with those, so I wanted to filter things down.
First, you'll want to make sure to declare a variable to hold some dynamic SQL, which will be used towards the end when we execute dynamic SQL against our PARTNER
DECLARE @SqlStmt varchar (MAX)
/*********************************************
Added the logic for the PARTNER linked server
**********************************************/
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM sys.syslogins as l LEFT JOIN
sys.server_principals as p ON
( l.name = p.name )
LEFT JOIN PARTNER.master.sys.syslogins as r
ON l.name=r.name
WHERE
p.type = 'S' AND p.name <> 'sa' AND is_disabled <> 1 AND
r.name IS NULL AND r.sid IS NULL AND l.isntuser <> 1 AND l.isntgroup <> 1
At the very end of the script, where it performs the last PRINT @tmpstr r, I changed the code to build dynamic SQL into a variable, and then executed that dynamnic SQL, targeted towards my "PARTNER" linked server.
SET @SqlStmt= 'USE master;' + @tmpstr
--execute the CREATE statement against our PARTNER, which is the secondary replica server.
exec (@SqlStmt) AT PARTNER
The end result?
What this does is allows me to execute the sp_help_revlogin stored procedure during my powershell provisioning script. The stored procedure has been modified to search for SQL logins that exist on SQLA but don't have an exact match on SQLB (based on name AND/OR SID), generate the CREATE T-SQL, and then execute the T-SQL against SQLB (our PARTNER from SQLA's perspective). At the end we would have our SQL logins on both SQLA and SQLB, and when we have a failover, we won't have any issues with permissions because the EXACT same SQL login now exists on both SQL servers (identical Names, SIDs, and Passwords).
What this doesn't do:
Unfortunately I haven't added the logic to check first to see if the SQL login may already exist (by name ) before I run my CREATE T-SQL. So this solution assumes that the SQL login simply just doesn't exist on SQLB.
Nice :). We have a job that is deployed on all the servers and will email us along with a script if the logins or jobs are out of sync.
April 2, 2016 at 12:04 pm
kyyo757 (3/29/2016)
Problem: If you create a SQL login on SQLA, let's call it "sqluser", and then also create the same SQL login on SQLB, they will not be the same (they may appear to be).
create the login on SQLA and then script it out using this script below which you then apply to SQLB
SELECT'CREATE LOGIN [' + name + '] WITH PASSWORD = ' +
sys.fn_varbintohexstr(password_hash) +
' HASHED, SID = ' + sys.fn_varbintohexstr(sid) +
', DEFAULT_DATABASE = ' + QUOTENAME(default_database_name) +
', DEFAULT_LANGUAGE = ' + default_language_name +
', CHECK_EXPIRATION = ' +
CASE
WHEN is_expiration_checked = 0 THEN 'OFF'
ELSE 'ON'
END +
', CHECK_POLICY = ' +
CASE
WHEN is_policy_checked = 0 THEN 'OFF'
ELSE 'ON'
END +
CASE is_disabled
WHEN 0 THEN ''
ELSE '; ALTER LOGIN [' + name + '][ DISABLE;'
END
FROM master.sys.sql_logins
WHERE name = 'theloginname'
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply