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
