locked
Understanding SSL Encrypted Connections RRS feed

  • Question

  • Good morning,

    I'm currently struggling a bit understanding what's happening when testing out encrypting connections using SSL.

    Can anybody tell me why/what's happening.

    Using wireshark, you can “sniff” the network traffic.  So, I created a SQL Server and a client server, setup everything as the default (using port 1502 on the SQL Server).

    I then tried to connect to my database server using the “sa” password.  I performed a couple of queries and it was possible to find unencrypted packets containing important information, including the actual resultset from the queries.

    So, in order to test, I created a self-signed SSL certificate and applied it to the SQL Server Service, enabled "Enforce Encryption" and restarted the SQL Server Services.

    I then closed and opened SSMS on my client computer and connected back to my database server.  I did NOT tick "Encrypt Connection" under the connection options.

    When I performed the same tests I did initially, I was unable to retrieve the query or resultset from the WireShark trace.

    So, here's where I'm a bit confused.  If we can, lets ignore the fact that the SSL is a self created one for now...
    1) Is my connection ACTUALLY encrypted securely?  I have not imported my certificate into the client's certificate store, so why does my connection trust this certificate as there's no trust chain that I can see?

    2) I did not click "Encrypt Connection" so how come my connection was encrypted and didn't just error out saying "Sorry, force encryption is enabled, and this connection is not encrypted"?  It appears to have accepted that all connections NEED to be encrypted and automatically forced the encryption on the connection.

    Is what I've described how it is meant to behave?  I was expecting the certificate to be required by both server and client and also I was expecting to have to change my connection string to say "ENCRYPT=YES" or something and not simply automatically encrypt.

    Any advise or thoughts would be appreciated.  I actually documented "what I did" as I went along, but didn't want to spam the blog with a host of images, but if more information is required, I can provide this.

    Regards,
     

    Tuesday, March 24, 2015 10:43 AM

Answers

  • Hi AndyB1978,

    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. You can check that whether connections are encrypted between server and clients using the following DMV statement.

    USE master
    
    GO
    
    SELECT encrypt_option FROM sys.dm_exec_connections
    
    GO


    For more information about SSL encryption in SQL Server, please review the following article.
    Encrypting Connections to SQL Server

    For more details about client side setting and connection property options, please review the following blog.
    Selectively using secure connection to SQL Server

    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support



    Wednesday, March 25, 2015 3:07 AM
  • Using SQL Server Configuration Manager, right-click SQL Native Client Configuration, and then click Properties. What do you have for the setting Trust Server Certificate? Perhaps your client is configured to trust any certificate.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Wednesday, March 25, 2015 4:34 PM

All replies

  • Hi AndyB1978,

    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. You can check that whether connections are encrypted between server and clients using the following DMV statement.

    USE master
    
    GO
    
    SELECT encrypt_option FROM sys.dm_exec_connections
    
    GO


    For more information about SSL encryption in SQL Server, please review the following article.
    Encrypting Connections to SQL Server

    For more details about client side setting and connection property options, please review the following blog.
    Selectively using secure connection to SQL Server

    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support



    Wednesday, March 25, 2015 3:07 AM
  • Using SQL Server Configuration Manager, right-click SQL Native Client Configuration, and then click Properties. What do you have for the setting Trust Server Certificate? Perhaps your client is configured to trust any certificate.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Wednesday, March 25, 2015 4:34 PM
  • If the client doesn't request encryption, then it also doesn't check the certificate. 

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, March 25, 2015 6:34 PM