Solved

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

Posted on 2016-09-23
18
126 Views
Last Modified: 2016-10-06
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
Comment
Question by:tschary
  • 9
  • 7
  • 2
18 Comments
 
LVL 30

Expert Comment

by:Rich Weissler
ID: 41812655
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
 

Author Comment

by:tschary
ID: 41812662
Hi Rich, Privileges like db_owner role is missing every alwayson failover  for the user SA authentication. Please provide your inputs
0
 
LVL 30

Assisted Solution

by:Rich Weissler
Rich Weissler earned 250 total points
ID: 41812688
*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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:tschary
ID: 41812712
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
 

Author Comment

by:tschary
ID: 41818199
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
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41819355
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
 

Author Comment

by:tschary
ID: 41819370
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
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41819371
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
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41819373
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
 

Author Comment

by:tschary
ID: 41819490
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
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41819516
Remove those logins from the secondary and transfer them following this Microsoft's article. This will guarantee the same SID on both replicas.
1
 

Author Comment

by:tschary
ID: 41819753
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
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41819764
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
 

Author Comment

by:tschary
ID: 41831306
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
 
LVL 48

Accepted Solution

by:
Vitor Montalvão earned 250 total points
ID: 41831313
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
 

Author Comment

by:tschary
ID: 41831344
I tested in verification now and it worked well. Thanks for support.
0
 

Author Closing Comment

by:tschary
ID: 41831346
Thanks a lot.
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41831347
Cheers
1

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question