Solved

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

Posted on 2016-09-23
18
54 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 29

Expert Comment

by:Rich Weissler
Comment Utility
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
Comment Utility
Hi Rich, Privileges like db_owner role is missing every alwayson failover  for the user SA authentication. Please provide your inputs
0
 
LVL 29

Assisted Solution

by:Rich Weissler
Rich Weissler earned 250 total points
Comment Utility
*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
 

Author Comment

by:tschary
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:tschary
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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 45

Accepted Solution

by:
Vitor Montalvão earned 250 total points
Comment Utility
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
Comment Utility
I tested in verification now and it worked well. Thanks for support.
0
 

Author Closing Comment

by:tschary
Comment Utility
Thanks a lot.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Cheers
1

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

728 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now