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.

SQL 2012 Failover Cluster Pt. 12: Kerberos n SSL

KerberosSo here we are, at the end of a somewhat long journey. Yes, this is the last installment in the SQL 2012 Failover cluster series. However, two very important settings are configured. The first is Kerberos, which is the preferred authentication protocol (vice NTLM). In fact, some software will break if Kerberos is not functioning properly. Thankfully there’s a new Microsoft tool that automates that process for the most part, reducing human error.

The second major item is SSL. This is a bit tricky on a Failover cluster, since the exact same SSL certificate must be used on both nodes. To make things more difficult you need to hack the registry to get the certificate to work. And there’s a weird Unicode issue with Notepad that can fowl up your registry hack. So pay attention to the instructions, even if they seem a bit wacky. Last up is installing the latest cumulative update. This is always recommended, so that you have swatted the latest set of bugs.

Blog Series

SQL 2012 Failover Cluster Pt. 1: Introduction
SQL 2012 Failover Cluster Pt. 2: VM Deployment
SQL 2012 Failover Cluster Pt. 3: iSCSI Configuration
SQL 2012 Failover Cluster Pt. 4: Cluster Creation
SQL 2012 Failover Cluster Pt. 5: Service Accounts
SQL 2012 Failover Cluster Pt. 6: Node A SQL Install
SQL 2012 Failover Cluster Pt. 7: Node B SQL Install

SQL 2012 Failover Cluster Pt. 8: Windows Firewall
SQL 2012 Failover Cluster Pt. 9: TempDB
SQL 2012 Failover Cluster Pt. 10: Email & RAM
SQL 2012 Failover Cluster Pt. 11: Jobs n More
SQL 2012 Failover Cluster Pt. 12: Kerberos n SSL

Kerberos

One of the potentially troublesome areas of SQL is properly configuring Kerberos. Kerberos is much more secure than NTLM or SQL authentication. Some applications, such as SharePoint, require it to properly function. Other applications such as vCenter could operate with NTLM, but will take advantage of Kerberos if its properly configured.

1. Download the Microsoft Kerberos Configuration Manager from here. Be sure to get the 64-bit version.

2. Run through the installer using all default values.

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

4. On the connect page don’t enter any information.

5. Once it connects it should show both cluster nodes. Click on the SPN tab. It should list both cluster nodes.

9-28-2013 2-52-13 PM

6. If you scroll all the way to the right it will show the Status, and probably shows the required SPN is missing. If you want ignorant bliss, then just click on Fix. If you want to see actual fix click on the Generate button. The “script” is just the setspn command with the proper arguments. Nothing fancy here. Fix both nodes.

9-28-2013 2-54-21 PM

7. Open up Active Directory Users and Computers. Locate the database engine service account. Open the properties of the account and click on the Delegation tab. Select the Use Kerberos Only option, as shown below. Add a service then enter the database engine service account name. It should then return a list of two SPNs. Highlight both SPNs click OK.

9-28-2013 2-59-45 PM

8. If everything went as planned, you should now have two SPNs listed on the main Delegation tab. Close the ADUC window and return back to your SQL server.

9-28-2013 3-01-29 PM

9. In the Kerberos Configuration Manager, click on the Delegation tab. Select Refresh from the menu. You should now see Constrained to Kerberos delegation.

9-28-2013 3-08-34 PM

SSL Configuration

Normally when services connect to the SQL server they pass all data, except authentication, in the clear. Generally databases contain sensitive data, so the clear text could be a security risk. SQL server supports the usage of SSL encryption for database connections. This is simple to configure, once you get the right SSL certificate installed on each node.

Not all applications support SQL SSL connections. One culprit that comes to mind is the vCenter 5.1 SSO service. On the SQL side you can either require encryption, or make it optional. The setting is up to you. Obviously test your applications, and also see if there’s any performance hit. On modern processors with the AES-NI instruction set the overhead should be quite minimal.

As with all SSL certificates, there’s a number of ways to get them. In my environment I’m assuming a Windows Server root CA is online and configured with an appropriate template. A generic “web server” template would work, as long as it allows you to customize the certificate request (unlike the ‘computer’ template) AND allows the exporting of the private key. Both nodes MUST have the exact same certificate, or you will have a smoking hole. The SQL service checks the thumbprint to validate it is using the same certificate. You can NOT trick it.

1. Open a blank MMC and add the Certificates snap-in. Choose the Computer account. Expand the Personal object and right click on Certificates. Select Request New Certificate, as shown below.

9-28-2013 3-19-38 PM

2. Click through the wizard until you get to the Request Certificates page. What is listed here is entirely dependent on your CA’s configuration. The important fact to keep in mind here is that we need to create a certificate for the cluster name, not the SQL server’s real hostname. Applications connect to the cluster name, not an individual node. So the template you choose must allow you to customize the properties.

You should see a yellow warning under the templates that need more information, which is the kind of template you want.

9-28-2013 3-26-08 PM

3. On the Subject tab use the common name for the subject and enter the FQDN of the cluster name (e.g. D001SQL03-DB.contoso.net). If you wish to possibly connect to the server via a short name, add a DNS alternative name with the short name.

9-28-2013 3-24-08 PM

On the Private Key tab check the box to allow you to export the private key. Note that your CA might over-ride this setting, so just because you set it here doesn’t guarantee you can export it.

9-28-2013 4-12-00 PM

4. Click on the enroll button, and you should see a new certificate in your store. Do NOT close out the certificate MMC, we will use it later.

9-28-2013 3-28-50 PM

5. Right click on the certificate, select All Tasks, then Manage Private Keys. Give the database engine service account READ (only) access to the private key.

9-28-2013 3-29-59 PM

9-28-2013 3-30-59 PM

6. Now you need to export the certificate WITH the private key. Right click on the Certificate and from the All Tasks menu select Export. Check the box to export the private key. Leave the default file format. Secure the key with a password or select your AD account (new to WS2012). Save it to a file and copy the file to the other node.

7. Open the MMC snap-in and connect to the computer’s certificate store. In the Personal node right click on the Certificates node, select All Tasks, then Import. Run through the import wizard, select your key file, and you should get the key imported, and probably your root CA as well. You can delete the root CA certificate, assuming the machine already trusts your CA.

9-28-2013 4-20-29 PM

8. Like you did on the first node, give the SQL database engine service account read permissions to the SSL certificate.

9. Open the SQL Server Configuration Manager and expand the SQL Server Network Configuration container. Right click on Protocols for MSSQLSERVER. If you want to force encryption set the option shown below.

9-28-2013 3-39-15 PM

10. Unfortunately for a clustered SQL instance the Certificate tab will NOT list your certificate issued to the cluster name. We will need to hack the registry to input the proper thumbprint. Back in the certificate MMC double click on your cluster certificate and scroll down to the Thumbprint field. Copy the thumbprint to the clipboard and paste it into an empty notepad window. Remove all spaces.

This is VERY important: Save the notepad file as a .txt file. Click OK when you get the Unicode error. CLOSE Notepad and re-open your thumbprint file. Remove the question mark at the beginning of the file. Copy the remaining characters to the clipboard.

If your registry key has the hidden character the SQL services will fail with the following error:

TDSSNIClient initialization failed with error 0xd, status code 0x38. Reason: An error occurred while obtaining or using the certificate for SSL.

9-28-2013 3-48-45 PM

11. Navigate to HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\<instance>\MSSQLServer\SuperSocketNetLib\Certificate. Paste the thumb print into the registry key, as shown below.

9-28-2013 3-53-06 PM

12. Repeat the registry modification on the second node, using the same “clean” thumbprint.

13. In the Failover Cluster Manager locate the SQL role, then change the node SQL is running on. Move the service back and fourth twice, making sure it starts without errors on both nodes.

 9-28-2013 4-43-44 PM

14. Now that SQL SSL is configured you can change the Connect to Server options to use encryption. Click on Options then check the Encrypt connection box. If you do that, then you need to connect to the SQL server using the FQDN. If you use the short name then you will probably get the following error:

A connection was successfully established with the server, but then an error occurred during the login process. Provider: SSL Provider, error: 0 – The target principal name is incorrect.

While you may think that’s a Kerberos problem, it’s really a certificate issue. Connect with the FQDN, as shown below.

9-29-2013 8-15-16 PM

Cumulative Updates

Cumulative updates are roll-up bundles of fixes between service packs. They are tested, and production ready. In come cases software will require you to be at a certain CU level, or later. As of this writing CU #6 was available for SQL 2012 SP1. The easiest way to find the latest CU is just to Google. You can download CU6 here.

Summary

This is the last installment in the SQL 2012 Failover cluster series. There’s a few more things you could do, such as configuring cluster aware Windows updates (a cool new WS2012 feature). But I’m chomping at the bit to start on the vCenter 5.5 installation series, so that’s all for SQL for now.

As a reminder, I’m NOT a DBA, and these instructions are aimed at your average administrator that doesn’t have access to a DBA, yet wants to setup a SQL failover cluster. I’m sure a professional DBA would configure the system differently, and tailored for the environment. Don’t expect this setup to support your enterprise ERP system, as it would NOT. But, it should be a good starting point for vCenter 5.5 or other similar applications. Memory and disk performance are key, in addition to thorough testing. Documenting performance baselines is very important, should users complain the system is “slow” down the road. You then have a good baseline to compare to, which can help pinpoint performance issues.

© 2017 - Sitemap