locked
SSL Encryption for all network traffic RRS feed

  • Question

  • Hi,

    I am using SQL Server 2014 SP2 CU5 on Windows 2008 R2 SP1.

    Is there a way to force all connections to SQL Server to use encryption for ALL network traffic, not just the network packets associated with logging in?

    I have already setup a trusted SSL certificate on the server and I can verify that the certificate is being used by the server for encrypting network packets associated with logging in.

    Via the "SQL Server Configuration Manager" I have set "Force Encryption" = "Yes" on the "SQL Server Network Configuration" AND Force Encryption = Yes on the "SQL Native Client 11.0 Configuraton" and "SQL Native Client 11.0 Configuraton (32bit)".

    However, I can can still make a client connections that are not encrypted! Why? 

    It seems to me that the client is the only one responsible for determining if the entire connection will be encrypted or not. Can anyone clarify this?

    Thanks.

    Tuesday, June 13, 2017 2:12 AM

All replies

  • Hello,

    Sure you can connect unencrypted? Check it with sys.dm_exec_connections (Transact-SQL) => encrypt_option


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, June 13, 2017 5:39 AM
  • Hello,

    Sure you can connect unencrypted? Check it with sys.dm_exec_connections (Transact-SQL) => encrypt_option


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Hi,

    If I connect via SSMS and uncheck "Encrypt connection" under the "Connection Properties" tab and then query sys.dm_exec_connections it shows ENCRYPT_OPTION as TRUE, however in the right hand pane under "Current connection properties" it says the connection is "Not encrypted".  Also, I am able to connect using the hostname of the server, not the fully qualified hostname as specified in the SSL certificate that is installed on the server hosting SQL Server! 

    If I connect via SSMS and check "Encrypt connection" under the "Connection Properties" tab and then query sys.dm_exec_connections is shows ENCRYPT_OPTION as TRUE AND in the right hand pane under "Current connection properties" it says the connection is "Encrypted" and I also get the Padlock symbol in the task bar of a new Query window. Also I MUST specify the full name of the server as specified in the SSL certificate that is installed on the server hosting SQL Server - otherwise I get an SSL error.

    Hence, querying sys.dm_exec_connections is not a reliable indication at all! Plus it seems that encrypting the connection is only enforced via a client setting.

    Wednesday, June 14, 2017 2:54 AM
  • Can you connect without FQDN when you specify Trust Server Certificate false?

    I believe the Encrypted property SSMS displays only indicates encryption was explicitly specified as a connection parameter, not whether the transport is encrypted. All sessions are encrypted when force encryption is specified under SQL Server Network Configuration protocols. The native client specification or client encryption option is not relevant in that case.

    I would trust sys.dm_exec_connections (and a network trace if you doubt).


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Wednesday, June 14, 2017 3:22 AM
  • Can you connect without FQDN when you specify Trust Server Certificate false?

    I believe the Encrypted property SSMS displays only indicates encryption was explicitly specified as a connection parameter, not whether the transport is encrypted. All sessions are encrypted when force encryption is specified under SQL Server Network Configuration protocols. The native client specification or client encryption option is not relevant in that case.

    I would trust sys.dm_exec_connections (and a network trace if you doubt).


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    I have "Trust Server Certificate" set to "No" via the SQL Server Configuration Manager and that same option is unchecked as a connection option in SSMS yet I can still connect without the FQDN (but only when I do NOT select "Encrypt connection" in SSMS). 

    I was under the impression that i must use the FQDN in the connection string so it matches the name I used in the SSL certificate. Also note that I have no set up any aliases.


    Wednesday, June 14, 2017 3:39 AM
  • I was under the impression that i must use the FQDN in the connection string so it matches the name I used in the SSL certificate. Also note that I have no set up any aliases.

    The FQDN in the cert much match the SQL server host or virtual cluster name but I'm not aware of a requirement that the FQDN must be specified in the connection string in this scenario.  Specifying Force Encryption for the instance under SQL Server Network Configuration will encrypt all the SQL traffic regardless of client connection string.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Wednesday, June 14, 2017 3:59 AM
  • I was under the impression that i must use the FQDN in the connection string so it matches the name I used in the SSL certificate. Also note that I have no set up any aliases.

    The FQDN in the cert much match the SQL server host or virtual cluster name but I'm not aware of a requirement that the FQDN must be specified in the connection string in this scenario.  Specifying Force Encryption for the instance under SQL Server Network Configuration will encrypt all the SQL traffic regardless of client connection string.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Firstly, if I connect via SSMS and check "Encrypt connection" via the connection options then I MUST use the FQDN to connect to the server otherwise I get the error "A connection was successfully established with the server, but then an error occurred during the login process: (Provider: SSL Provider, error: 0 - The target principal name is incorrect.)... " Why does "Encrypt connection" force this behaviour? 

    Secondly, is there something I can use that doesn't support encryped connections so that the connection will fail - and thus prove SQL Server is enforcing only encrypted connections?

    Wednesday, June 21, 2017 10:01 PM