SQL Login keeps losing access to databases - mirrored set RRS feed

  • 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.

    There is always a way...

    • Edited by SeanPerkins Tuesday, August 30, 2016 2:06 PM added more info for searches
    Monday, August 29, 2016 4:07 PM


All replies

  • Monday, August 29, 2016 4:42 PM
  • As Tom says, the problem is that the SID is different for the SQL Login on each server. The mapping between server and database is on SID, not name, so this is why things don't work when you move the database.

    You don't enounter this problem with Windows logins, because in this case the SID comes from the AD.

    Then again, it is possible to have the same SID for SQL logins well, as you can use the SID option to set an explicit SID when you create the login.

    • Proposed as answer by Teige Gao Tuesday, August 30, 2016 1:38 AM
    Monday, August 29, 2016 9:18 PM
  • I understand what you're saying about the SID after some research, but I'm not sure that applies exactly to my situation, unless I'm still missing something...

    The ONLY thing that changes is that the mirror fails over, one day SERVER1 will be the primary, the next day SERVER2 will be the primary.

    For example:

    We created the SQL login and the reports were working fine on SERVER1 on Monday, then the mirror swaps Monday night afterhours.  We run the reports again on Tuesday on SERVER2 and they run fine, then the mirrored failover happens again on Tuesday night after hours.  Wednesday morning, we run the reports on SERVER1 and they fail with the aforementioned screenshots.  This is the point where we delete the account and re-add it with the proper permissions for each database, then the reports work again.

    This cycle seems to happen over and over again.  Maybe the long term answer is using an AD account so the SID is the same no matter what with less overhead.

    There is always a way...

    Monday, August 29, 2016 11:36 PM
  • This is caused by the login SID being different between the servers.

    Please see:https://www.mssqltips.com/sqlservertip/1166/login-failures-connecting-to-new-principal-after-sql-server-database-mirroring-failover/

    I'll look this over, thank you for the link, Tom.

    There is always a way...

    Monday, August 29, 2016 11:37 PM
  • Now that I know how to fix the problem, I want to know why this happened.  I'm trying to understand how/why the SID would make a difference to SSRS when the report is ran.

    Does the SQL login go into a "limbo status" after a failover and break all connections to the databases?  Every time we create the account on the new principle databases it works, which means it is a new SID every time.  If it works once on SERVER1 and fails over to SERVER2 and then back again to SERVER1, it should still work (in my mind), but that doesn't seem to be the case.

    There is always a way...

    Tuesday, August 30, 2016 12:10 AM
  • The ONLY thing that changes is that the mirror fails over, one day*SERVER1* will be the primary, the next day *SERVER2 *will be the primary.

    That has the same effect. The logins are defined in the master database. Each server has its own master, and if you create the logins independently without using the SID option, they will get different SIDs and things will turn sour after a failover.

    • Proposed as answer by Teige Gao Tuesday, August 30, 2016 8:26 AM
    Tuesday, August 30, 2016 7:13 AM
  • Thank you all for replying and helping me, you've solved my issue.

    I know understand and will not forget this lesson!

    There is always a way...

    Tuesday, August 30, 2016 2:01 PM