question

swarchy avatar image
swarchy asked

SQL Native Client 11.0 config force protocol encryption

Hi I have a SQL network encryption question I was wondering if anyone could shed any light on

Within SQL Configuration manager for SQL 2012 and 2014

If I select to force protocol encryption to yes for SQL Native Client 11.0 config

I find that with \ without a restart (doesn’t matter) of the SQL service that the SQL Management error logs become inaccessible from within SSMS (both locally and from a remote machine)

Error details from SSMS

===================================

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

------------------------------

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476

Within the windows event viewer I see

The description for Event ID 17052 from source MSSQLSERVER cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.


If the event originated on another computer, the display information had to be saved with the event.


The following information was included with the event:


Severity: 16 Error:-2146893019, OS: -2146893019 [Microsoft][SQL Server Native Client 11.0]SSL Provider: The certificate chain was issued by an authority that is not trusted.


the message resource is present but the message is not found in the string/message table


This would lead me to think there is a lack of trust between the client and the 2012/2014 instance I have applied the forced protocol encryption on.

Problem is if I set force encryption under the SQL Network configuration, restart and check the logs I can see

The certificate [Cert Hash(sha1) "hash key was here but I have removed"] was successfully loaded for encryption.

Also if I connect in ssms on my laptop using the force encryption option with the trust server cert not selected this also works suggesting the trust with the certificate is ok. The connection also shows as encrypted in sys.dm_exec_connections

So I’m puzzled as to why this happens when you force SQL Native Client 11.0 protocol encryption option in SQL 2012/2014. This doesn’t seem to happen in SQL 2016 +

I have also checked if the native client driver has the QFE patch that allows for TLS1.2 connectivity, and have tried this on a SQL 2014 SP3 Developer edition Azure VM image where it was the same behaviour.

It does say on Microsoft site: https://docs.microsoft.com/en-us/sql/tools/configuration-manager/sql-native-client-11-0-configuration?view=sql-server-ver15

“The settings configured in SQL Server Native Client Configuration, are used on the computer running the client program. When configured on the computer running SQL Server, they affect only those client programs running on the server.

These settings do not affect clients connecting to previous versions of SQL Server, unless they are using the client tools starting with SQL Server, such as SQL Server Management Studio.”

Am I missing something here, does this mean that the Native client 11.0 protocol options only effect the connections being made internally on the server itself?

As in when I’m running SSMS on an external machine and try access the SQL logs, does SQL then rely on the native client to connect to the log file on the server’s local drive? In which case does it matter if I leave the Native Client Configuration force option as no as it will only encrypt traffic local to the server itself anyway and not traffic over the network like the SQL Network configuration does?

Any guidance would be appreciated

Thanks

encryptionsql2012sql2014client-toolsssl
1612126234142.png (34.3 KiB)
1612126257284.png (59.4 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.