locked
Why SQL accepts both encrypted connection and non-encrypted connections when force encryption option is set to true? RRS feed

  • Question

  • I have installed a certificate and have set force encryption to yes under the flags' tab of protocols' window as well as SQLnative client
    configuration properties but SQL Server accepts both encrypted connection and non-encrypted connections,why?(i have checked the encrypt connection
    of the connection properties of connect to server window).
    also when i run profiler, it can capture both T-SQL statements which has run against encrypted and non-encrypted connection
    and i expect the profiler should not be able to get the encrypted connection's T-SQL statement,am I right or something is wrong with my  configurations?

    --yousef
    Wednesday, September 1, 2010 5:26 PM

All replies

  •  

    Please cross check if you followed the steps as advised on How to Enable Encrypted Connections to the Database Engine

    If the encrypted connection is configured SQL Server would throw a error on trying to connect by non-encrypted connection. 


    Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!
    Thursday, September 2, 2010 10:18 AM
  • Have you checked the dmv - sys.dm_exec_connections?

    Did you restart SQL Server after making the configuration changes?


    every day is a school day
    Thursday, September 2, 2010 10:41 AM
  • Hi Yousef,

     

    i expect the profiler should not be able to get the encrypted connection's T-SQL statement

    The data is only encrypted by SSL while transmitting between SQL Server and client. Once the SQL Server received the data and it will be decrypted.

    The statement captured by SQL Server Profiler is out of the scope of connection. Therefore we could see it whether the connection is encrypted or not.

     

    If anything is unclear, please let me know.


    Regards,
    Tom Li
    Thursday, September 2, 2010 10:56 AM
  • Thank you all for taking time to solve my problem.
    I have already done all the steps in that link,but
    I am able to use both
     
    cn.ConnectionString = "server=yousef;database=myDB;Trusted_connection=yes;encrypt=yes"
    

    and
     cn.ConnectionString = "server=yousef;database=myDB;Trusted_connection=yes;encrypt=no"
    
    

    to connect to SQL without receiving any error which says the latter connection has been refused  which i expect.

    when i run

     

    select session_id,net_transport,protocol_type,encrypt_option,auth_scheme
     from sys.dm_exec_connections
    
    
    I get the following result set for both connections that has the
    value of true under the encrypt_option column(session id 52).
    The strange thing is that all null sessions(i think they are internal processes) have value of false under encrypt_option column.
    (not relevant but,database mirroring is enabled on my sqlbox and this instance is the witness server)

     

    session_id net_transport protocol_type encrypt_option auth_scheme
    NULL TCP Database Mirroring FALSE (Unknown)
    NULL TCP Database Mirroring FALSE (Unknown)
    NULL TCP TSQL FALSE (Unknown)
    NULL TCP TSQL FALSE (Unknown)
    51 TCP TSQL TRUE NTLM
    52 TCP TSQL TRUE NTLM

    Here is my system settings:
    OS: XP sp3
    SQL Edition: Developer 2005 sp2
    sql server service is running under Localsystem account
    Flags tab of protocols for MSSQLSERVER properties:
      Force Encryption :yes
      Hide Instance    :NO
    Certificate tab of protocols for MSSQ
      certificate      : yousef.Local
      expiration date  :12/31/2035
      Friendly Name    : 
      Issued by        : yousef.Local
      Iissued To       : yousef.Local
    SQL native client configuration  properties:
    Force protocol Encryption :yes
    Trust server certificate :No

    I have restarted sqlserver service several times after provisioning!


    --yousef
    Friday, September 3, 2010 12:06 PM
  • Are the application and SQL Server on the same machine?
    Sunday, September 5, 2010 8:00 AM
  • Are the application and SQL Server on the same machine?

    Hi Vincent,

    Yes ,they reside on the same machine.


    --yousef
    Sunday, September 5, 2010 5:13 PM
  • Hi9 Yousef,

    Please test it in different machine.

    If they are in the same machine, you might not see expected action.

    Monday, September 6, 2010 2:40 AM
  • Hi Vincent,

    at the moment i have no possibility of checking it on another machine.what surprised me is that  the connection with  encrypt=no is also has value of  true under the encrypte_option column of sys.dm_exec_connections .why NULL session_id connection is not encrypted?

     

     



    --yousef
    Tuesday, September 7, 2010 5:15 PM
  • Hi Yousef,

     

    i expect the profiler should not be able to get the encrypted connection's T-SQL statement

    The data is only encrypted by SSL while transmitting between SQL Server and client. Once the SQL Server received the data and it will be decrypted.

    The statement captured by SQL Server Profiler is out of the scope of connection. Therefore we could see it whether the connection is encrypted or not.

     

    If anything is unclear, please let me know.


    Regards,
    Tom Li
    Hi Tom,
    your answer is quite reasonable,I have chosen it as a part of my questions but i thought if i choose it as the answer i wont get any response to the second part of my questions.Hope you did not take any offend.

    --yousef
    Tuesday, September 7, 2010 5:22 PM