Does both the sql cluster nodes needs to use the same service account ? In my environment I saw one one was using a service account and another was using a local account although the sql agent was using the same acccount and once the failover went to the node which was using the local account i was not able to failback to the original node and also the back up were failing. Is this due to the mismatch of the sql server service account ? please help

  • Did you look at the error log to see why backups failed? – Erik Darling Nov 16 '17 at 17:28
  • 'XXXX failed to create. Operating system error 5(Access is denied.)'. This might might be that since it failed back to a server that was using a local account the cluster instance was not accessible anymore thats my guess – SqlNovice Nov 16 '17 at 17:35

All nodes in a cluster should be using the same service account for SQL Server. This is true for both an FCI instance and an Availability Group setup.

For security sake, consider using Group Managed Service Accounts.https://blogs.technet.microsoft.com/askpfeplat/2012/12/16/windows-server-2012-group-managed-service-accounts/

This will allow you to have a service account that will have an strong, automatically changing password for your SQL Servers. Auditors love this. Your risk is minimum since Active Directory is handling the password change and downtime is not required to do this.

  • I understand the point when the services failed over I could not log in to the clustered instance name as it was using the local account, my question is that are the failed backups were also due to the sql service using a local account although agent were using the sane account – SqlNovice Nov 16 '17 at 16:54
  • +1 for mentioning Group Managed Service Accounts. Great addition. Also might be worth mentioning Kerberos authenication. It can really bite you later if you neglect it. docs.microsoft.com/en-us/sql/database-engine/configure-windows/… – Shooter McGavin Nov 16 '17 at 22:11

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.