KB10119 - MSSQL: Implement SSL/TLS

KB10119 - MSSQL: Implement SSL/TLS

I am not the SQL guy but this "problem" was so stupid and there was no real description on one page what to manage to enable SSL/TLS on MSSQL Servers so I decided to write down the required steps just to know and to remember...

Steps:

  1. Request a certificate
    1. Open a MMC and add the certificates snap in for the local computer
    2. Go to Personal and request a new certificate
      This description is written in an Active Directory environment so there is a MS CA with policies enrolled
    3. Context menu -> All Tasks -> Advanced Operations -> Create Custom Request...
      1. You can use a certificate with an enrolment policy
      2. OU you can complete with a custom request (in this case
    4. Select a Template which is used for "Server Authentication" (OID: 1.3.6.1.5.5.7.3.1)
      If you work without policy select "no template legacy key"
    5. On the "General" tab key in a friendly name for the certificate (I prefer to use the DNS alias in here...)
    6. On the "Subject" Tab
      In the screenshot the REAL FQDN name is "srvsql1.schnitzelbroetchen.info" and the DNS alias is "mssql.schnitzelbroetchen.info"
      • Select "Common name" as subject and enter the servers REAL FQDN name here (((Get-WmiObject win32_computersystem).DNSHostName+"."+(Get-WmiObject win32_computersystem).Domain).Tolower())
      • Add this name also as alternative name with type DNS (Not mandatory)
      • If the clients use a DNS alias for the connection you can add additional names here.
    7. On the "Extensions" Tab
      • Ensure "Digital signature" and "Key encipherment" is enabled as Key usage
      • and "Server Authentication" is enabled as Extended Key usage
    8. On the "Private Key" key tab
      • Select "Microsoft RSA SChannel Cryptographic Provider (Encryption)" and "Microsoft DH SChannel Cryptographic Provider (Encryption)" as CSPs
      • Select a key size with at least 2048 bit
      • Make the private key exportable (if allowed an required)
      • Select "Use custom permissions" under Key permissions
      • Add the username under whitch the SQL service gest started (you can find this one in the services.msc)
    9. Now you can save the request as file or send it directly to an online CA
    10. If the request is saved to a file you have to request a new certificate for this request.
    11. If you have to manually import the certificate
      • Open the context menu of the certificates mmc and select All Tasks -> Import
      • Open the certificate file an select personal as target store
  2. Install the certificate to MSSQL
    At this point the certificate including the private key is installed on the SQL Server
    1. Open the "SQL Server 20xx Configuration Manager"
    2. Go to "SQL Server Network Configuration"
    3. right click "Protocols for xxx" where xxx is you instance name and select "Properties"
    4. On the "Certificate" Tab select the new certificate created
      If it is not visible here you probable missed something in the request ;-)
    5. To enforce encryption select "Yes" in "Force Encryption" in the "Flags" tab
  3. Restart SQL service
    1. When the service starts everything is fine and you now can or have to use encrypted connections to the SQL Server
    2. If not please check the event log in my case the "problem" was always that the service account running SQL service had no permission to the certificates private key.