{"id":42851,"date":"2026-02-12T13:31:22","date_gmt":"2026-02-12T12:31:22","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=42851"},"modified":"2026-02-13T09:46:32","modified_gmt":"2026-02-13T08:46:32","slug":"automate-encryption-in-transit-for-sql-server","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/automate-encryption-in-transit-for-sql-server\/","title":{"rendered":"Automate encryption-in-transit for SQL Server"},"content":{"rendered":"\n<p>Encryption-in-transit is part of the essential security baseline for SQL Server.<br>Without TLS, TDS packets travel in clear text across the network, making them vulnerable to interception or modification by an attacker attempting to gain access to the system.<br>This is why TLS has become a standard in modern SQL Server architectures.<\/p>\n\n\n\n<p><br>In this article, we will look at how to standardize and automate this deployment using PowerShell.<br>This will provide the core of the TLS setup, based on a certificate name and an instance name.<br>For full generalization, it only requires to loop over a list of instances, or to integrate it into an Ansible playbook, for example.<\/p>\n\n\n\n<p>In our automation, we assume that the certificate is already present in <code>Cert:\\LocalMachine\\My<\/code> (Local Machine Personal). The certificate is <strong>ws202201.dbi.test<\/strong> in this output:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: powershell; gutter: false; title: ; notranslate\" title=\"\">\nPS&gt; ls cert:\\LocalMachine\\My\n\n\n   PSParentPath: Microsoft.PowerShell.Security\\Certificate::LocalMachine\\My\n\nThumbprint                                Subject\n----------                                -------\nE6DB2D082B41B36F08806EA82B25064150618E1F  CN=CLIUSR\nD4EBB1260BECFD28254082A734FD4D5663199B16  CN=CLIUSR\n30B20A2A54A3DAB60E6AC601B94F6E5A626D7C2D  CN=ws202201.dbi.test\n<\/pre><\/div>\n\n\n<p>The first step is to give the SQL Server service account read permissions on the certificate\u2019s private key, otherwise the instance will not be able to use the certificate for TLS.<br>The script uses two input parameters : <strong>Instance Name<\/strong> of SQL Server and <strong>Friendly Name<\/strong> of the certificate.<br><\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: powershell; gutter: false; title: ; notranslate\" title=\"\">\n#---- Variables -------------------------------------------------------------\n$InstanceName = &#039;INSTANCE1&#039;;     # Instance name to adapt\n$FriendlyName = &#039;Instance1 TLS&#039;; # Certificate Friendly Name to adapt\n\n#---- Begin Script ----------------------------------------------------------\n# Construct Virtual Account for SQL Server instance\nIf ($InstanceName -eq &#039;MSSQLSERVER&#039;) {\n    $user = &#039;NT SERVICE\\MSSQLSERVER&#039;;\n} else {\n    $user = &quot;NT SERVICE\\$(&#039;MSSQL$&#039;+$InstanceName)&quot;;\n};\n\n# Retrieve certificate based on Friendly Name\n$cert = Get-ChildItem -Path &#039;Cert:\\LocalMachine\\My&#039; ` \n| Where-Object {$_.FriendlyName -eq &quot;$FriendlyName&quot;};\n\n# Give Virtual Account read permission\n$permission = &quot;Read&quot;; \n$rule = New-Object System.Security.AccessControl.FileSystemAccessRule($user, $permission, &quot;Allow&quot;); \n$keyPath = &#x5B;System.IO.Path]::Combine(&quot;C:\\ProgramData\\Microsoft\\Crypto\\RSA\\MachineKeys&quot;, $cert.PrivateKey.CspKeyContainerInfo.UniqueKeyContainerName); \n$acl = Get-Acl -Path $keyPath;\n$acl.SetAccessRule($rule); \nSet-Acl -Path $keyPath -AclObject $acl;\n<\/pre><\/div>\n\n\n<p>The next step is to reference the certificate\u2019s thumbprint in the SQL Server registry configuration.<br>A key detail here: while PowerShell is <strong>case-insensitive<\/strong>, the thumbprint stored in the registry is <strong>case-sensitive<\/strong>. SQL Server expects the value to be written in lowercase; otherwise, the instance will not load the certificate at startup.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: powershell; gutter: false; title: ; notranslate\" title=\"\">\n# Root registry entry of instance (Regex)\n$Pattern = &quot;MSSQL\\d\\d\\.$($InstanceName)&quot;;\n\n# Construct path where is stored Certificate property\n$RegMSSQL = &#039;HKLM:\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\&#039;;\n$RegRootInstance = Get-childItem -Path $RegMSSQL `\n| Where-Object Name -match &quot;$Pattern&quot;;\n\n# Update Certificate property with certificate thrumprint in lower case\n$RegTCPCertificate = &#039;MSSQLServer\\SuperSocketNetLib\\&#039;;\nSet-ItemProperty ` \n-Path (Join-Path -Path $RegRootInstance.PSPath -ChildPath $RegTCPCertificate) `\n-Name &#039;Certificate&#039; -Value &quot;$($cert.Thumbprint.ToLower())&quot;;\n<\/pre><\/div>\n\n\n<p>All that remains is to restart the SQL Server service to apply the changes.<\/p>\n\n\n\n<p>To confirm encryption, connect SQL Server and run the following T-SQL command:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; gutter: false; title: ; notranslate\" title=\"\">\nSELECT \n  session_id \n  ,encrypt_option\nFROM sys.dm_exec_connections \nWHERE session_id = @@SPID\n\n\nsession_id encrypt_option\n---------- --------------\n        60 TRUE\n<\/pre><\/div>\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Encryption-in-transit is part of the essential security baseline for SQL Server.Without TLS, TDS packets travel in clear text across the network, making them vulnerable to interception or modification by an attacker attempting to gain access to the system.This is why TLS has become a standard in modern SQL Server architectures. In this article, we will [&hellip;]<\/p>\n","protected":false},"author":14,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[99],"tags":[2564,51],"type_dbi":[],"class_list":["post-42851","post","type-post","status-publish","format-standard","hentry","category-sql-server","tag-security-3","tag-sql-server"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Automate encryption-in-transit for SQL Server - dbi Blog<\/title>\n<meta name=\"description\" content=\"Automate SQL Server encryption-in-transit using PowerShell. Configure TLS certificates, private key permissions, and registry settings to secure SQL Server connections.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/automate-encryption-in-transit-for-sql-server\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Automate encryption-in-transit for SQL Server\" \/>\n<meta property=\"og:description\" content=\"Automate SQL Server encryption-in-transit using PowerShell. Configure TLS certificates, private key permissions, and registry settings to secure SQL Server connections.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/automate-encryption-in-transit-for-sql-server\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2026-02-12T12:31:22+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2026-02-13T08:46:32+00:00\" \/>\n<meta name=\"author\" content=\"Nathan Courtine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Nathan Courtine\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/automate-encryption-in-transit-for-sql-server\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/automate-encryption-in-transit-for-sql-server\/\"},\"author\":{\"name\":\"Nathan Courtine\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1\"},\"headline\":\"Automate encryption-in-transit for SQL Server\",\"datePublished\":\"2026-02-12T12:31:22+00:00\",\"dateModified\":\"2026-02-13T08:46:32+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/automate-encryption-in-transit-for-sql-server\/\"},\"wordCount\":265,\"commentCount\":0,\"keywords\":[\"Security\",\"SQL Server\"],\"articleSection\":[\"SQL Server\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/automate-encryption-in-transit-for-sql-server\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/automate-encryption-in-transit-for-sql-server\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/automate-encryption-in-transit-for-sql-server\/\",\"name\":\"Automate encryption-in-transit for SQL Server - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2026-02-12T12:31:22+00:00\",\"dateModified\":\"2026-02-13T08:46:32+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1\"},\"description\":\"Automate SQL Server encryption-in-transit using PowerShell. Configure TLS certificates, private key permissions, and registry settings to secure SQL Server connections.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/automate-encryption-in-transit-for-sql-server\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/automate-encryption-in-transit-for-sql-server\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/automate-encryption-in-transit-for-sql-server\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Automate encryption-in-transit for SQL Server\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1\",\"name\":\"Nathan Courtine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g\",\"caption\":\"Nathan Courtine\"},\"description\":\"Nathan Courtine has more than four years of experience in Microsoft solutions. He is specialized in SQL Server installation, migration, performance analysis, best practices, etc. Moreover, he has a background in Oracle Java and .NET software and web development. Nathan Courtine is Microsoft Certified in Administering SQL Server 2012 Databases. Nathan Courtine holds an Engineer\u2019s Degree in Computer Science from the ENSISA (Ecole Nationale Sup\u00e9rieure d'Ing\u00e9nieurs Sud Alsace) in Mulhouse (F). His branch-related experience covers Public Sector, Automotive, IT, Financial Services \/ Banking, etc.\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/nathan-courtine\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Automate encryption-in-transit for SQL Server - dbi Blog","description":"Automate SQL Server encryption-in-transit using PowerShell. Configure TLS certificates, private key permissions, and registry settings to secure SQL Server connections.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/automate-encryption-in-transit-for-sql-server\/","og_locale":"en_US","og_type":"article","og_title":"Automate encryption-in-transit for SQL Server","og_description":"Automate SQL Server encryption-in-transit using PowerShell. Configure TLS certificates, private key permissions, and registry settings to secure SQL Server connections.","og_url":"https:\/\/www.dbi-services.com\/blog\/automate-encryption-in-transit-for-sql-server\/","og_site_name":"dbi Blog","article_published_time":"2026-02-12T12:31:22+00:00","article_modified_time":"2026-02-13T08:46:32+00:00","author":"Nathan Courtine","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Nathan Courtine","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/automate-encryption-in-transit-for-sql-server\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/automate-encryption-in-transit-for-sql-server\/"},"author":{"name":"Nathan Courtine","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1"},"headline":"Automate encryption-in-transit for SQL Server","datePublished":"2026-02-12T12:31:22+00:00","dateModified":"2026-02-13T08:46:32+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/automate-encryption-in-transit-for-sql-server\/"},"wordCount":265,"commentCount":0,"keywords":["Security","SQL Server"],"articleSection":["SQL Server"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/automate-encryption-in-transit-for-sql-server\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/automate-encryption-in-transit-for-sql-server\/","url":"https:\/\/www.dbi-services.com\/blog\/automate-encryption-in-transit-for-sql-server\/","name":"Automate encryption-in-transit for SQL Server - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2026-02-12T12:31:22+00:00","dateModified":"2026-02-13T08:46:32+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1"},"description":"Automate SQL Server encryption-in-transit using PowerShell. Configure TLS certificates, private key permissions, and registry settings to secure SQL Server connections.","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/automate-encryption-in-transit-for-sql-server\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/automate-encryption-in-transit-for-sql-server\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/automate-encryption-in-transit-for-sql-server\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Automate encryption-in-transit for SQL Server"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1","name":"Nathan Courtine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g","caption":"Nathan Courtine"},"description":"Nathan Courtine has more than four years of experience in Microsoft solutions. He is specialized in SQL Server installation, migration, performance analysis, best practices, etc. Moreover, he has a background in Oracle Java and .NET software and web development. Nathan Courtine is Microsoft Certified in Administering SQL Server 2012 Databases. Nathan Courtine holds an Engineer\u2019s Degree in Computer Science from the ENSISA (Ecole Nationale Sup\u00e9rieure d'Ing\u00e9nieurs Sud Alsace) in Mulhouse (F). His branch-related experience covers Public Sector, Automotive, IT, Financial Services \/ Banking, etc.","url":"https:\/\/www.dbi-services.com\/blog\/author\/nathan-courtine\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/42851","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/14"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=42851"}],"version-history":[{"count":17,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/42851\/revisions"}],"predecessor-version":[{"id":42887,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/42851\/revisions\/42887"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=42851"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=42851"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=42851"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=42851"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}