4

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?

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.

  • I've asked why the SQL Server 2016 documentation talks about syslogins, which was deprecated in SQL Server 2005. – Aaron Bertrand Jul 8 '15 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 '15 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 '15 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 '15 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 '15 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

  • 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 '17 at 20:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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