The Problem:
In your SQL Availability Group environment and you have created a new login. After failover the users reports that they cannot connect to the database with the application. Because the connection to database goes via AG Listener, the application will be redirect to the secondary SQL Server Node after failover of Availability Group (AG). You need to create login also there on secondary server. A lot of administrators forget about it.
The Solution:
We have multiple methods to achieve this, but I choose the best one for me. And ones again many thank to dbatools developers especially to Chrissy LeMaire for this great tool.
If you do not have in your environment dbatools already you can download it from official repo. Here you can find how to do it.
The simple version.
$AGListener = 'AG-Name'
$primaryReplica = Get-DbaAgReplica -SqlInstance $
AGListener
| Where Role -eq Primary
$secondaryReplicas = Get-DbaAgReplica -SqlInstance $
AGListener
| Where Role -eq Secondary
$LoginsOnPrimary = (Get-DbaLogin -SqlInstance $primaryReplica.Name)
$secondaryReplicas | ForEach-Object {
$LoginsOnSecondary = (Get-DbaLogin -SqlInstance $_.Name)
$diff = $LoginsOnPrimary | Where-Object Name -notin ($LoginsOnSecondary.Name)
if($diff) {
Copy-DbaLogin -Source $primaryReplica.Name -Destination $_.Name -Login ($diff.Name)
}
}
But what if you have multiple instances and you will do it with one job for example.
Steven Naudet gave me a hint which I’m using, but I have to extend this solution a little bit. The entire article here.
If you are using Central Management Server for SQL you can use this function from dbatools – Get-DbaRegisteredServer. How to create CMS? See here
So I have to decide create a Job on the register server to sync all login for each Node with one job on my entire AG SQL environment.
- Because I am using different service account on each SQL Instances (one account pro AG) I have to create a sql credential and then use a proxy account for SQL Agent.
/*
Create sql credential
*/
USE master
GO
CREATE CREDENTIAL ProxyUserCredential
WITH IDENTITY = 'domain\user'
, SECRET = 'YourStrongPa$$w0rd'
GO
/*
Add credentail to SQL Agent proxy
*/
USE msdb
GO
EXEC msdb.dbo.sp_add_proxy
@proxy_name = 'WinUserProxy'
, @credential_name = 'ProxyUserCredential'
, @enabled = 1 ;
GO
/*
Grant the persmission to CmdExec subsystem
*/
EXEC msdb.dbo.sp_grant_proxy_to_subsystem
@proxy_name = 'WinUserProxy'
, @subsystem_name = 'CmdExec' ;
GO
2. I create the PowerShell Script and save it as .ps1 file on the disk. I filter the result with Where-Object {$_.Group -Like ‘*\AG*’}; AG is the folder where I put all AG Listener Name
TVD_SyncLogin.ps1
$Listeners = Get-DbaRegisteredServer -SqlInstance 'CMS Server' | Where-Object {$_.Group -Like '*\AG*'};
foreach ($lsn in $Listeners) {
$primaryReplica = Get-DbaAgReplica -SqlInstance $lsn.ServerName | Where Role -eq Primary | Sort-Object Name -Unique
$secondaryReplicas = Get-DbaAgReplica -SqlInstance $lsn.ServerName | Where Role -eq Secondary | Sort-Object Name -Unique
<#
Some instances have more than 1 AvailabilityGroup
=> Added Sort-Object -Unique
#>
# primary replica logins
$primaryLogins = (Get-DbaLogin -SqlInstance $primaryReplica.Name -ExcludeFilter '##*','NT *','BUILTIN*', '*$')
$secondaryReplicas | ForEach-Object {
# secondary replica logins
$secondaryLogins = (Get-DbaLogin -SqlInstance $_.Name -ExcludeFilter '##*','NT *','BUILTIN*', '*$')
$diff = $primaryLogins | Where-Object Name -notin ($secondaryLogins.Name)
if($diff) {
Copy-DbaLogin -Source $primaryReplica.Name -Destination $_.Name -Login ($diff.Name) -Whatif
}
}
}
3. Then I create the SQL Job on the CMS Server and I run it with Proxy Credential
Please be aware that the script don’t drop the user if you drop it on primary or secondary node. To delete the user or login you must do it manually.
Maybe this article help you to create you own solution.
Cheers
Andrzej (Anjay)
Home 