• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 759
  • Last Modified:

Missing Privileges after every failover of SQL Server 2014 Always-on

Hi Experts,
I have observed that after every  failover ,the SQL Server Authentication user privileges are missing in SQL Server 2014 Always-on Servers. I am adding again the privileges. Please guide me how to fix this.

Thanks,
Sreenivasa
0
tschary
Asked:
tschary
  • 9
  • 7
  • 2
2 Solutions
 
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
I assume what is missing is user logons.  A couple ways to resolve the issue... one of which would be to use Contained Databases.  (That said, I haven't met anyone using Contained Databases yet.)

Alternately, Jonathan Kehayias has a tool for synchronizing logon objects, which would be an add-in for SSMS.

Should also be possible to automate a solution with an SSIS package.
1
 
tscharyAuthor Commented:
Hi Rich, Privileges like db_owner role is missing every alwayson failover  for the user SA authentication. Please provide your inputs
0
 
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
*worry*  Are permissions 'dropping off' of just SA, or is it happening to other accounts as well?  

I suspect it's a case like SA, where two SQL Accounts exists (one on each SQL instance) with the same name... but are different accounts, that SQL isn't identifying them as identical.  If you manually created accounts on each of the two boxes, they'd have different SIDs as well, and the permissions wouldn't carry over.  On the other hand, Windows domain credentials, or sql logons copied while retaining SIDs (either using the Microsoft script, or Kehayias/SQLSkills tool... that the permissions will be retained.
1
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
tscharyAuthor Commented:
yes.Its SA. user account name is same but copid from one to another . user account is exist on both instances but database role is missing. I wil go through the link and revert. thanks
0
 
tscharyAuthor Commented:
Hi Rich,

issue is  SA logins exist on both SQL Server 2014 Alwayson instances but privileges like database role db_owner is missing when ever fail-over occurred. I have seen the SID and they are different. Please see the below and suggest.

test1--0xA406F104F9DD7E47B878D36E89E942A8
test2--0x4F7B55F8418491458A1533DA6311E76B
test4--0xF4E66317DD16194FB31DC44390126D4E
test4--0xDDA40B51CA780041ABD7FE61DCF26516


Instance B

test1--0x58316DE47D95094BA41A1712ABD9A64D      
test2--0x5CE9289F5AD5A546832C99EBE94626DC      
test3--0x74DEDB589EDC024580033A407F6A9CD1      
test4--0x244A52011DCD2E44BE8D3A7A8AEEF431
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
SA doesn't need db_owner in databases. Being SA is implicit that it owns any database in the SQL instance.
Are you getting any error because of this?
1
 
tscharyAuthor Commented:
Hi Vitor,

user is SQL Server authentication account. we have created and provided only db_owner privileges to the database and not a sysadmin role. when a  fail-over occurred we are losing privileges on another instance. Please find below the error and suggest me.

Message
Error: 18456, Severity: 14, State: 40.

Login failed for user 'test1'. Reason: Failed to open the database 'xyz' specified in the login properties. [CLIENT: xx.xx.xx.xx]
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Check if the default database are the same for the Login in both SQL Server instances.
Also check if they have the same UID:
SELECT principal_id 
FROM sys.server_principals
WHERE name = 'LoginName'

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry, you need to verify SID and not the principal_id:
SELECT sid
FROM sys.server_principals
WHERE name = 'LoginName'

Open in new window

0
 
tscharyAuthor Commented:
Yes you are right, I have observed  for other SA users and they have same SID on both the instances.So we did not get privileges issues after failover. And we are getting few users which has different SID on both instanceS.

Shall I copy the Primary replica User login script and execute in the secondary. I think secondary will not allow changes as it is readonly databases.

Please guide me how to change SID in secondary replica and keep both the instances has same SID.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Remove those logins from the secondary and transfer them following this Microsoft's article. This will guarantee the same SID on both replicas.
1
 
tscharyAuthor Commented:
Thanks Vitor, I went though  the link . I want it for only 4 users and not for all . it is a critical server and I want to do it for only different SID users. Do we have any more  easy method? .. Meanwhile i will try to do it in verification.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
No, that's the only and the correct method but you have it all there. Just copy the script and you can add a filter just for those 4 logins.
0
 
tscharyAuthor Commented:
Hi Vitor,

I tried as per your suggestion and created stored procedures. but while running sp_help_revlogin script in secondary replica , it is not allowing and  getting error below.

Failed to update database "OP Jobs" because the database is read-only. Error: 3906.

Please help me to resolve.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
That script is only to run of the Primary Replica where the logins are working. It will produce then a result that you should copy to run in the Secondary Replica:

"3.Run the following statement:
EXEC sp_help_revlogin
The output script that the sp_help_revlogin stored procedure generates is the login script. This login script creates the logins that have the original Security Identifier (SID) and the original password.

4.On server B, start SQL Server Management Studio, and then connect to the instance of SQL Server to which you moved the database.

Important Before you go to step 5, review the information in the "Remarks" section.
5.Open a new Query Editor window, and then run the output script that is generated in step 3"
1
 
tscharyAuthor Commented:
I tested in verification now and it worked well. Thanks for support.
0
 
tscharyAuthor Commented:
Thanks a lot.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Cheers
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 9
  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now