none
SQL 2017 accepts both ssl and non ssl client connections? RRS feed

  • Question

  • I have "Force Encryption" set to yes.  I thought this setting forces it so clients can only connect to the server with SSL and non ssl connections would not be allowed.

    In my case, on SQL 2017, both SSL and non SSL client connections will work.

    How do I set it so only SSL client connections are accepted?

    Wednesday, December 20, 2017 12:56 AM

Answers

  • My Ultimate goal is to force only SSL connections.  I appreciate all the responses and they are helping me troubleshoot, but the testing of the connection is not my ultimate goal.

    You have accomplished your goal if sys.dm_exec_connections shows only encrypted connections. Again, the SSMS client will not show the session as encrypted if encryption was because of the server side force encryption setting.



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


    Saturday, December 23, 2017 3:31 AM
    Moderator
  • Hi EulogioApelin,

    As I mentioned above, if the sys.dm_exec_connections shows only encrypted connections, the connection has been encrypted. When the Force Encryption option for the Database Engine is set to YES, all communications between client and server is encrypted no matter whether the “Encrypt connection” option (such as from SSMS) is checked or not.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, December 25, 2017 1:30 AM
    Moderator

All replies

  • Hi EulogioApelin,

    Forcing encryption by using “ForceEncryption” property under the Network Configuration in the server will force all clients to use encryption and any client that is not able to use an encrypted connection will fail.

    How do you judge that non-ssl Client can still work after set "Force Encryption" to yes? 

    When the Force Encryption option for the Database Engine is set to YES, all communications between client and server is encrypted no matter whether the “Encrypt connection” option (such as from SSMS) is checked or not.

    Please use the following code to check the status of connections:

    USE master
    GO
    SELECT session_id,connect_time,net_transport,encrypt_option FROM sys.dm_exec_connections
    GO

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, December 21, 2017 2:06 AM
    Moderator
  • The output of the SQL query you provided,  in the encrypt_option column is all "TRUE".  The fact that the column is called "encrypt_option" to me implies that the OPTION is set to TRUE.  This is not telling me if the actual connection is encrypted.

    Instead, what I see is in SSMS, i'll right click on the sql server i'm connected to on the left, select properties, then on the left, select "View connection properties", in the "Connection" section, Encrypted is reporting "No"

    

    This to me, tells me that the connection of SMSS to the server is NOT SSL.

    Friday, December 22, 2017 2:05 AM
  • This to me, tells me that the connection of SMSS to the server is NOT SSL.

    I think that's just saying that the client did not request encryption. To verify the session is encrypted, query sys.dm_exec_connections:

    SELECT encrypt_option FROM sys.dm_exec_connections

    WHERE session_id = 61;


    You could also run a network trace.


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

    Friday, December 22, 2017 2:34 AM
    Moderator
  • In the connection properties of SSMS, i have "Encrypt connection" unchecked

    

    On the sql 2017 server that the SSMS client is connecting it has this configuration setting.

    

    This is the first time, I'm installing a sql server.  My title should have been originally "How to force only encyprted (SSL) connections from SQL clients."  In my testing, the first picture to me, tells me I'm not connecting to the sql with SSL.  And the 2nd picture tells me that I've set the server to ONLY accept encrypted (SSL) connections from clients.  But i'm connecting with a client that has the option of encrypt connection off, and I connect successfully.

    My Ultimate goal is to force only SSL connections.  I appreciate all the responses and they are helping me troubleshoot, but the testing of the connection is not my ultimate goal.

    Friday, December 22, 2017 7:35 PM
  • My Ultimate goal is to force only SSL connections.  I appreciate all the responses and they are helping me troubleshoot, but the testing of the connection is not my ultimate goal.

    You have accomplished your goal if sys.dm_exec_connections shows only encrypted connections. Again, the SSMS client will not show the session as encrypted if encryption was because of the server side force encryption setting.



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


    Saturday, December 23, 2017 3:31 AM
    Moderator
  • Hi EulogioApelin,

    As I mentioned above, if the sys.dm_exec_connections shows only encrypted connections, the connection has been encrypted. When the Force Encryption option for the Database Engine is set to YES, all communications between client and server is encrypted no matter whether the “Encrypt connection” option (such as from SSMS) is checked or not.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, December 25, 2017 1:30 AM
    Moderator