As we near the end of this installation series, there are a couple of final areas to cover. Up in this installment is configuring SSL. Now you may be thinking, SQL and SSL, really? Yes, for a good number of years SQL has supported the use of SSL for database connections. This enables you to encrypt from client to server all of the SQL traffic. Because we are using AAGs and two cluster nodes, configuring SSL for all of the possible connections is tricky. I will make the assumption here that you have an internal Microsoft CA which can issue certificates with SANs (subject alternative names). If this is not your situation you can still read through the post to get a feel for the configuration steps, but you will need to modify them for your environment.
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
SSL Configuration
1. Open a blank MMC console and add the Certificates snap-in for the local computer store.
2. Expand the Personal node, right click and select All Tasks then Request New Certificate.
3. Select the Active Directory Enrollment Policy.
4. Now here’s where we may run into problems. On my Microsoft CA I have a template called Server Authentication – SAN. You won’t have this, but may have other templates that work. The important factor here is that we need to enter SAN values on the certificate. This will be denoted by a yellow warning under the certificate template that more information is needed. If you don’t have any templates which need this additional information, you will need to make a new template. You can do this by duplicating the web server template and making sure the supply in request option is used for the subject name.
5. Check the box next to your template that needs additional information and will work as a server SSL certificate.
6. Click on the yellow warning message. A window will open up requesting certificate properties. For the subject name change the type to common name and enter the FQDN of the SQL server node.
7. In the alternative name field change the type to DNS. Enter the short name of your SQL server node, plus the short name of your AAG listener  (e.g. SDSQL03-AG1L) and the FQDN of your AAG listener.
8. Process through the enrollment process, and you should now have a freshly minted SSL certificate in your computer’s certificate store.
9. Right click on the certificate, select All Tasks, then choose Manage Private Keys. Give the SQL DBE service account read (only) permissions to the certificate.
10. Close out the certificate MMC.
11. Open the SQL Server Configuration Manager. Open the properties of the Protocols for MSSQLSERVER as shown below.
12. Click on the Certificates tab and choose the SSL certificate from the dropdown menu. You could also go to the Flags tab and enforce encryption. I would not recommend this unless you know 100% all of your applications support SQL SSL. Many do not.
13. Stop and restart the SQL server and ensure it starts.
14. Repeat the entire process on the second SQL node, but use the FQDN and short name of the other node during the certificate request process. Use the same AAG listener short name and FQDN.
Summary
We have now configured both SQL nodes in our AAG cluster to be SSL enabled. So if you have applications which support encrypted SSL, then you are now set. Please do note that SSL certificates and AlwaysOn listeners have extremely limited client support due to the use of SAN certificates. See this MS article for more details. Bottom line is that even though we have fully configured SSL certs on the server side, the client side may not support the SAN certificate property needed to encrypt to the AAG listener. For non-AAG enabled databases you could still encrypt by connecting directly to the SQL server node’s name.
Also remember that if you add more AAG listeners to the cluster, then you will need to re-issue the SSL certificates on both nodes to include the additional SAN short names and FQDNs.
can i use a wildcard cert?
I do not think so, but I haven't researched that lately.
Khanh,
Actually SQL 2008 and higher does support wildcard certificates. The one caveat is that the certificate will not be visible in the SSL drop down location and must be added through the registry using the thumbprint of the certificate you want to use and don’t forget to give the SQL service account access to read the private keys if you are using a specific account other than SYSTEM.
Arggghh!!! Of course it doesn’t work on the last step! 🙂 So close, yet so far away. The cert just simply isn’t in the dropdown in SQL Configuration Manager. Haven’t found the solution yet, but will post it if I find it.
I was able to get the SQL engine to use encryption by following the instructions on the section titled: Unable to see the certificate in the drop-down list box in SQL Server Configuration Manager. Go here: https://thesqldude.com/tag/certificate/
What should I do if the server does not see the certificate? Could you give an example of setting up your template, by which you issued a certificate for the server?