none
Force encryption on SQL Server not working? RRS feed

  • Question

  • Hello Everyone,


    I'm running SQL Server 2008 64-bit. I've installed a self-signed cert on the box and set  "Force Encryption"  and restarted SQL server. 

    I setup a client machine to trust the authority of the cert installed on the server. When I connect to that SQL server from SSMS from a client machine and select the "encrypt connection" option in the client Connection properties, SSMS correctly complains that the cert on the server does not match the computer name I asked to log into . This is because, although the cert is trusted, the dns name dos not match the CN in the cert <- Perfect, exactly what I am expecting.

    When I connect to the same SQL server from the same client but  UNCHECK "encrypt connection" on the client, I'm able to login. Considering I've checked the "Force Encryption" on the server, the server should have rejected the connection. Why not?



    Ameer Deen
    Sunday, December 26, 2010 9:59 PM

Answers

  • Hi Ameer,

    Thanks for your post!

    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 it using the following DMV statement.

    One difference is that if you use self-signed certificate and the client is the one request encryption (with “Encrypt connection” option checked), then it will attempt to perform server validation on the certificate to verify the identity of the server machine so that it will be failed for a self-signed certificate since it hasn’t been signed by a trust root authority. For more information, please refer to http://blogs.msdn.com/b/dataaccess/archive/2005/08/05/448401.aspx.

    It is not recommended to enable "Force Encryption" option on both the client and the server. For more informaiton, please refer to KB article http://support.microsoft.com/kb/316898.

    -- To check whether connections are encrypted between server and clients
    USE master
    GO
    SELECT encrypt_option FROM sys.dm_exec_connections
    GO
    

    Hope this helps.

    Thanks,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Monday, December 27, 2010 10:36 AM
    Moderator
  • Hi Ameer,

    If the server is set to Force Encryption, it will guarantee communicataions will be encrypted (SSL) between the server and the client as you check that running the DMV statement in my last reply.

    The only difference if you set Encrypt connection in the client, the client will attempt to perform server validation on the certificate to verify the identity of the server machine which will be failed if the server uses auto-generated selft-signed certificate because CN does not match the server FQDN and the certificate is not trusted by the client. However, if the client sets Encryt connection to false, the client does not authenticate the server so that the login will be succeeded.

    Please let me know if you have more questions.

    Thanks,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Tuesday, December 28, 2010 1:47 AM
    Moderator

All replies

  • Hi Ameer,

    Thanks for your post!

    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 it using the following DMV statement.

    One difference is that if you use self-signed certificate and the client is the one request encryption (with “Encrypt connection” option checked), then it will attempt to perform server validation on the certificate to verify the identity of the server machine so that it will be failed for a self-signed certificate since it hasn’t been signed by a trust root authority. For more information, please refer to http://blogs.msdn.com/b/dataaccess/archive/2005/08/05/448401.aspx.

    It is not recommended to enable "Force Encryption" option on both the client and the server. For more informaiton, please refer to KB article http://support.microsoft.com/kb/316898.

    -- To check whether connections are encrypted between server and clients
    USE master
    GO
    SELECT encrypt_option FROM sys.dm_exec_connections
    GO
    

    Hope this helps.

    Thanks,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Monday, December 27, 2010 10:36 AM
    Moderator
  • Hi there Chunsong,

     

    Thanks for your response. My problem is server is not failing the login.

    Server is set to "Force Encryption"

    Client should fail the cert validation and disallow login. Login is working. Why?

     

    Ameer Deen.

     


    Ameer Deen
    Monday, December 27, 2010 12:49 PM
  • Hi Ameer,

    If the server is set to Force Encryption, it will guarantee communicataions will be encrypted (SSL) between the server and the client as you check that running the DMV statement in my last reply.

    The only difference if you set Encrypt connection in the client, the client will attempt to perform server validation on the certificate to verify the identity of the server machine which will be failed if the server uses auto-generated selft-signed certificate because CN does not match the server FQDN and the certificate is not trusted by the client. However, if the client sets Encryt connection to false, the client does not authenticate the server so that the login will be succeeded.

    Please let me know if you have more questions.

    Thanks,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Tuesday, December 28, 2010 1:47 AM
    Moderator
  • Hi Chunsong,

    so it is no possible for the server to enforce the client to verify the identity of the server (i. e. make sure the servername in the request is equal to the servername in the cerificate)?

    Regards

    Alexander

    Tuesday, October 18, 2011 2:14 PM
  • Hi all,

    We are implementing a Merge Synchronization solution which involves three SQL Servers located on three Azure locations worldwide and one on-premises location. We need to secure communications between all servers. We are evaluating the encryption of all server communications through SSL:

    http://technet.microsoft.com/en-us/library/ms191192.aspx

    When we configure one server (let’s call it server A) to accept only encrypted connections (with Force Encryption=Yes) we still can connect from other server (let’s call it server B) that do not have the certificate installed. We would expect the server B to fail in the attempt of connect as server A should only accept encrypted communications and those should need the certificated to encrypt/decrypt everything (commands and data).

    We have also review the following forum post that is very similar to this one:

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/bde679d9-ff83-4fa7-b402-42e336a97106/force-encryption-on-sql-server-not-working

    In all cases the Microsoft answer is:

    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 it using the following DMV statement

    When we run the provided DMV statement to check if encryption is enabled:

    -- To check whether connections are encrypted between server and clients

    SELECT encrypt_option FROM sys.dm_exec_connections

    We get “TRUE”. So theoretically encryption is enabled.

    Then:

    • Why can we run SQL statements against server A from server B (with SSMS) without any certificate?
    • Are we wrong when we expect server A to refuse any client that do not have the right certificate?
    • How can server B, without any certificate, decrypt the data encrypted by server A?

    Our intention is to encrypt all server in the same way so all of them will accept only encrypted communications. We are assuming that the Merge Agent will be able to communicate with the Publisher and the Subscriber through this encrypted environment. May anyone please confirm ti?

    Thanks for your help.

    Best Regards

    Benjamin Moles
    Wednesday, January 15, 2014 4:14 PM
  • SQL Server Generates Temp self generated Certificate and uses it for encrypting the connection. Even when no certificate is selected or assigned on the sql server. I verified using the wireshark with and without force encryption and the tcp data packets seems to be encrypted, when force encryption is Yes.

    http://technet.microsoft.com/en-us/library/ms189067(v=sql.105).aspx

    Friday, May 16, 2014 12:33 PM
  • if my i have two server SERVER A AND SERVER B with different FQDN (fully qualified domain name) and both are exchanging data between each other does i need to implement ssl on both.

    Do i need to purchase two different ssl certificate registered with different FQDN.



    HS

    Friday, July 25, 2014 10:35 AM
  • Hi all,

    My observation may help to answer this question : 

    There are two ways to encrypt the SSL connection.

    1. Configure server for all the incoming client request for SSL

    2. Selective client configuration.

    1. In this case if you installed certificate on the server and make "Force encryption to yes" then no matter which client is querying for connection, it will always create SSL connection.

    2. In this case you need to have same certificate installed on server and client(trust root certificate authorities store) machine.Then it will create SSL connection for that client otherwise it will fail to establish SSL Connection (provided you check the Encrypt connection in SSMS).If Encrypt connection is not checked then it will create connection but not secured(SSL).

     


    • Edited by Nihal_dev Monday, July 18, 2016 8:59 AM
    Monday, July 18, 2016 8:41 AM
  • Hello all,

    I realize that this is a somewhat old but this topic has yet to be fully answered to my understanding...

    1. In this case if you installed certificate on the server and make "Force encryption to yes" then no matter which client is querying for connection, it will always create SSL connection.

    If the server is implementing SSL encryption using a self signed certificate - One would think that without the Public Key or the root CA of the server on the client side - We will get an error (Just like when using the SSMS on the client side and checking the "Encrypt Connection".

    But lets assume that it acts as if the data is still encrypted (Same as in regular non trusted HTTPS website)

    Is there a way to validate that the traffic is still encrypted? For example with Wireshark?

    I've looked at the packets but since its not normal HTTPS traffic I did not find any Client/Server Hello packets...

    Furthermore - Is that a way to enforce validation of the certificate instead of just assuming that the client ignores the fact the certificate is not trusted but still uses the hash and encryption algorithms...

    The reason I ask is that I have already created a self-signed certificate for the testing purposes and selected it in the SQL configuration manager + selected Force encryption

    The query I ran in the SSMS (on the server side) seems to show that its encrypted..

    ************************************************************************

    SELECT c.connect_time, c.auth_scheme, s.original_login_name, DB_NAME(s.database_id) AS DBNAME FROM sys.dm_exec_connections c

    INNER JOIN sys.dm_exec_sessions s ON c.session_id = s.session_id WHERE c.encrypt_option = 'TRUE' -- Change it to TRUE to see all the encrypted connections

    ************************************************************************

    It shows the connection from the client side...

    And also this one:

    ***********************************************************************

    select * from sys.dm_exec_connections

    --select * from sys.key_encryptions

    ***********************************************************************

    This shows that the "encrypt_option" is TRUE

    So basically the main question is whether or not its possible to use Validation from the Server side to ENFORCE it on all clients and also to use some sort of packet sniffer I.E Wireshark to see that there is an actual encryption


    Tuesday, May 7, 2019 4:13 PM
  • I too am interested in understanding how we confirm that the connection is secure.

    We have installed a certificate on the server and enable the force encryption option.  It connects OK but how do we know the communication channel is encrypted?

    Our application is written in VB .Net so do we need to amend the connection string to ensure it always connects securely?  If anyone can share some code samples for this that'd be great (I'd like the application to check for a secure connection and warn the user if it is not).

    Thursday, May 9, 2019 5:01 AM
  • I used Microsoft Network Monitor  tool on Server to check what request are coming to it and monitor same.

    Basically using that if you see a TLS type connection in the logs from your client machine public IP, it proves your connection is using SSL.

    Thanks,

    HS


    HS


    Thursday, May 9, 2019 1:45 PM
  • Hi Alex

    I am trying to configure the same setup as is being discussed in this thread.

    I have set as discussed force encryption to yes and when I run

    select * from sys.dm_exec_connections

    It shows encrypt_option set to TRUE for all my connections.

    However in SQL Management Studio if I open a new query window to my server and select 'View'>'Properties Window'

    In  'connection details' 'Connection encryption' is shown as Not Encrypted 

    So I am confused is my connection encrypted or not?

    Monday, June 10, 2019 4:51 PM
  • I too have the same question that even after i enabled SSL through configuration manager with qualified certificate, without that certificate get installed with my laptop, i am able to connect that sql server from my laptop's SSMS.
    Friday, September 27, 2019 5:21 PM