September 14, 2017 at 9:31 am
We recently had a network issue and the availability groups failed over from the primary to the secondary. The next thing that happened was the company website went down.
We had a quick meeting to see what happened. That is when we found the network issue. I also saw that the databases were up but access was being denied.
Error message
The target database, 'epiCommerce', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access.
Problem
Logins from the primary weren't on the secondary. When the failover occurred, there were logins that could not access the database.
Question
What is the best way to keep the logins synchronized between the primary and secondary replica?
1. What I am doing is to remove the databases from the availability group.
2. Script out logins and passwords from the primary replica and copy them to the secondary replica.
3. Backup the database from the primary.
4. Backup the log from the primary.
5. Restore the database on the secondary with norecovery.
6. Restore the log on the secondary with norecovery.
7. Add the databases back to the availability group.
I just want to insure this is the only way to keep the users, logins and mappings synchronized.
Thanks.
Glenn
Things will work out. Get back up, change some parameters and recode.
September 14, 2017 at 9:44 am
Look at "sp_help_revlogin", its a script to download from MS, it will create a output for all the logins on one machine with the passwords hashed and the SID's so when you failover the login just works.
You need to run it at regular intervals and replay the output on the other nodes to keep in sync.
We did this with a trigger and AOAGs syncing a "Login" database which just had an output of the revlogin sproc in 1 table and it just looped through that table at regular intervals.
September 14, 2017 at 9:51 am
This won't keep logins sychronized.
Logins are an instance level object. Users and the rest of objects in an AG are database level objects. AGs keep databases in sync. They do not sync logins, jobs, linked servers, or any other instance level feature.
The only way to do this is when you add a login to the primary, you also add it the secondary. You can do this manually, use a process that adds to both, or use a process that syncs logins. There are ways to script the login from the primary and ensure it has the same SID on the secondary so the users map correctly.
There are a few articles:
http://www.sqlservercentral.com/articles/Disaster+Recovery+(DR)/71992/
http://www.sqlservercentral.com/articles/63028/
http://www.sqlservercentral.com/articles/PowerShell/145244/
September 14, 2017 at 10:41 am
I really appreciate both responses. The logins to a table is an interesting approach. I would like to hear more about how you go from a table to creating the logins on server #2.
Thanks also for the articles which share login creation.
However, the big problem is that you can't create a login and map it to a database on the secondary replica when that database is part of an availability group. You have to remove the database first from the availability group. Create the login and map it. Then you have to add the database back to the availability group.
This makes it hard to keep the primary and secondary replicas in sync with users/logins and mappings.
Thanks.
Things will work out. Get back up, change some parameters and recode.
September 14, 2017 at 11:17 am
WebTechie - Thursday, September 14, 2017 10:41 AMI really appreciate both responses. The logins to a table is an interesting approach. I would like to hear more about how you go from a table to creating the logins on server #2.
Was done via a DDL trigger for create login, which called sp_help_revlogin based on the created user. Which inturn wrote the output of sp_help_revlogin to a database which was synced between the nodes. Each node had a job to loop through the entries and replay the command on their node. Don't have the script as it was at a secure client I as working for, but that's the jist of the idea.
If failover occurred during a period where the job hadn't executed, we could kick off the job manually or select all the entries and replay them in one go. Very rare that happened, as you get into the habit of running sp_help_revlogin every time you create a new SQL based login.
WebTechie - Thursday, September 14, 2017 10:41 AMHowever, the big problem is that you can't create a login and map it to a database on the secondary replica when that database is part of an availability group. You have to remove the database first from the availability group. Create the login and map it. Then you have to add the database back to the availability group.
That's where sp_help_revlogin comes in handy, as it creates the logins with the same SID's and thus doesn't orphan the user, so no need to keep adding and removing from the AG's to map a user.
September 14, 2017 at 12:14 pm
That sounds awesome.
So you put in output into a column? What datatype? Varchar(max) I would assume.
Then you add that row to a "DBA" database that is synchronized?
Then I guess you take read from that row's value on the secondary replica and execute the login code.
And I can run this without removing the database from the AG?! Cool!
I am going to try this.
Thanks to the tip.
Things will work out. Get back up, change some parameters and recode.
September 14, 2017 at 12:24 pm
WebTechie - Thursday, September 14, 2017 10:41 AMHowever, the big problem is that you can't create a login and map it to a database on the secondary replica when that database is part of an availability group. .
You don't need to.
The user's already in the database, it's a database object and hence it's synced with the AG. All you need to do is create the login with the same SID as on the primary replica, which sp_help_revlogin does.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 14, 2017 at 12:49 pm
As Gail mentioned, you don't need to.
When you add a login to the primary instance in the master database, you create a user in the primary database replica. This gets transferred to the secondary replica(s) in the AG. On the secondary instance, this user is orphaned without a login.
If you create the login on the secondary instance (in master ) with the same SID, if you failover, the user maps to the login automatically.
September 14, 2017 at 1:01 pm
Thanks guys for the class today. I honestly appreciate it and I've learned a little more about implementing HAG solutions.
I see my problem has been the SID of the logins. Now that you mention it, it makes total sense why I was having that issue. Everything else is working fine. I am even created a linked server to the listener of the AG. I just need to implement the strategy from today's discussion.
Thanks again.
Things will work out. Get back up, change some parameters and recode.
September 14, 2017 at 1:32 pm
Best of luck and let us know if you have more issues.
BTW, I have seen people build a sync process that queries sp_help_revlogin, scripts new logins, runs on the server. You can have this running on replicas, connecting to the primary instance and checking master.
September 15, 2017 at 5:12 am
WebTechie - Thursday, September 14, 2017 10:41 AMI really appreciate both responses. The logins to a table is an interesting approach. I would like to hear more about how you go from a table to creating the logins on server #2.Thanks also for the articles which share login creation.
However, the big problem is that you can't create a login and map it to a database on the secondary replica when that database is part of an availability group. You have to remove the database first from the availability group. Create the login and map it. Then you have to add the database back to the availability group.
This makes it hard to keep the primary and secondary replicas in sync with users/logins and mappings.
Thanks.
Hi,
I' shure, we created a login and add this login to the database role db_datareader to an database in AOAG, on the 2nd node. I think, you had to do a manuell failover, but you need not to remove the database from AOAG.
Kind regards,
Andreas
September 18, 2017 at 2:05 am
I usually use SSIS to run weekly to copy over new Logins @serverlevel and copy over all jobs, as jobs can be altered on a regular basis on the PRIMARY. I drop and recreate jobs as part of the package and make sure they are disabled on the DR server.
Very easy and simple to set up.
September 18, 2017 at 2:17 am
Super Cat - Monday, September 18, 2017 2:05 AMI usually use SSIS to run weekly to copy over new Logins @serverlevel and copy over all jobs, as jobs can be altered on a regular basis on the PRIMARY. I drop and recreate jobs as part of the package and make sure they are disabled on the DR server.Very easy and simple to set up.
The problem with the "Transfer Login Task" in SSIS is that it creates the login disabled and with a random password. So unless you go in manually afterwards to enable the login and set the password your logins wont work after failover.
That's where sp_help_revlogin comes in handy as it can hash out the correct password and also created the logins enabled, so there are no additional administrative processes that need to happen.
September 19, 2017 at 7:04 am
WebTechie - Thursday, September 14, 2017 9:31 AMQuestion
What is the best way to keep the logins synchronized between the primary and secondary replica?
Use my script, enjoy 😎
SET CONCAT_NULL_YIELDS_NULL OFF
SELECT'CREATE LOGIN [' + sp.name + '] ' +
CASE
WHEN sp.type = 'S' THEN 'WITH PASSWORD = ' + sys.fn_varbintohexstr(sl.password_hash) +
' HASHED, SID = ' + sys.fn_varbintohexstr(sl.sid) + ', '
WHEN sp.type IN ('G', 'U') THEN ' FROM WINDOWS WITH'
END
+
' DEFAULT_DATABASE = ' + QUOTENAME(sp.default_database_name) +
', DEFAULT_LANGUAGE = ' + sp.default_language_name +
CASE sp.type
WHEN 'S' THEN ', CHECK_EXPIRATION = ' +
CASE
WHEN sl.is_expiration_checked = 0 THEN 'OFF'
ELSE 'ON'
END +
', CHECK_POLICY = ' +
CASE
WHEN sl.is_policy_checked = 0 THEN 'OFF'
ELSE 'ON'
END
END +
CASE sp.is_disabled
WHEN 0 THEN ''
ELSE '; ALTER LOGIN [' + sp.name + '] DISABLE;'
END
FROM master.sys.server_principals sp
left outer join sys.sql_logins sl on sp.principal_id = sl.principal_id
WHERE sp.name not like '##%' and sp.name <> 'sa' AND sp.type <> 'R'
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 20, 2017 at 2:53 am
What about using Contained Databases instead to get around this problem? If you set the database Containment property to 'Partial' then you can use contained users, which means there is no need to have a SQL login. I am currently looking into using this for a new AlwaysOn AG we want to set up for one of our systems. There are some gotchas that you have to consider though.
https://blog.sqlrx.com/2015/10/23/contained-databases-pros-and-cons/
Anyone using contained databases with AlwaysOn?
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply