5

When using AlwaysOn Availability Groups does each SQL Instance need to use the same service credentials?

We are setting up a new SQL2014 deployment and will be using AlwaysOn. If I have 2 SQL servers (call them ServerA and ServerB) then can I use 2 active directory accounts called Domain\ServerA_SQLServerService and Domain\ServerB_SQLServerService (one on each server)?

Or do they need to use the same account so I should just create Domain\SQLServerService

The accounts would be in the same domain and can have access to both servers. Previously we've had the SQL Server Service account locked out which has obviously caused problems. What I'd like to do is have 2 accounts so that the likelihood of both being locked out is reduced.

Is there a requirement and/or what is Microsoft's best practice?

Improve this question

2 Answers 2

Reset to default
5

When using AlwaysOn Availability Groups does each SQL Instance need to use the same service credentials?

No, SQL Server service accounts can be different. We have a 3 node cluster running AlwaysON and on all the servers SQL Server is running with a different account.

e.g. If you have one server in NY and other in LD, then

Active Directory Accounts to be created (Below will be sql server service accounts)

NYDB1_sql

LDDB1_sql

Add both these accounts to the group DB1-SQL (below)

Active Directory Group to be created : DB1-SQL (Both the above accounts to be members of this group).

One important thing to note, from BOL :

If two server instances run as different accounts, the system administrator must use the CREATE LOGIN Transact-SQL statement to create a login for the startup service account of the remote instance in the syslogins table of the master database of each server instance.

Also, grant the CONNECT permission on the endpoint to the service accounts.

Previously we've had the SQL Server Service account locked out which has obviously caused problems.

You can use group managed service account - which are new in Windows Server 2012. The only requirement is you need at least one Windows 2012 domain controller, and use 2012 or 2012 R2 member servers.

Improve this answer
6
  • I've asked why the SQL Server 2016 documentation talks about syslogins, which was deprecated in SQL Server 2005.
    – Aaron Bertrand
    Jul 8, 2015 at 1:01
  • @AaronBertrand Good catch and clearly it is deprecated (SybaseASE still has it :-) ). They should update it to sys.server_principals
    – Kin Shah
    Jul 8, 2015 at 1:58
  • Is the purpose of the DB1-SQL group so that you can assign the group permissions on all of the nodes? Or is there something extra I'm missing?
    – Greg
    Jul 8, 2015 at 2:16
  • @Greg you can think of that, so that all the nodes will be getting same permissions. Also, it becomes more managable at AD level to just allocate permissions to the group rather than individual accounts. Also, it makes things more manageable when you have a complex environment with different requirements.
    – Kin Shah
    Jul 8, 2015 at 2:20
  • @Kin Thanks. I was planning to use the group for that reason, just wanted to check if there was another less obvious (to me) reason.
    – Greg
    Jul 8, 2015 at 2:30
1

If you use differente Service Accounts for the Engine on each replica, you will not be able configure the SPN and you will not be able to use Kerberos

Improve this answer
1
  • 1
    Yep - we hit this problem. We ended up going with a single grouped Managed Service Account and assigned the SPN's to that account
    – Greg
    Oct 18, 2017 at 20:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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