Friday, April 19, 2013

How To Encrypt SQL Server Connections

Open the Logical Certificate Store for the Local Machine

  1. Start the Microsoft Management Console (mmc.exe)
  2. Under the File menu select Add/Remove Snap-in… to launch the Add or Remove Snap-ins window.
  3. Select Certificates from the list of Available Snap-ins.
  4. Click [Add >] to launch the Certificates snap-in configuration window.
  5. Select Computer account
  6. Click [Next]
  7. Select Local computer
  8. Click [Finish] to close the Certificates snap-in configuration window.
  9. Click [OK] to close the Add or Remove Snap-ins window.

Create a Certificate Request

  1. In the Microsoft Management Console, select Certificates (Local Computer) on the left side of the window. This will display a list of certificate categories.
  2. Right-click on the Personal on the right side of the window.
  3. Click on Create custom request under Add Tasks -> Advanced Operations to launch the Certificate Enrollment wizard.
  4. Click [Next] to go to the Certificate request page
  5. From the Template dropdown select (No template) Legacy key and leave the Request format as PKCS #10.
  6. Click [Next] to go to the Certificate information page.
  7. Expand the request by clicking on the clip_image004 button on the right side of the window.
  8. Click [Properties] to launch the Certificate Properties window.
  9. On the General tab, in the Friendly Name field, enter SSL Certificate for SQL Server. The friendly name can actually be anything, but it should be easily distinguishable as the certificate for the SQL Server.
  10. On the Subject tab, add the Common Name attribute with the actual name of the server. If the server is part of a Windows domain, it must be the fully qualified name of the server including the domain. If the server is not part of a Windows domain, it must be the NetBios name of the server.
  11. Also on the Subject tab, add any other attributes required by your Certificate Authority
  12. On the Private Key tab, change the Key Type to Exchange.
  13. Expand the Key options group on the Private Key tab and change the Key size to 2048.
  14. Expand the Key permissions group on the Private Key tab, check Use custom permissions.
  15. Click [Set permissions] to open the Permissions window.
  16. From here add the start up account for SQL Server service. This is the “Log on as” Windows account used by the SQL Server service. If necessary, this can be configured later.
  17. Click [OK] to close the Permissions window.
  18. Click [OK] to close the Certificate Properties window.
  19. Click [Next] in the Certificate Enrollment wizard to go to the Export page of the wizard.
  20. Enter (or browse for) a File Name for the certificate request export.
  21. Click [Finish].
Once complete, a Certificate Request file will be generated and can be turned into a Certificate by any certificate authority.

Generate Signed Certificate

<<insert magic here>>

I leave it up to the reader to decide how to generate a signed certificate.  Whether using a 3rd party certificate authority, a Windows certificate authority in Active Directory, or generating a self-signed certificate, all will work.  And, YES, a self-signed certificate will work - at least, at this stage in the game.  While the SQL Server can require encrypted connections, it is up to the client to decide whether certificate validations occur. This will be covered later in Part 2.


Import the Signed Certificate

  1. In the Microsoft Management Console, select Certificates (Local Computer) on the left side of the window.
  2. Right-click on the Personal on the right side of the window.
  3. Click on Import under All Tasks to launch the Certificate Import Wizard.
  4. Click [Next].
  5. Enter (or browse for) the signed certificate file generated by the certificate authority.
  6. Click [Next].
  7. Click [Next].
  8. Click [Finish] to close the Certificate Import Wizard.

Configure SQL Server to Use Encrypted Connections

  1. Start the SQL Server Configuration Manager.
  2. Expand SQL Server Network Configuration.
  3. Right-click on Protocols for MSSQLSERVER. If SQL Server is installed as an instance, MSSQLSERVER will actually be the name of the instance.
  4. From the context menu, click Properties to launch the Protocols for MSSQLSERVER Properties window.
  5. On the Flags tab, select Force Encryption and change the value to Yes.
  6. On the Certificate tab, select the SSL Certificate for SQL Server certificate from the dropdown. The certificates listed here will be listed by the Friendly Name on the certificate. If not specified, the Common Name will be listed instead.
  7. Click [OK] to close the Protocols for MSSQLSERVER Properties window.
  8. In the SQL Server Configuration Manager window, select SQL Server Services.
  9. Right-click on SQL Server (MSSQLSERVER) on the right side of the window.
  10. From the context menu, click Restart.

Notes


Minimum SSL Certificate Requirements

In some cases, the certificate may not appear in the SQL Server Configuration Manager window. Below are the absolute minimum requirements for a certificate to show in the window and for it to work with SQL Server.
  • The Template used must be (No template) Legacy key. This allows the Key Type to be changed.
  • Private key must have a Key Type of Exchange.
  • The Common Name (or Issued To) attribute must be the same as the server name. If part of a domain, this will be the fully qualified domain name of the machine. If not part of a domain, it will simply be the NetBios name of the machine.
  • The Enhanced Key Usage for the certificate must allow for Server Authentication (1.3.6.1.5.5.7.3.1).
  • The certificate & private key must be stored in the logical certificate store for the local computer.
  • The private key must be accessible to the Log On as Windows account for the SQL Server service. Even if inaccessible, the certificate will display, but the service will fail to start. 

SQL Server Fails to Restart

If permissions are not properly configured, the exception 0x8009030d may occur during SQL Server startup and be logged in the SQL Server ERRORLOG file. Because SQL Server has been configured to require encrypted connections, this will prevent the SQL Server service to start. The full text of the error will be similar to the following:
The server could not load the certificate it needs to initiate an SSL connection. It returned the following error: 0x8009030d. Check certificates to make sure they are valid.
To resolve the issue, modify the permissions for the certificates private key as covered in Configure Private Key Permissions. Alternately, reconfigure the SQL Server to use unencrypted connections as covered in Configure SQL Server to Use Unencrypted Connections.

Configure Private Key Permissions

If the SQL Server fails to start, check the permissions to the certificate’s private key.
  1. In the Microsoft Management Console, select Certificates (Local Computer) on the left side of the window.
  2. Double-click on the Personal category on the right side of the window.
  3. Double-click on Certificates on the right side of the window.
  4. Right-click on the certificate and click Manage Private Key…under All Tasks. This will open the Permissions window for the certificate’s private key.
  5. From here add the start up account for SQL Server service. This is the “Log on as” Windows account used by the SQL Server service.
  6. Click [OK] to close the permissions window.

Configure SQL Server to Use Unencrypted Connections

If all else fails, reverting to unencrypted connections may be the only way to restore access to the SQL Server.
  1. Start the SQL Server Configuration Manager.
  2. Expand SQL Server Network Configuration.
  3. Right-click on Protocols for MSSQLSERVER. If SQL Server is installed as an instance, MSSQLSERVER will actually be the name of the instance.
  4. From the context menu, click Properties to launch the Protocols for MSSQLSERVER Properties window.
  5. On the Flags tab, select Force Encryption and change the value to No.
  6. On the Certificate tab, click [Clear].
  7. Click [OK] to close the Protocols for MSSQLSERVER Properties window.
  8. In the SQL Server Configuration Manager window, select SQL Server Services.
  9. Right-click on SQL Server (MSSQLSERVER) on the right side of the window.
  10. From the context menu, click Restart.