locked
How to verify Connections to SQL Server is Encrypted RRS feed

  • Question

  • I have a test scenario as below, I want to know if the connection from client PC to the SQL Server is considered as encrypted.

    1. I deployed certificate to certificate store on the server that is hosting SQL Server.

    2. On the same server, in SSCM, I was able to pick up the certificate that deployed in step 1. I also make ForceEncryption=Yes

    3. From a client PC, I was able to using SSMS to connect to the SQL Server (Note: I didn't check "Encrypt Connection" or "Trust Server Certificate" in SSMS)

    4. I run query from the client PC to verify the connection if it is encrypted. got the screenshot below.

    The odd is the query shows the connection is encrypted, but on the right-hand side, the properties of the connection tells the connection is not encrypted. Why?

    Thanks!



    • Edited by PhotoHiker Tuesday, January 22, 2019 11:20 PM
    Tuesday, January 22, 2019 11:18 PM

Answers


  • Since the connection is already encrypted after I set ForceEncryption=Yes in SSCM, why do I have to check "Encrypt Connection" in SSMS to make the "Connection Encryption" in Connection Properties to show Encrypted? The Connection is already encrypted, shouldn't the "Connection Encryption" reflect the same value as Encrypted?

    1. That's just the way you request a connection to be encrypted from SSMS. If it was another client, you'd have to export the certificate and install it on the client and configure the SQL Server Native client to enforce encryption. But in your case, you have already enforced it on the server so it's irrelevant if you further check that option in SSMS dialog.

    2. On the other hand, if you had not checked the "ForceEncryption" on the server, you'd have had the option of selectively choosing which connections to be encrypted via that same option in SSMS connection dialog. 

    3. You can test #2 above, just uncheck ForceEncryption in SSCM and then connect via SSMS by checking that option (Encrypt connection) in the connection dialog and run the same query and notice the "encyrpt_option" column. 


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    • Marked as answer by PhotoHiker Thursday, January 24, 2019 3:19 PM
    Wednesday, January 23, 2019 3:45 PM

All replies

  • Have you rebooted the SQL Server after enabling ForceEncryption in SSCM? Anyway, from your screenshot, it does appear that the communication between client and server is encrypted.

    The reason the properties window shows  "Not Encrypted" is probably because you didn't check the "Encrypt Connection" in the SSMS connection dialog (under the options tab). Try checking that option and see if it changes. anything. Since you are enforcing encryption on the server itself, all connections would be encrypted and SQL will reject any connection that doesn't support encryption. 


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Wednesday, January 23, 2019 12:10 AM
  • The Connection properties Shows "Not encrypted", because you haven't selected explicit the Option "Encrypt Connection" in SSMS login dialog => Advanced.

    But because of SQL Server Setting "ForceEncryption" the Server forces the client implicit to change to encryption


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, January 23, 2019 8:00 AM
  • Hi PhotoHiker,

     

    >>I want to know if the connection from client PC to the SQL Server is considered as encrypted.

     

    According to your screenshot, the connection has been encrypted.When you make ForceEncryption=Yes in SQL Server,it forces all data transfers between the client and the SQL server to be encrypted by the certificate of your choice.  

     

    >>The odd is the query shows the connection is encrypted, but on the right-hand side, the properties of the connection tells the connection is not encrypted. Why?

     

    As Mohsin_A_Khan said, you didn't check the "Encrypt Connection" in the SSMS connection dialog (under the options tab):

     

     

    When you check the encrypt connection option, Connection encryption is displayed as encrypted.

     

    Best regards,

    Dedmon Dai

     


    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

    Wednesday, January 23, 2019 9:08 AM
  • Thanks everybody for replying.

    I knew if I checked "Encrypt Connection" in SSMS, the "Connection Encryption" in Connection Properties will be Encrypted. What I want to know is

    Since the connection is already encrypted after I set ForceEncryption=Yes in SSCM, why do I have to check "Encrypt Connection" in SSMS to make the "Connection Encryption" in Connection Properties to show Encrypted? The Connection is already encrypted, shouldn't the "Connection Encryption" reflect the same value as Encrypted?

    What does it exactly mean "Connection Encryption"=Encrypted vs. the "encrypt_option"=TRUE in the query?

    Thanks again.

    Wednesday, January 23, 2019 3:30 PM

  • Since the connection is already encrypted after I set ForceEncryption=Yes in SSCM, why do I have to check "Encrypt Connection" in SSMS to make the "Connection Encryption" in Connection Properties to show Encrypted? The Connection is already encrypted, shouldn't the "Connection Encryption" reflect the same value as Encrypted?

    1. That's just the way you request a connection to be encrypted from SSMS. If it was another client, you'd have to export the certificate and install it on the client and configure the SQL Server Native client to enforce encryption. But in your case, you have already enforced it on the server so it's irrelevant if you further check that option in SSMS dialog.

    2. On the other hand, if you had not checked the "ForceEncryption" on the server, you'd have had the option of selectively choosing which connections to be encrypted via that same option in SSMS connection dialog. 

    3. You can test #2 above, just uncheck ForceEncryption in SSCM and then connect via SSMS by checking that option (Encrypt connection) in the connection dialog and run the same query and notice the "encyrpt_option" column. 


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    • Marked as answer by PhotoHiker Thursday, January 24, 2019 3:19 PM
    Wednesday, January 23, 2019 3:45 PM
  • The only thing that matters is the encrypt_option in the query.

    When you select "Encrypt Connection" the SQL Server driver attempts directly to connect using encryption.

    When you DO NOT select "Encrypt Connection", but the server has "Force encryption = on", then the connection is first attempted as not encrypted, the server returns a result like "connection must be encrypted" and the driver internally attempts to encrypt the connection.

    See the table of possible values:

    https://docs.microsoft.com/en-us/sql/relational-databases/native-client/features/using-encryption-without-validation?view=sql-server-2017

    Wednesday, January 23, 2019 3:53 PM