{"id":11747,"date":"2022-03-15T10:30:59","date_gmt":"2022-03-15T09:30:59","guid":{"rendered":"https:\/\/sdna.lu\/?p=11747"},"modified":"2025-09-01T10:13:55","modified_gmt":"2025-09-01T09:13:55","slug":"enable-encrypted-connections-to-the-database-engine","status":"publish","type":"post","link":"https:\/\/sdna.lu\/?p=11747","title":{"rendered":"Enable Transport Layer Security (TLS) on SQL Server"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">SQL Server now supports <strong>Transport Layer Security (TLS)<\/strong> to encrypt data transmitted between clients and the server. Using TLS ensures that your data remains secure and protected from eavesdropping.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This guide explains how to create a suitable certificate, bind it to SQL Server, and verify that TLS encryption is working.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">1. Requirements for the Certificate<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Your SQL Server must have a certificate that meets the following criteria:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>FQDN Matching<\/strong>: The certificate\u2019s name must match the <strong>fully qualified domain name (FQDN)<\/strong> of your SQL Server.<\/li>\n\n\n\n<li><strong>Server Authentication<\/strong>: The certificate must be issued for <strong>Server Authentication<\/strong>.<\/li>\n\n\n\n<li><strong>Key Specification<\/strong>: Use <code>AT_KEYEXCHANGE<\/code> (not <code>AT_SIGNATURE<\/code>) to allow SQL Server to securely exchange encryption keys with client computers.<\/li>\n\n\n\n<li><strong>Trusted Authority<\/strong>: Sign your server certificate with your <strong>Enterprise Certificate Authority (CA)<\/strong>, which should already be trusted and installed on all client machines.<\/li>\n<\/ul>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\">Using a trusted CA avoids warnings on client machines and ensures secure communication.<\/p>\n<\/blockquote>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">2. Creating a Server Certificate (PowerShell Example)<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Administrators can create a certificate for SQL Server using PowerShell. This example creates a <strong>self-signed certificate<\/strong> that:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Authenticates your SQL Server<\/li>\n\n\n\n<li>Provides an encryption key for secure communication<\/li>\n\n\n\n<li>Is valid for 3 years<\/li>\n\n\n\n<li>Allows the key to be exported<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>&lt;# Create a certificate signed by our Enterprise Certificate Authority to authentify your SQL server and give it an encryption key it can exchange with your computers to encrypt the connections. Make it valid for 3 years and make that key exportable so you can install it on your server#&gt;\n$certSQLServerEncrypt = New-SelfSignedCertificate -Subject \u201cYOURSQLSRV.YOURDOMAIN.local\u201d -KeySpec KeyExchange -type SSLServerAuthentication -CertStoreLocation \u201ccert:\\LocalMachine\\my\u201d -NotAfter (Get-Date).AddYears(3) -KeyExportPolicy Exportable -Signer $certEnterpriseCA\n<\/code><\/pre>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\"><strong>Tip:<\/strong> Replace <code>YOURSQLSRV.YOURDOMAIN.local<\/code> with your SQL Server\u2019s actual FQDN.<\/p>\n<\/blockquote>\n\n\n\n<p class=\"wp-block-paragraph\">If you don\u2019t have an Enterprise CA, you can create a self-signed certificate like this:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>New-SelfSignedCertificate `\n    -Subject \"YOURSQLSRV.YOURDOMAIN\" `\n    -DnsName \"YOURSQLSRV.YOURDOMAIN\",\"YOURSQLSRV\" `\n    -CertStoreLocation \"cert:\\LocalMachine\\My\" `\n    -KeySpec KeyExchange `\n    -KeyExportPolicy Exportable `\n    -NotAfter (Get-Date).AddYears(3) `\n    -FriendlyName \"SQL Server TLS Certificate\" `\n    -TextExtension @(\"2.5.29.37={text}1.3.6.1.5.5.7.3.1\")\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">After creating the certificate, ensure the SQL Server service has read access to the private key. To do this, follow these steps:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Open <strong>MMC<\/strong> (Microsoft Management Console).<\/li>\n\n\n\n<li>Add the <strong>Certificates<\/strong> snap-in for <strong>Local Computer<\/strong>.<\/li>\n\n\n\n<li>Navigate to <strong>Certificates (Local Computer) \u2192 Personal \u2192 Certificates<\/strong>, then right-click your certificate.<\/li>\n\n\n\n<li>Select <strong>All Tasks \u2192 Manage Private Keys\u2026<\/strong><\/li>\n\n\n\n<li>In the <strong>Security<\/strong> tab, add the SQL Server service. Usually, it is named like <code>NT Service\\MSSQL$SQL2022<\/code> (for SQL Server 2022).<\/li>\n\n\n\n<li>Allow <strong>Full Control<\/strong> and <strong>Read<\/strong> rights for the service.<\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">3. Binding the Certificate to SQL Server<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Once the certificate is created:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Open <strong>SQL Server Configuration Manager<\/strong>.<\/li>\n\n\n\n<li>Navigate to <strong>SQL Server Network Configuration \u2192 Protocols for [Your Instance]<\/strong>.<\/li>\n\n\n\n<li>Right-click <strong>Properties \u2192 Certificate<\/strong> and select your new certificate.<\/li>\n\n\n\n<li>Under <strong>Flags<\/strong>, set <strong>Force Protocol Encryption<\/strong> (old flag: <em>Force Encryption<\/em>) to <code>Yes<\/code> if you want all connections to use TLS.<\/li>\n\n\n\n<li>Restart the SQL Server service to apply the changes.<\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">4. Recommended TLS Versions<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use <strong>TLS 1.2 or 1.3<\/strong>, as older versions (TLS 1.0\/1.1) are considered insecure.<\/li>\n\n\n\n<li>Ensure both server and clients support the selected TLS version.<\/li>\n<\/ul>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\">You can verify or disable TLS versions in Windows using registry settings or group policies.<\/p>\n<\/blockquote>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">5. Verifying the TLS Configuration<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">After binding the certificate, you can verify TLS encryption with:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>SQL Query:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT session_id, encrypt_option\nFROM sys.dm_exec_connections\nWHERE session_id = @@SPID;\n<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>encrypt_option<\/code> should return <code>TRUE<\/code> for an encrypted connection.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>PowerShell:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Get-ChildItem -Path Cert:\\LocalMachine\\My\n<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Ensure your certificate appears in the personal store and is valid.<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">6. Client Trust and Deployment<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Ensure client machines <strong>trust your Enterprise CA<\/strong>.<\/li>\n\n\n\n<li>If using self-signed certificates without a trusted CA, clients may show warnings or fail to connect.<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">7. Renewal and Expiration<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Monitor your certificate\u2019s expiration date.<\/li>\n\n\n\n<li>Renew or replace the certificate <strong>before it expires<\/strong> to avoid connection failures.<\/li>\n\n\n\n<li>Export the certificate and key if needed for deployment on other servers.<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Summary<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Enabling TLS on SQL Server protects your data and ensures secure communication. By following this guide, you will:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Create a properly configured certificate<\/li>\n\n\n\n<li>Bind it to SQL Server<\/li>\n\n\n\n<li>Enforce modern TLS standards<\/li>\n\n\n\n<li>Verify encrypted connections<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Sources: <\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/database-engine\/configure-windows\/enable-encrypted-connections-to-the-database-engine?view=sql-server-ver15\">Microsoft: Enable encrypted connections to the Database Engine<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/docs.microsoft.com\/en-us\/previous-versions\/sql\/sql-server-2012\/ms191192(v=sql.110)?redirectedfrom=MSDN#configureserverconnections\">Microsoft: Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager)<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/www.sqlshack.com\/how-to-set-and-use-encrypted-sql-server-connections\/\">SQLShack: How to set and use encrypted SQL Server connections<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/stackoverflow.com\/questions\/36817627\/ssl-certificate-missing-from-dropdown-in-sql-server-configuration-manager\">Stackoverflow: SSL Certificate missing from dropdown in SQL Server Configuration Manager627\/ssl-certificate-missing-from-dropdown-in-sql-server-configuration-manager<\/a><\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>SQL Server now supports Transport Layer Security (TLS) to encrypt data transmitted between clients and the server. Using TLS ensures that your data remains secure and protected from eavesdropping. This guide explains how to create a suitable certificate, bind it to SQL Server, and verify that TLS encryption is working. 1. Requirements for the Certificate [&hellip;]<\/p>\n","protected":false},"author":19,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-11747","post","type-post","status-publish","format-standard","hentry","category-non-classe"],"_links":{"self":[{"href":"https:\/\/sdna.lu\/index.php?rest_route=\/wp\/v2\/posts\/11747","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sdna.lu\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sdna.lu\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sdna.lu\/index.php?rest_route=\/wp\/v2\/users\/19"}],"replies":[{"embeddable":true,"href":"https:\/\/sdna.lu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=11747"}],"version-history":[{"count":13,"href":"https:\/\/sdna.lu\/index.php?rest_route=\/wp\/v2\/posts\/11747\/revisions"}],"predecessor-version":[{"id":12707,"href":"https:\/\/sdna.lu\/index.php?rest_route=\/wp\/v2\/posts\/11747\/revisions\/12707"}],"wp:attachment":[{"href":"https:\/\/sdna.lu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=11747"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sdna.lu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=11747"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sdna.lu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=11747"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}