none
MS SQL Server 2008 configured for secure (SSL) connection allows non-secure connection from JDBC Client

    Question

  • I need to connect to the MS SQL Server 2008 r2 using the secure communication. I have been able to do the same using the following connection string:

    • jdbc:sqlserver://<<db server name>>:1433;databaseName=<<db name>>;selectMethod=cursor;encrypt=true;trustServerCertificate=false;integratedSecurity=false;trustStore=<<path to my trust store>>;trustStorePassword=<<password>>

    On the DB server I have used Configuration Manager to specify the certificate to be used and enable 'Forced Encryption' SQL Server Configuration Manager -> SQL Server Network Configuration -> Protocols for <> - Right Click -> Properties -> Force Encryption and Certificate

    However, I am able to connect to the same DB without specifying 'encrypt=true' that is with the following URL:

    • jdbc:sqlserver://<<db server name>>:1433;databaseName=<<db name>>;selectMethod=cursor;

    My confusion is that when SQL Server has been configured for secure connection, shouldn't it reject/ignore the non-encypted connection. Or do I need to do additional configuration so the DB server accepts only secure connections

    If I run the following Query than I get 2 rows with the value of 'TRUE':

    • SELECT encrypt_option FROM sys.dm_exec_connections

    So, I feel that my SQL Server is configured correctly for encrypted connections

    Thanks and Regards

    P Manchanda

    Monday, December 02, 2013 5:34 AM

Answers

  • I have been trying to find documentation to confirm my belief, but I have not been able to. What I believe is that once you have set the setting "Force Encryption" on the server, SQL Server tells the client "you better encrypt your data, or am I not going to talk with you". And the setting you see in sys.dm_exec_connection seems to confirm that.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, December 02, 2013 10:43 PM
  • Hi P Manchanda,

    In addition,to enable encryption when a certificate has not been provisioned on the server, the Force Protocol Encryption and the Trust Server Certificate options can be set in SQL Server Configuration Manager. In this case, encryption will use a self-signed server certificate without validation if no verifiable certificate has been provisioned on the server. Application settings cannot reduce the level of security configured in SQL Server, but can optionally strengthen it.

    For more information about connection string syntax, you can review the following article.
    http://msdn.microsoft.com/en-us/library/ms254500(v=vs.110).aspx

    Thanks,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    Friday, December 06, 2013 5:57 AM

All replies

  • I have been trying to find documentation to confirm my belief, but I have not been able to. What I believe is that once you have set the setting "Force Encryption" on the server, SQL Server tells the client "you better encrypt your data, or am I not going to talk with you". And the setting you see in sys.dm_exec_connection seems to confirm that.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, December 02, 2013 10:43 PM
  • Hi P Manchanda,

    In addition,to enable encryption when a certificate has not been provisioned on the server, the Force Protocol Encryption and the Trust Server Certificate options can be set in SQL Server Configuration Manager. In this case, encryption will use a self-signed server certificate without validation if no verifiable certificate has been provisioned on the server. Application settings cannot reduce the level of security configured in SQL Server, but can optionally strengthen it.

    For more information about connection string syntax, you can review the following article.
    http://msdn.microsoft.com/en-us/library/ms254500(v=vs.110).aspx

    Thanks,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    Friday, December 06, 2013 5:57 AM