November 4, 2015 at 11:40 am
We have created a SS2014 AlwaysOn environment and are doing the pre-live testing. I'm currently testing logins and have found an issue that I need help resolving.
Background:
Primary server
Two Replicas
We use the replicas for specific business groups to query so they don't impact the Primary.
I created a login on each server.
On the primary, I created the user in a database and granted the user read/write.
That permission copied to the replicas.
I logged into the primary and was able to see the objects in the database.
I logged into one of the replicas and wasn't able to see the database objects unless Public had permission to them (we remove that permission, but I set it on one table for testing).
Researching and the 'trial and error' method showed that the issue is the login's SID
SELECT SUSER_SID('testLogin')
The only way I found to get the SID to match, was to drop the login from each replica, and run a new CREATE LOGIN script that uses the SID from the Primary.
DROP LOGIN testLogin;
GO
CREATE LOGIN testLogin
WITH PASSWORD = '**********', SID = <copied SID>;
That worked. I could see all the objects on all the replicas.
Sounds like I got the solution, so what's the issue?
I have hundreds of logins that need created on each server. Getting the SID for each one from the Primary and creating it on the replicas just isn't efficient. Does anyone have a solution to getting the SID to match on the replicas? The best solution would allow me to create the logins on each server and then synch the SIDs with what is on the Primary.
-SQLBill
November 4, 2015 at 1:56 pm
You could build something around sp_help_revlogin.
https://support.microsoft.com/en-us/kb/918992
It will generate the SID and password for SQL logins.
November 4, 2015 at 2:26 pm
Thanks. I think that's what we are going to have to do for the initial cutover. We can do that manually (run sp_help_revlogin and then run it on each server). I'm going to have to see how I can use that in our automated process that creates logins. But it looks like there is no way to change the SID once the login has been created. I can get the SID from syslogins or sys.server_principals, but ad hoc changes to those tables aren't allowed.
-SQLBill
November 5, 2015 at 4:25 pm
It is a pain that HA wizard does not consider the login mappings re SID
November 5, 2015 at 4:34 pm
i1888 (11/5/2015)
It is a pain that HA wizard does not consider the login mappings re SID
This really wasn't an issue until AlwaysOn. The SID was server-specific which rarely was an issue. However, with AlwaysOn, you can't query the replicas unless the SID matches the Primary. That makes it a big issue now and maybe MS will figure out an easier way to make SIDs match.
-SQLBill
November 6, 2015 at 7:02 am
SQLBill (11/4/2015)
We have created a SS2014 AlwaysOn environment and are doing the pre-live testing. I'm currently testing logins and have found an issue that I need help resolving.Background:
Primary server
Two Replicas
We use the replicas for specific business groups to query so they don't impact the Primary.
I created a login on each server.
On the primary, I created the user in a database and granted the user read/write.
That permission copied to the replicas.
I logged into the primary and was able to see the objects in the database.
I logged into one of the replicas and wasn't able to see the database objects unless Public had permission to them (we remove that permission, but I set it on one table for testing).
Researching and the 'trial and error' method showed that the issue is the login's SID
SELECT SUSER_SID('testLogin')The only way I found to get the SID to match, was to drop the login from each replica, and run a new CREATE LOGIN script that uses the SID from the Primary.
DROP LOGIN testLogin;GO
CREATE LOGIN testLogin
WITH PASSWORD = '**********', SID = <copied SID>;
That worked. I could see all the objects on all the replicas.
Sounds like I got the solution, so what's the issue?
I have hundreds of logins that need created on each server. Getting the SID for each one from the Primary and creating it on the replicas just isn't efficient. Does anyone have a solution to getting the SID to match on the replicas? The best solution would allow me to create the logins on each server and then synch the SIDs with what is on the Primary.
-SQLBill
There are many ways to synchronise logins between partners for mirroring, log shipping and alwayson groups. It's easy to script them out and apply them to partner instances and there are many different scripts available, even one from microsoft
SQLBill (11/5/2015)
This really wasn't an issue until AlwaysOn. The SID was server-specific which rarely was an issue. However, with AlwaysOn, you can't query the replicas unless the SID matches the Primary. That makes it a big issue now and maybe MS will figure out an easier way to make SIDs match.
The SID mismatch has always been an issue, it's not AlwaysOn that has created the issue. Even in database mirroring and log shipping, if you didn't script the logins across to the secondary you would end up with orphaned database users.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 7, 2015 at 4:05 pm
MS has two ways. They are documented in https://msdn.microsoft.com/en-us/library/hh270282.aspx as being contained databases or domain accounts. The difficulty is rooted in the different ways a SID can be generated. For domain accounts, it is the domain's SID. For local Windows accounts, it is the local machine's SID. For SQL Server accounts, a SID is a self-generated one-based enumeration.
July 22, 2016 at 1:36 pm
Agreed this is not an "AlwaysOn" specific problem. We have been using a modified version of sp_help_revlogin from this https://support.microsoft.com/en-us/kb/918992, but we are about to embark upon SQL2014 upgrade for many servers so I just want to know if the scripts generated from that will work on SQL2014? Any other "gotchas"? I think we may need to handle the different HASH method somehow, but not sure how. We don't actually HAVE all the passwords for the SQL Logins, which isn't a problem when going from 2008 to 2008, but will POSSIBLY be when upgrading. Or does the existing hash still work and it just creates the longer one, thereby removing BACKWARD compatablity? I may post this to another post also.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply