SQL Accounts in Always on Availability Group

Question

Post reply

SQL Accounts in Always on Availability Group

Wecks

Right there with Babe

Points: 776
More actions
February 18, 2021 at 10:12 pm
Go to Answer
#3846983

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.

Jeffrey Williams

SSC Guru

Points: 90252

February 20, 2021 at 3:19 pm

Answer

#3847742

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs