Recently encountered an issue on a client site where they have automatic Database Mirroring failover but when it came to failing over it didn’t work!  (the automatic failover of the database worked but the website couldn’t connect).This quickly becomes a stressful place to be, especially if the database is client facing and you are new to the environment.

First place I always look is in the SQL Server errorlog and if its flooded with messages, which it will be if the instance has been enabled for login auditing for both failed and successful logins, then the following SQL comes in handy for quickly finding any errors. (not going to go over the full syntax here that’s something I’ll let you explore on your own).

exec sp_readerrorlog 0,1,'error'

And here’s some sample output I’ve created. Now the state code below of 38 means the login is valid but the database is unavailable or the login is not permissioned. Now I know where to find the error in the log I will revert back to using sp_readerrorlog without any parameters to get the full error message.

LogDate                 ProcessInfo  Text
----------------------- ------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2017-02-22 11:14:53.850 Server       Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
2017-02-22 11:14:53.850 Server       Registry startup parameters: 
  -d C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\master.mdf
  -e C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG
  -l C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL
2017-02-22 11:14:54.070 Server       Machine supports memory error recovery. SQL memory protection is enabled to recover from memory corruption.
2017-02-22 11:15:36.390 Logon        Error: 18456, Severity: 14, State: 38.
2017-02-22 11:17:03.770 Logon        Error: 18456, Severity: 14, State: 38.

I will post something later on state codes as its useful to have these to hand if disaster strikes.

With the database online I could connect using my support account and see others doing the same. So the state code and my experience prompted me to check if the SID (‘security identifier’) for the login differed from the original primary instance and it did, when the SID of the login does not match the SID of the user in the database it becomes orphaned.  Orphaned users lose any access to the database. I checked the SID values in sys.sql_logins for the login on each instance; but it would be much quicker to run the following SQL on the new primary instance.

exec sp_change_users_login 'report'
UserName                                                                                                                         UserSID
-------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
MySQLLogin                                                                                                                       0xDC74C13D680EDD48A452D4A883B21A00

When running it within the user database, if you get any output you have orphaned users. To quickly fix this run the following SQL to link the entry in sys.database_principals system catalog view in the user database to the SQL Login.

exec sp_change_users_login 'auto_fix','MySQLLogin'

Please note this is only a temporary fix to get you back up and running quickly as when you fail back over to the original primary you are going to get exactly the same problem. To fix this on a permanent basis to ensure that any automatic failover goes smoothly, you need to drop the SQL login on the now secondary instance and recreate it specifying the SID that matches the one on the now primary instance. Here is an example.

/* Please use complex passwords with CHECK_POLICY= ON unlike this example. */
, DEFAULT_DATABASE=[AdventureWorks2012]
, DEFAULT_LANGUAGE=[us_english]
, SID = 0xDC74C13D680EDD48A452D4A883B21A09;

Hopefully by reading this you will check your environments, whether they are using Database Mirroring, Log Shipping or Always On, and you don’t get caught out with orphaned users.


When automatic failover doesn’t work
Follow on Feedly
%d bloggers like this: