Kerberos and AvailabilityGroups - What you need to know

  • Article

With the freshly released SQL Server 2012 we can put availability groups to live. You have seen all the demo's and now you are ready to get things going in your own datacenter. At this point you might get to a few surprises. One of these might be to get Kerberos going for your availability groups. If we recap the theory behind availability groups we get a more clearer picture.

In SQL Server 2012 we use availability groups as a unit of failover. We have up to five single SQL instances. The underlying nodes participate in the same Windows Failover Cluster. In the cluster configuration you will notice a few resources shared amongst these independent SQL instances:

  • Availability Group Resource
  • IP-Address
  • Virtual Network Name

Now if you want to use Kerberos you must make sure all prerequirements are met. The most important thing being a service principal name, or SPN. We set a SPN using the SETSPN command that's included in the Windows. A good start on more information is Books Online: https://msdn.microsoft.com/en-us/library/ms191153(v=sql.110).aspx

Getting Kerberos to work on just SQL should not be hard. So now we introduce availability groups. This is where it gets harder. To get Kerberos working on availability groups you must also set a SPN on the virtual network name.

So if you have an availability group named AG-SQL-1 in the CONTOSO.COM domain you must set a SPN on ag-sql-1.contoso.com with the correct port and the domain account running the SQL Server process. So here is a catch. What if the availability group fails over to another node. It needs to be registered again unless that node is running with the same domain credentials.

Bottom line: if you want to use Kerberos with availability groups you need to run all nodes with the same domain account. This will save you a lot of headaches.

You can read up on it in Books Online: https://msdn.microsoft.com/en-us/library/ff878487(v=sql.110).aspx#PrerequisitesSI