October 17, 2014 at 11:37 am
Hi,
I am not sure if I designed the AlwaysOn wrong, but every time we fail over from primary server to another server, my applications cannot connect because the sql logins cannot connect to their default databases. Once I run the command to link the login with the user in the default database then the users are able to connect. Did I do something wrong when designing AlwaysOn? If not, how would I handle this problem?
October 17, 2014 at 1:03 pm
shahgols (10/17/2014)
Hi,I am not sure if I designed the AlwaysOn wrong, but every time we fail over from primary server to another server, my applications cannot connect because the sql logins cannot connect to their default databases. Once I run the command to link the login with the user in the default database then the users are able to connect. Did I do something wrong when designing AlwaysOn? If not, how would I handle this problem?
Have you set up a listener for the AlwaysOn group?
have you synchronised all logins from the primary replica to any secondary replicas?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 17, 2014 at 2:26 pm
You need to create logins on all secondaries with the same SID as the primary logins have. You can find various means of doing this online.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 17, 2014 at 2:51 pm
Logins are set at instance level. So you may need to add logins manually to secondary instance. otherwise you may need to use contained databases
October 17, 2014 at 3:07 pm
Unfortunately SQL Server authentication does not work with Always-On. The accounts have Security identifiers(SID's) that are unique to each server.
Even if you've created the account on both sides and given it the same password, you will still need to run sp_change_user_login every time it fails over.
The correct way around this to use either a contained database or AD authentication. Hope that helps!
October 17, 2014 at 3:25 pm
Ozzmodiar (10/17/2014)
Unfortunately SQL Server authentication does not work with Always-On. The accounts have Security identifiers(SID's) that are unique to each server.Even if you've created the account on both sides and given it the same password, you will still need to run sp_change_user_login every time it fails over.
The correct way around this to use either a contained database or AD authentication. Hope that helps!
You have one thing correct - SIDs will be different if you create the same login on multiple servers.
But as I said, you can create logins with the SAME SID as the primary. Here is code that can do this. Use at your own risk:
SELECT
'create login [' + p.name + '] ' +
case when p.type in('U','G') then 'from windows ' else '' end +
'with ' +
case when p.type = 'S' then 'password = ' + master.sys.fn_varbintohexstr(l.password_hash) + ' hashed, ' +
'sid = ' + master.sys.fn_varbintohexstr(l.sid) +
', check_expiration = ' + case when l.is_expiration_checked > 0 then 'ON, ' else 'OFF, ' end +
'check_policy = ' + case when l.is_policy_checked > 0 then 'ON, ' else 'OFF, ' end +
case when l.credential_id > 0 then 'credential = ' + c.name + ', ' else '' end
else '' end +
'default_database = ' + p.default_database_name +
case when len(p.default_language_name) > 0 then ', default_language = ' + p.default_language_name else '' end
FROM sys.server_principals p
LEFT JOIN sys.sql_logins l ON p.principal_id = l.principal_id
LEFT JOIN sys.credentials c ON l.credential_id = c.credential_id
WHERE p.type in('S','U','G')
AND p.name <> 'sa'
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 17, 2014 at 3:33 pm
Script the sql logins from the primary and synch the logins on the secondary
October 17, 2014 at 4:12 pm
The script kevin has posted is exactly the script i would use,mit will retain the logins sid and hashed password. The only thing it doesnt extract are server role memberships
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 17, 2014 at 4:24 pm
I'm curious Kevin,
What would be the advantage to this as opposed to a contained database?
Thanks!
October 17, 2014 at 7:29 pm
Ozzmodiar (10/17/2014)
I'm curious Kevin,What would be the advantage to this as opposed to a contained database?
Thanks!
No one uses them.
They are half-baked.
Significant caveats, limitations, provisos, gotchas, etc.
The better question is why do you think you need them. What problem(s) are you solving with their use?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 18, 2014 at 1:32 am
Ozzmodiar (10/17/2014)
I'm curious Kevin,What would be the advantage to this as opposed to a contained database?
Thanks!
The only real advantage is that the databases are easier to move as they have no dependency on the instance where they are located, or at least they shouldn't have. Remember these are partiallly contained databases not fully contained. Partially contained databases can still reference objects outside of the database boundary.
Now in your situation you're using AlwaysOn groups, contained databases are not suitable here as there is a dependency on the instance where the database resides.
The problem you're having is with unsynced logins and the SID issue only affects sql accounts. Its an age old issue thats easily sorted once you understand why. To recap
On instance a you create a sql server login called bob with password smith, the server generates a random sid and you map this login into a database called fred.
You create a login on server b called bob with password smith and this instance generates a random sid for the account.
You now move fred to instance b but the login bob on instance b cannot access fred. The sids do not match. The following simple query would cure this
use fred
Go
Alter user [bob] with login = [bob]
Go
HTH, Post back if you're still stuck, but the script kevin supplied is good
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 20, 2014 at 6:29 pm
TheSQLGuru, you are the man, that is the correct answer! tHANK YOU!
October 20, 2014 at 6:58 pm
Glad I could help!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 20, 2014 at 7:19 pm
shahgols (10/20/2014)
TheSQLGuru, you are the man, that is the correct answer! tHANK YOU!
Have you even bothered to read my replies??
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 21, 2014 at 11:55 am
Perry Whittle (10/20/2014)
shahgols (10/20/2014)
TheSQLGuru, you are the man, that is the correct answer! tHANK YOU!Have you even bothered to read my replies??
Yes I have sir, why? Did I miss something?
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply