Windows OS Hub
  • Windows Server
    • Windows Server 2022
    • Windows Server 2019
    • Windows Server 2016
    • Windows Server 2012 R2
    • Windows Server 2012
    • Windows Server 2008 R2
    • SCCM
  • Active Directory
    • Active Directory Domain Services (AD DS)
    • Group Policies
  • Windows Clients
    • Windows 11
    • Windows 10
    • Windows 8
    • Windows 7
    • Windows XP
    • MS Office
    • Outlook
  • Virtualization
    • VMWare
    • Hyper-V
    • KVM
  • PowerShell
  • Exchange
  • Cloud
    • Azure
    • Microsoft 365
    • Office 365
  • Linux
    • CentOS
    • RHEL
    • Ubuntu
  • Home
  • About

Windows OS Hub

  • Windows Server
    • Windows Server 2022
    • Windows Server 2019
    • Windows Server 2016
    • Windows Server 2012 R2
    • Windows Server 2012
    • Windows Server 2008 R2
    • SCCM
  • Active Directory
    • Active Directory Domain Services (AD DS)
    • Group Policies
  • Windows Clients
    • Windows 11
    • Windows 10
    • Windows 8
    • Windows 7
    • Windows XP
    • MS Office
    • Outlook
  • Virtualization
    • VMWare
    • Hyper-V
    • KVM
  • PowerShell
  • Exchange
  • Cloud
    • Azure
    • Microsoft 365
    • Office 365
  • Linux
    • CentOS
    • RHEL
    • Ubuntu

 Windows OS Hub / SQL Server / Configure SSL Connection Encryption in MS SQL Server

August 15, 2022 SQL ServerWindows Server 2019

Configure SSL Connection Encryption in MS SQL Server

If you are using untrusted/public networks to connect clients/apps to your Microsoft SQL Server database, it is recommended to use traffic encryption. Let’s see how to configure and enable mandatory SSL/TLS traffic encryption for client connection to Microsoft SQL Server.

In MS SQL 2016 and newer, TLS is used instead of SSL.

To enable encryption for an MS SQL Server connection, you need to a TLS/SSL certificate on your server. It may be a commercial certificate, a certificate issued by your CA, or a self-signed certificate. A TLS certificate must meet the following requirements:

  • Enhanced Key Usage — Server Authentication 1.3.6.1.5.5.7.3.1 ;
  • Valid expiration date;
  • The certificate must be located in the user (certmgr.msc) or computer (certlm.msc) certificate store;
  • The certificate must have the server name on which MSSQL is running in its Subject Name;
  • Clients must trust the certificate.

You can create a self-signed certificate using PowerShell (it is recommended to use self-signed certificates in test environments only):

New-SelfSignedCertificate -DnsName srvdb01.woshub.com -CertStoreLocation cert:\LocalMachine\My

If you are going to access your MSSQL instance not only by hostname but also by other names (for example, if an Always-On High Availability Group listener with an RDS Connection Broker database is located on the server), add all available names to the Subject Alternative Name. To issue a self-signed certificate with multiple SANs valid for three years:

$curdate = Get-Date
$add3year = $curdate.AddYears(3)
$newcert=New-SelfSignedCertificate -DnsName srvdb01, srvdb01.woshub.loc,SQLRDDBCL.woshub.com -CertStoreLocation cert:\LocalMachine\My -notafter $add3year

Import the certificate to the list of trusted certificates:

$certFile = Export-Certificate -Cert $newcert -FilePath C:\certname.cer
Import-Certificate -CertStoreLocation Cert:\LocalMachine\AuthRoot -FilePath $certFile.FullName

sql server server authenticatin ssl certificate

If you are using a certificate from your CA or an external CA, import it to the computer certificate store (or user certificate store if the MS SQL Server is running under a user or gMSA managed account). If the SQL Server service is running under an account without local administrator privileges, grant it read privileges for the certificate.

Then bind your certificate to the installed SQL Server instance:

  1. Run the SQL Server Configuration Manager, go to SQL Server Network Configuration, and open the properties of Protocols for MSSQLSERVER;
  2. Enable Force Encryption; force ssl encryption for sql server connections
  3. Go to the Certificate tab and select the certificate you imported to the user or computer certificate store in the dropdown list; bind certificate to sql server
  4. Restart MS SQL to apply the changes.

To force the client to always use an encrypted connection to your MSSQL server:

  • Open Server Configuration Manager on the client -> SQL Native Client Configuration -> Properties -> enable Force Protocol Encryption. Force Protocol Encryption in SQL Server Native Client
  • You can also use the following connection string that makes the client use SSL/TLS to connect to the MSSQL server: Data Source=srvdb01.woshub.com;Integrated Security=False;User ID=test;Password=[Password];Encrypt=True
Clients must trust the certificate used to encrypt traffic. Otherwise, the following error will appear at the connection:

Cannot connect to SRVDB01.
A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (Microsoft SQL Server, Error: -2146893019).

You can add a certificate to the trusted root list on domain computers using GPO.

To make SQL Server Management Studio (SSMS) always use encryption to connect to an SQL Server database, open the connection window (Connect to Server) -> Options -> enable the Encrypt Connection option.

Encrypt connection in SQL Server Management Studio

If “The target principal name is incorrect” error appears, make sure you are using the SQL Server name specified in the Subject Name or the certificate SAN.

sql server error The target principal name is incorrect

You can connect to your SQL Server using the SqlServer PowerShell module and make sure if your SQL Server supports connection encryption:

Invoke-Sqlcmd -ServerInstance "srvdb01" -Query "SELECT DISTINCT encrypt_option FROM sys.dm_exec_connections WHERE session_id = @@SPID"

If your SQL Server instance is not running on the default port, t is specified as follows: -ServerInstance "srvdb01,23223"

Invoke-Sqlcmd - check if encryption is enabled

In our example, the SQL Server supports encrypted connections (encrypt_option=TRUE).

Azure SQL databases support encrypted connections only. Even if the Encrypt connection option in SSMS is not checked, the connection to Azure SQL will be encrypted (see the article Database Security in Azure SQL).

1 comment
1
Facebook Twitter Google + Pinterest
previous post
Managing Microsoft Teams with PowerShell
next post
How to Remove (Demote) a Domain Controller in Active Directory?

Related Reading

Using Previous Command History in PowerShell Console

January 31, 2023

How to Install the PowerShell Active Directory Module...

January 31, 2023

How to Disable or Uninstall Internet Explorer (IE)...

January 26, 2023

How to Delete Old User Profiles in Windows?

January 25, 2023

How to Enable TLS 1.2 on Windows?

January 18, 2023

1 comment

Mehmet September 7, 2022 - 1:09 pm

Hello,

First Thanks for the guide.

Can the server respond to non-SSL requests when this method is applied?

So I understand that the SSL certificate is absolutely necessary on the client side, is it true? Can you confirm?

Reply

Leave a Comment Cancel Reply

Categories

  • Active Directory
  • Group Policies
  • Exchange Server
  • Microsoft 365
  • Azure
  • Windows 11
  • Windows 10
  • Windows Server 2022
  • Windows Server 2019
  • Windows Server 2016
  • PowerShell
  • VMWare
  • Hyper-V
  • Linux
  • MS Office

Recent Posts

  • Using Previous Command History in PowerShell Console

    January 31, 2023
  • How to Install the PowerShell Active Directory Module and Manage AD?

    January 31, 2023
  • Finding Duplicate E-mail (SMTP) Addresses in Exchange

    January 27, 2023
  • How to Delete Old User Profiles in Windows?

    January 25, 2023
  • How to Install Free VMware Hypervisor (ESXi)?

    January 24, 2023
  • How to Enable TLS 1.2 on Windows?

    January 18, 2023
  • Allow or Prevent Non-Admin Users from Reboot/Shutdown Windows

    January 17, 2023
  • Fix: Can’t Extend Volume in Windows

    January 12, 2023
  • Wi-Fi (Internet) Disconnects After Sleep or Hibernation on Windows 10/11

    January 11, 2023
  • Adding Trusted Root Certificates on Linux

    January 9, 2023

Follow us

woshub.com
  • Facebook
  • Twitter
  • RSS
Popular Posts
  • MS SQL Server 2019 Installation Guide: Basic Settings and Recommendations
  • Upgrade Microsoft SQL Server Evaluation Edition to Standard/Enterprise
  • MS SQL Server Setup Stucks on Install/Uninstall
  • How to Reset SA Password on Microsoft SQL Server?
  • Change the Default Port Number (TCP/1433) for a MS SQL Server Instance
  • Starting SQL Server without TempDB Database
  • Configuring Always-On High Availability Groups on SQL Server
Footer Logo

@2014 - 2023 - Windows OS Hub. All about operating systems for sysadmins


Back To Top