...
| Info |
|---|
| If you are unsure whether or not to encrypt the connection or trust the server, contact your DBA or IT department. |
The MSOLEDBSQL19 data provider has different default behavior for encryption and trusting the server certificate than prior versions of the MSOLEDBSQL provider.
...
If the expected certificate does not exist, the connection will fail and SQL Server will return an error regarding the certificate or certificate chain.
Per the Microsoft Documentation regarding encryption and certificate validation dated 12/12/2022, the outcome of the settings will be as follows:
| Encryption Option | Result |
|---|---|
| Do not encrypt | Encryption only occurs for LOGIN packets. Does not require a verifiable server certificate. |
| Encrypt and trust the server certificate | Encryption always occurs, but the server may use a self-signed server certificate. *Requires client machine to also trust the server certificate. |
| Encrypt but do not trust the server certificate | With a verifiable server certificate, the connection will succeed and encryption will occur. Without a verifiable server certificate, the connection will fail. |
...
*The ACM default selection is in bold.
| Warning |
|---|
The third option, Encrypt but do not trust the server certificate, requires a certificate issued by a trusted Certificate Authority. If the expected certificate does not exist, the connection will fail and SQL Server will return an error regarding the certificate or certificate chain. |
For more information: https://learn.microsoft.com/en-us/sql/connect/oledb/features/encryption-and-certificate-validation?view=sql-server-ver16#major-version-19
Testing the Encryption Options
If you wish to test the encryption options before changing the connection in ACM, you can do this in SQL Server Management Studio. On the "Connect to Server" form, click the "Options" button and open the "Connection Properties" tab. There are two options: "Encrypt" and "Trust the Server Certificate". Selecting neither will not encrypt the connection (ACM Option 1), selecting both will encrypt the connection and allow a self-signed certificate (ACM Option 2), and selecting only "Encrypt" will require a verifiable server certificate (ACM Option 3).