Enable encrypted connections to the Database Engine

Question

Hi , we have SQL server 2019 on prem and looking to enable encrypted connections to the Database Engine.
( we have not enabled any force protocol encryption option in SQL native client configuration) and when we connect to DB through SSMS, option with Encrypt connection=yes , trusted server certificate=yes I can see the connection made to the sys.dm_exec_connections encrypt_option=true ,auth_scheme=TRUE ,net_transport=TCP.

Q1. I tried to connect using PS with Encrypt=true;TrustServerCertificate = true and its failed .but works with SSMS. not with script . not sure why. any idea please

$sqlConn = New-Object System.Data.SqlClient.SqlConnection
$sqlConn.ConnectionString = “Server=SQLServer2;TRUSTED_CONNECTION=TRUE;Encrypt=true;TrustServerCertificate = false;Initial Catalog=master”
$sqlConn.Open()
$sqlcmd = $sqlConn.CreateCommand()
$query = “SELECT @@SERVERNAME go select ORIGINAL_LOGIN()”
$sqlcmd.CommandText = $query
$adp = New-Object System.Data.SqlClient.SqlDataAdapter $sqlcmd
$data = New-Object System.Data.DataSet
$adp.Fill($data) | Out-Null
$data.Tables
would that mean by default

Q2: if we configured with certificate rather than default configuration
https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/enable-encrypted-connections-to-the-database-engine?view=sql-server-ver16

how do we handle all other users(business users, service users from other systems other than proper application connection ) direct DB connections to the DB with encryption? do we have to configure certificates in business users/ client laptops as well. ? or can we bypass this option for direct connections which I listed here

q4: How do we identify, the connection are made with default TLS or installed certificates

Thank you in advance

Answer 1

Hi @Ashwan ,

works with SSMS. not with script.

You mean you can set by SSMS but you can't set by script. Could you provide more information like error message or windows log

how do we handle all other users(business users, service users from other systems other than proper application connection ) direct DB connections to the DB with encryption? do we have to configure certificates in business users/ client laptops as well.

If you wanna others to connect your server you gona make sure you are in the same domain. And the steps can be seen by this link:
https://support.timextender.com/hc/en-us/articles/360042584612-Enable-Remote-Connections-to-SQL-Server-using-IP-address#:~:text=Configure%20SQL%20Server%20machine%201%201.%20Windows%20Firewall,5.%20Enable%20SQL%20Service%20to%20listen%20on%20TCP%2FIP

How do we identify, the connection are made with default TLS or installed certificates

I think you can check it in the SSCM:

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment"

Answer 2

Hi PandaPan

please find the following clarifications
"You mean you can set by SSMS but you can't set by script. Could you provide more information like error message or windows log"

when made the connection through SSMS with enabling following options , this works

when made the connection through PS with same enabling options , This is not working

"If you wanna others to connect your server you gona make sure you are in the same domain. And the steps can be seen by this link:"

Not that I referred . I meant to say , when we secured encrypted using purchased certificates (URL i already mentioned before) you would think do we have to configure on "MMC " client machines as well? or will that work by setting parameters(no certificates will configure in MMC ) ?(TRUSTED_CONNECTION=TRUE;Encrypt=true;TrustServerCertificate = true) ? by doing this, can we achieve the same result what we want(encrypt connections ?

thanks