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.
Self-signed certificates might show as a vulnerability to some security scanning software. Hardening the operating system requires removing these self-signed certificates per this article "SSL/TLS Vulnerabilities and Operating System Hardening."
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.
RDP – REMOTE DESKTOP ENCRYPTION (RDP PROTOCOL)
- 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
SQL ENCRYPTION UTILIZING SAME CERTIFICATE (HASH)
- 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
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
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.
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.
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.
- 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.
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.
Encrypting Connections to SQL Server
How to enable SSL encryption for an instance of SQL Server by using Microsoft Management Console
How to Enable Channel Encryption
SQL Server fails to start with error 17182 "TDSSNIClient initialization failed with error 0xd, status code 0x38" when server is configured to use SSL
Citrix XenDesktop 7.6 Citrix Policies Advanced Printing
Citrix XenDesktop 7.6 Citrix Policies - Lock Down Peripherals
Citrix XenDesktop 7.6 Citrix Policies - Graphics and Audio
Citrix XenDesktop 7.6 Core Software Install
Citrix XenDesktop 7.6 VMware SDK Certificate
Citrix XenDesktop 7.6 Citrix Studio Configure New Site
SSL/TLS Vulnerabilities and Operating System Hardening
Does this shared knowledge provide value? If this article has value please click on "Good Article" button to your right. Knowing this knowledge is valued by others is motivation to continue sharing.