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.

Print Friendly, PDF & Email

Related Posts

Subscribe
Notify of
9 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
jbvernejoux
June 27, 2017 1:29 am

hello,
Great post on kerberos and SQL AAG.

But your 2 URL links about scripts to test Kerberos Connection are dead:
an dropbox error is displayed.

Could you please fix it, I would test your script ?
Thank you

Jesse
August 10, 2017 12:02 pm

All of your dropbox links no longer work, is it possible to get these back up?

Scott
August 16, 2017 6:16 am

Derek, is it possible to re-post the scripts referenced here. The links are currently dead. Thanks.

Scott
August 16, 2017 12:39 pm

Derek, is it possible to re-post the scripts referenced here. The links are currently dead. Thanks.

Chad
March 1, 2018 5:50 am

I cannot get the validation of the ‘PoSh Listener Connection’ to even show up as either NTLM or KERBEROS. Any suggestions?

Ytsejamer1
June 5, 2018 10:34 am

I don’t think I need this particular application, but was going through the exercise for ifnormational purposes. Unfortunately the version I downloaded (4.1) will not connect – I get a System.DirectoryServices.AccountManagement.PrincipalServerDownException: The server could not be contacted. —> message.

Oh well… Love this series Derek…so much great information here. Can’t wait for newer revisions.

May 8, 2019 5:58 am

Derek, is it possible to e-mail the script referenced here. The links are currently dead. Thanks.

Ewan
April 21, 2022 5:03 am

Derek – thank you! Excellent article, clear, good explanation and screenshots.
Saved me from the pain of kerberos troubleshooting! 🙂
We were missing SPNs for AG Listeners – after creating these clients can now connect.