Kerberos and AvailabilityGroups - What you need to know

  • [アーティクル]

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

Comments

  • Anonymous
    August 20, 2012
    Any tips for named instance?  I have always had Kerberos working well in our environment with our failover cluster.  Now in AG, I cannot get Kerberos working on named instance.  The default instance works fine.  The default instance is using port 1434 and the named instance 1435.  Any tips or ideas?

  • Anonymous
    May 25, 2015
    I'm also having the same issue with named instances with alwayson configuration. SPN's don't seem to work, has anyone got it working?

  • Anonymous
    May 29, 2015
    Something was cleared out^^. Command should be:
    SetSPN -s "MSSQLSvc/FQDN AG listener name:port" "domainserviceaccountname"

  • Anonymous
    May 29, 2015
    Working with Kerberos and AG groups is pretty straightforward if you run all participating SQL services running under the same domain user account. You need to register the AG listeren name like this:
    SetSPN -s "MSSQLSvc/:" ""

  • Anonymous
    March 01, 2016
    Yeah should it be AG Name or AG Listener name?

  • Anonymous
    April 18, 2016
    It should be the AG listener name