Andrea Allred presents RoyalSQL

Bringing happy endings to all your data stories.

Tag Archives: Failed Login

April 30, 2021 by andreaallred

Only AG’s can hurt like this

While doing a planned Availability Group failover, the application stopped talking to the database. After checking the SQL Server log, we found that all the SQL Logins were failing with an “incorrect password” error. The logins were on the server, the users were in the databases, and the passwords were even right, so what was wrong? It all comes down to SID’s (Security Identifiers).

I am going to back up a little bit and point out the few main items that don’t automatically synchronize between two AG Servers. I like to think in terms of Database Level vs Server Level. If it is stored in a database and that database is in the AG, then it will synchronize. The msdb database and the master database can’t be in an AG, those two hold the agent jobs, linked servers, backup history and logins. Because a sql login can be created on two different servers, even if they have the same username and password, they can have a different SID which will make them mismatch. That is what had happened in this case.

I like to use a script when creating these logins that has the SID specified so they will match between all the AG nodes. It can be done like this:

CREATE Login MyLogin WITH password = 'SecurePassword', SID = 0x06046EG0F6C0C6488GCGG3G815EFF5GA

But how can I check if the SIDs match once they are created? I like to create a temporary linked server between the nodes to make it easier for me to verify them (it can introduce a security risk if left there). Then I run something like this:

SELECT A.name, A.sid, B.name, B.sid
FROM NodeA.master.dbo.sysusers A
LEFT JOIN NodeB.master.dbo.sysusers B ON A.name = B.name
WHERE A.sid <> B.sid

I absolutely could do this without the temporary linked server by querying each node and comparing the SID’s but this is a little faster.

This posts song is Only Love Can Hurt Like This by Paloma Faith

Posted in Awesome T-SQL, Moving Data, Security
Tagged AG, Availability Group, Failed Failover, Failed Login, Log, SID, T-SQL
1 Comment

Post navigation

  • Pictures -Taken by Magic Mirrors
  • About the Royals
  • Playlist
  • SQL Server 2016 Discovery Day – SLC

News From The Kingdom

  • These are the ints that never die, we reseeded negative.
  • I Made That Slow Query Run, Run, Run
  • I can query multiple instances, I am King!
  • Hit me with them good vibes, CTfP is set nice. Everything is so fire, little bit of sunshine!
  • I’m going on down to New Orleans and renaming some database things…

Enter your email address to follow this blog and receive notifications of new posts by email.

Find Your Data Story

Royals Magic

  • Register
  • Log in
  • Entries feed
  • Comments feed
  • WordPress.com
Blog at WordPress.com.
  • Follow Following
    • Andrea Allred presents RoyalSQL
    • Join 2,028 other followers
    • Already have a WordPress.com account? Log in now.
    • Andrea Allred presents RoyalSQL
    • Customize
    • Follow Following
    • Sign up
    • Log in
    • Report this content
    • View site in Reader
    • Manage subscriptions
    • Collapse this bar