I have built an Availability Group. The application connects to the listener however it utilizes a SQL account to function.
I want to make it so that when the server fails the SQL Srverice account will function on the secondary.
What is the best way to make this happen? I can script the logins but they will have different SIDS I believe. if anyone has a way for me to do any useful scripts to help me do this that would be great.
February 19, 2021 at 11:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
Script the login from the primary - with the SID - and create the login on the secondary with that SID. I use SQLCMD mode to create the login and add that login to the database(s), here is an example:
:connect {your primary server here}
:out C:\Temp\LoginSID.sql
:setvar Login {your login here}
:setvar newPassword ^%$#@!NotNeededAccountAlreadyExists+_)(*&
:setvar uniquePassword +_)(*&ThisLoginWillNeverBeUsed!@#$%^&*()
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
--==== 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 {secondary 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
Go
Now - the login on both systems will match and the user in the databases will tied to the login. This shows how to create the login on the primary and secondary at the same time - but if you already have a login it will skip the creation, pull the SID from the primary and execute that script on the secondary.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
February 21, 2021 at 12:55 am
This is exactly what I am after.
Thanks.
February 21, 2021 at 1:05 am
Thank you for the feedback, happy to help
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply