SQL 2014 Always-on AG Pt. 12: Kerberos

We are nearing the end of the SQL 2014 AlwaysOn Availability Group series, with just Kerberos and SSL left to configure. In this installment we tackle Kerberos. Depending on your environment, you may or may not need Kerberos configured. Kerberos is only effective when using Windows authentication to your SQL server, not SQL authentication. Should Kerberos authentication fail, it will fail back to NTLM. In case you do have a multi-tiered application that needs Kerberos, let’s get it configured. Microsoft has made some of the Kerberos configuration easy, via a nice GUI tool they created. Unfortunately it is not AAG aware, so there’s still a bit of manual configuration needed. But it helps reduce human error.

Blog Series

SQL 2014 Always-on AG Pt. 1: Introduction
SQL 2014 Always-on AG Pt. 2: VM Deployment
SQL 2014 Always-on AG Pt. 3: Service Accounts
SQL 2014 Always-on AG Pt. 4: Node A Install
SQL 2014 Always-On AG Pt. 5: Unattended Node B
SQL 2014 Always-on AG Pt. 6: Cluster Configuration
SQL 2014 Always-on AG Pt. 7: TempDB
SQL 2014 Always-on AG Pt. 8: Max Mem & Email
SQL 2014 Always-on AG Pt. 9: SQL Maintenance
SQL 2014 Always-On AG Pt. 10: AAG Setup
SQL 2014 Always-On AG Pt. 11: File Share Witness
SQL 2014 Always-On AG Pt. 12: Kerberos
SQL 2014 Always-On AG Pt. 13: SSL

Kerberos Configuration

1. Download the Microsoft Kerberos Configuration Manager for SQL server here. Install it one of your SQL servers.

2. Navigate to C:\Program Files\Microsoft\Kerberos Configuration Manager for SQL Server and launch KerberosConfigMgr.exe

3. Since we are connecting locally you won’t need to enter any connection information.

2014-10-26_19-03-23

4. Once connected, navigate to the SPN page. All the way on the right there is a Status column. Unless it says Good with a green checkmark, your SPNs are not configured. You should see a “Missing” status.

5. Click on the two Fix buttons. You should now see a status of Good.

6. Repeat the process on the second SQL server.

7. Go to an Active Directory domain controller and locate the database engine service account. In the advanced view open the Attribute Editor tab and locate the servicePrincipalName entry. Open it, and you should see four entries.

8. We need to add two SPNs, for the AAG listener. Follow the same format as the existing entries, but use the FQDN of your listener computer object. Add a second entry with the port number. You should now have a total of six SPNs.

2014-10-26_19-11-43

9. Download my Kerberos PowerShell test script from here. Copy it to a non-SQL server and run it. Enter the FQDN of the first SQL host and the FQDN of the AAG listener. You should see two Online statements. Do not run this from the SQL server, or the authentication method will be shown as NTLM. Run this on a non-SQL AAG server, please.

2014-10-26_19-19-43

10. Download my SQL authentication script from here. Open it in SQL Studio and run it. At the bottom of the screen you should see two Kerberos entries. This corresponds to the two connections made from my PowerShell script. If they are shown as NTLM, then Kerberos is not working. Re-run the PowerShell script, but this time connect to the second SQL server and validate Kerberos is working on there as well.

SQL Auth

Summary

At this point you’ve now configured Kerberos for both SQL nodes and your AAG listener. You’ve also tested it as well to ensure the configuration works. Sometimes Kerberos can be touchy, and I’ve had situations where even with all the SPNs setup correctly the listener won’t authenticate with Kerberos. So YMMV, and you may need to open a ticket with MS should it not work for you. But at least you know the process that should work for everyone.

Next up in Part 13 is configuring SSL.

Leave a Reply

4 Comments on "SQL 2014 Always-on AG Pt. 12: Kerberos"

Notify of
avatar
Sort by:   newest | oldest | most voted
wpDiscuz