How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Microsoft Remote Desktop Protocol (RDP) and Microsoft SQL server have options for self-signed certificates as their default. If you connect to a server for administrative purposes it uses RDP protocol and a self-signed certificate by default.
This article provides instructions on using a single TLS Certificate for Microsoft Remote Desktop Protocol (RDP) and Microsoft SQL server instances. It provides additional information for utilizing that same certificate to encrypt SQL traffic. The instructions allow for sharing a single TLS certificate per SQL server instance AND for Remote Desktop encryption.
This results in a cost save where third-party certificates are used, in that it uses one certificate per server per fully-qualified-domain-name (FQDN). One certificate to perform two or more encryption tasks might reduce your cost where two certificates often used. This article addresses the self-signed certificate issue with RDP connections allowing third-party certificate used for SQL to encrypt RDP traffic or vice versa. This addresses SQL instances as well creating further cost saving.
Using a third-party or internal issued Certificate file (CER)
Certificate Request (CSR), and private key exist in the Microsoft Crypto Store on the RDP/SQL Server
RDP – REMOTE DESKTOP ENCRYPTION (RDP PROTOCOL)
Open Remote Desktop Session Host Configuration
Under Connections > Right Click RDP-Tcp > Properties > General Tab > Click Select
Confirm New Certificate > Click OK
Confirm > Apply > OK
SQL ENCRYPTION UTILIZING SAME CERTIFICATE (HASH)
1. Logon to server with Administrative rights
2. Click on Start > Run > Type MMC.exe (do not use certmgr.msc)
3. Click File > Add Snapin > Certificates > Choose “Machine” > Ok
4. Verify view is “Local Computer” 5. Expand Personal > Certificates and validate most updated certificate installed
6. Minimize MMC Console to Task Bar for later reference 7. Click Start > Run > Type “Services.msc” > Enter
8. Scroll down to SQL Server > Document service accounts for every SQL Service 9. Go BACK to Custom MMC
10. Right Click Certificate located under Local Computer\Personal\Certificates
11. Choose All Tasks > Manage Private Keys 12. While logged on as Administrator > Click ADD 13. Click LOCATIONS > Set to Computername 14. Click Advanced > Verify Computer Name Location > Click Find Now 15. Using CTRL Key and Mouse Select Every SQL Account (NOT GROUP) as shown and click OK 16. Click OK Again after validation all accounts correct 17. Verify that Every SQL Local Account has “Full Control”. Highlight each one and validate. Click OK when done. 18. Click Start – Run – CMD
19. Type “certutil.exe –store MY” > Locate the Cert Hash for the corresponding Certificate 20. Right click in DOS window > Choose “MARK”
21. Highlight the Cert Hash string with Mouse then Press Enter (to Copy) 22. Open Notepad > Press CTRL + P to paste the hash in notepad
23. Remove all the spaces 24. Highlight Text, Right Click and Copy or CTRL + C
25. Open Registry Editor (regedit)
26. Go here: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\SuperSocketNetLib
27. Make sure you use the INSTANCE NAME as shown below 28. Add a STRING Value called “Certificate” 29. Copy and Paste the Hash Value to Certificate as indicated 30. NOTE: There might be more than one key. Check ALL INSTANCES 31. Add Required SQL Permissions (Local Server accounts) 32. Click OK
33. Restart SQL Server Service
34. Open Event Viewer > Application Log
35. Locate EventID 26013 and validate that Certificate was successfully bound and loaded as indicated below:
36. You can now enable Encryption on any down level client (Citrix Server) and it will encrypt all traffic
37. Do not enforce Encryption from the SQL Server
Example OPTIONAL STEP - FORCE ENCRYPTION
Current state after following all steps allows SQL to encrypt any session that requests encryption. This can, however, be forced using the following steps. All client machines must be configured properly. All clients must have the Internal CA Certificate in the Local Machine Store.
1. Start “SQL Server Configuration Manager”
2. Expand SQL Server Network Configuration
3. Double Click “Protocols for <INSTANCENAME>
4. Enable Force Encryption to “Yes” using SQL Server Configuration Manager. Note: DO NOT navigate to the Certificate tab and try to make any changes there. Just ignore the certificate tab altogether since we have directly modified the registry.
You can enable the Force Protocol Encryption option on the server or on the client.
Do not enable the Force Protocol Encryption option on both the client and the server.
To enable Force Protocol Encryption on the server, use the Server Network Utility or SQL Server Configuration Manager.
To enable Force Protocol Encryption on the client, use the Client Network Utility or SQL Server Configuration Manager.
Important: If you enable SSL encryption by using the Client Network Utility or SQL Native Client <version> Configuration (32 bit) or SQL Native Client <version> Configuration pages in SQL Server Configuration Manager, all connections from that client will request SSL encryption to any SQL Server to which that client connects.
If you want to enable Force Protocol Encryption on the client, you must have a certificate on the server and the client must have the Trusted Root Authority updated to trust the server certificate.