SQL Login keeps losing access to databases

Question

We created a SQL Login account for reporting use only (SSRS), we'll call it 'MyReportAcct'.

We've used this account for some time and it is created for every newly installed instance. It has been working as you'd expect, except on a couple of mirrored instances. I've not seen this issue on any standalone instances in my environment.

Randomly, reports will stop working and there is nothing being changed based on our internal processes for Change Management, Windows Logs, and SQL Error logs. Here are two of the error messages we receive:

Upon investigation of the account, it shows up under the Security object folder in SSMS. Also, we find that the account is associated to all the databases under User Mapping for the account properties. I've verified that 'MyReportAcct' has public and db_datareader, so on the surface every looks as expected.

The rub comes when I try to alter the account or remove the mappings. I'm met with:

• Cannot alter the user because it does not exists or you do not have permission (Error 15151).
• Cannot drop the user dbo.. (Error 15150)

What I've had to do to fix the issue is delete every instance of 'MyReportAcct' on the server, then recreate it, then add to each database. This doesn't happen all the time, but often enough that it is irritating. Further, we shouldn't have to do this at all.

I think that we should change this account to an Windows Account instead of a local SQL Login in every instance. I have a feeling that this might fix the issue, but I need to prove why or provide a reasonable answer. I think part of the problem is the mirroring in some way, but I don't know how to prove that either.

Security is a weakness of mine and I'm employing steps to change this, so any insight you can provide would be greatly appreciated. Please let me know if you need more information.

Answer 1

Short answer:

The SIDs for the ‘MyReportAcct’ SQL login are different on each server instance.

Long answer:

The master database houses the security database for each instance of SQL Server (think Active Directory for a database server). In this scenario we have two different master databases, since it is a mirrored pair. Each time you create a SQL Login, there is a different SID created along with the account, this is no different than how Active Directory has always worked.

When a mirrored database fails over, the SID in the database matches the SID stored in the master database of the principal server (SERVER1), not the mirrored server (SERVER2). This natural, and default process of failover causes the SQL Login (MyReportAcct) to become an orphaned account on the principal server (SERVER1). This orphanhood process happens EVERY time there is a failover. The start of authority for SQL Logins are the instance that they were created (SERVER1). The failover to the mirrored server (SERVER2) is now a different start of authority, so the SIDs will be different, hence why the reports fail.

Solution:

• Create the SQL Login ‘MyReportAcct’ with the same SID every time. This process requires using TSQL code in the query analyzer by someone who is a sysadmin.
• Create an Active Directory service account named ‘uic\MyReportAcct’. This way, the SID is managed by AD and we never have to worry about the aforementioned issue again.

Answer 2

Sounds like an orphaned user to me. Mismatched SIDs. You could use the following stored procedure to fix orphaned logins:

USE DatabaseName
GO

EXEC sp_change_users_login 'update_one', 'MyReportAcct', 'MyReportAcct'
GO

Perhaps you could create a regular job to sync the logins between Production/DR Database Instances and to check and fix orphaned accounts as described here: http://www.msbicoe.com/post/2012/04/05/Automatically-Transfer-and-Synchronize-SQL-Server-logins-SIDs-Passwords-and-Permissions.aspx