Share One TLS Certificate for Remote Desktop Services and SQL Server

Brian MurphySenior Information Technology Consultant
CERTIFIED EXPERT
Holistic technology infrastructure strategy, design, engineering and implementation that is highly scalable, secure, optimized, automated
Published:
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.

Assumptions:

  1. Using a third-party or internal issued Certificate file (CER)
  2. Certificate Request (CSR), and private key exist in the Microsoft Crypto Store on the RDP/SQL Server
RDP – REMOTE DESKTOP ENCRYPTION (RDP PROTOCOL)
  1. Open Remote Desktop Session Host Configuration
  2. Under Connections > Right Click RDP-Tcp > Properties > General Tab > Click Select
  3. Confirm New Certificate > Click OK
  4. Confirm > Apply > OK
image1.pngSQL 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”
image3.png5.    Expand Personal > Certificates and validate most updated certificate installed
6.    Minimize MMC Console to Task Bar for later reference
image4.png7.    Click Start > Run > Type “Services.msc” > Enter
8.    Scroll down to SQL Server > Document service accounts for every SQL Service
image5.png9.    Go BACK to Custom MMC
10.    Right Click Certificate located under Local Computer\Personal\Certificates
11.    Choose All Tasks > Manage Private Keys
image6.png12.    While logged on as Administrator > Click ADD
image7.png13.    Click LOCATIONS > Set to Computername
image8.png14.    Click Advanced > Verify Computer Name Location > Click Find Now
image10.png15.    Using CTRL Key and Mouse Select Every SQL Account (NOT GROUP) as shown and click OK
image11.png16.    Click OK Again after validation all accounts correct
image12.png17.    Verify that Every SQL Local Account has “Full Control”.  Highlight each one and validate.  Click OK when done.
image13.png18.    Click Start – Run – CMD
19.    Type “certutil.exe –store MY” > Locate the Cert Hash for the corresponding Certificate
image14.png20.    Right click in DOS window > Choose “MARK
21.    Highlight the Cert Hash string with Mouse then Press Enter (to Copy)
image15.png22.    Open Notepad > Press CTRL + P to paste the hash in notepad
23.    Remove all the spaces
image16.png24.    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
image17.png28.    Add a STRING Value called “Certificate
image18.png29.    Copy and Paste the Hash Value to Certificate as indicated
image19.png30.    NOTE: There might be more than one key.  Check ALL INSTANCES
image20.png31.    Add Required SQL Permissions (Local Server accounts)
image22.png32.    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:
image23.png
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 
38.    Complete

Example
image24.pngOPTIONAL 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.
image25.pngNote:
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.

ADDITIONAL REFERENCE
Force Encryption
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


ADDITIONAL READING

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


ACKNOWLEDGEMENT

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.  
0
3,431 Views
Brian MurphySenior Information Technology Consultant
CERTIFIED EXPERT
Holistic technology infrastructure strategy, design, engineering and implementation that is highly scalable, secure, optimized, automated

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.