3

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:

enter image description here enter image description here

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.

Improve this question
5
  • 1
    If the database is restored, then the permissions will get lost. Start an audit or server side trace and capture why it is loosing access. The default trace has objects_altered event which will help you along with other audit events as well. – Kin Shah Aug 29 '16 at 15:59
  • 1
    @Kin, I should've stated that the databases aren't being restored in the traditional sense. These are databases in a mirrored configuration. – Sean Perkins Aug 29 '16 at 16:15
  • 1
    @SeanPerkins, is a failover happening before the permissions are lost? If so, it's because the permissions were not set on the secondary server for things outside of the user database, such as permissions in msdb. System database permissions do not get mirrored. Once you add the permissions on each server, you should be all set going forward. – Tara Kizer Aug 29 '16 at 16:34
  • 1
    @SeanPerkins Have you created login with same sid on both the servers ? Assign permissions at database level. So after failover the permissions will be maintained. Or you can have a post failover script that will assign the same permissions as the primary server on the secondary server. – Kin Shah Aug 29 '16 at 16:41
  • 1
    i want to thank everyone that assisted and took time out of their day to help me see the light. I posted in other forums as well as performing more research. I now understand why this happened and will not forget this lesson! I've posted a complete answer below. – Sean Perkins Aug 30 '16 at 14:30
3

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.
Improve this 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

Improve this answer
1
  • 1
    Unfortunately, using sp_change_users_login will only resolve it once for the active node. As soon as the Availability Group fails over, the User will become orphaned again as the Login SIDs don't match on the two nodes participating in the AON AG. – AndyHerb Nov 10 '18 at 11:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.