locked
SQL Connection is failing if querying for SSL connection from client. RRS feed

  • Question

  • Hi,

    I have created a certificate on server and attached it to sql server and set force protocol No.

    Now i am trying to establish an SSL connection from client using connection string. [DBNETLIB][ConnectionOpen (SECDoClientHandshake()).]SSL Security error.

    My connection string is : "provider=SQLOLEDB;Use Encryption For Data=True;TrustServerCertificate=False;server=MyRemoteServer;Database=MyRemoteDB"

    TrustServerCertificate=False/True doesn't make any difference. Can somebody please tell me how to achieve this ??

    Note : 1. I haven't installed certificate on client machine.

       2. Use Encryption For Data=False is establishing TDS connection(not secured i believe)

    Requirement : I want only my client application to validate certificate and create SSL connection with sql server.(My client application is written in MFC and c#)

    Wednesday, July 6, 2016 9:18 AM

Answers

  • Hi Lin,

    Thanks for your reply.

    My server is configured properly because i can see encrypt option is true and also the query sp_readerrorlog 0 1 'cert'

    to check whether the certificate is installed properly.It seems server is configured properly.

    Meanwhile i just want to tell that the issue has been resolved.There was a problem on client side configuration.Instead of installing the certificate to trust root authorities folder i was installing it on personal folder.

    Thanks :)


    • Marked as answer by Nihal_dev Saturday, July 9, 2016 4:29 AM
    • Edited by Nihal_dev Saturday, July 9, 2016 4:30 AM
    Saturday, July 9, 2016 4:28 AM

All replies

  • Hi Nihal_dev,

    First of all, let’s make sure the server side is configured properly. To do that, use SQL Server Management Studio to connect to your instance and in “Connect to Database Engine” window, click on “options” button then insert the connection strings below in “Additional Connection Parameters” tab:

    Network Library=DBMSSOCN;TrustServerCertificate = yes;Encrypt =yes

    After connected to the instance, run the statements below:

    select session_id,net_transport,encrypt_option from sys.dm_exec_connections
    where net_transport = 'TCP'

    If encryption_option column shows ‘True’, that means the issue is not with server side. In that case, I would suggest you post a new thread in MFC or C# forum for better support.

    For more information about how to selectively using SSL encryption to SQL Server, please review this Blog.

    If you have any other questions, please let me know.

    Regards,
    Lin

    Friday, July 8, 2016 8:54 AM
  • Hi Lin,

    Thanks for your reply.

    My server is configured properly because i can see encrypt option is true and also the query sp_readerrorlog 0 1 'cert'

    to check whether the certificate is installed properly.It seems server is configured properly.

    Meanwhile i just want to tell that the issue has been resolved.There was a problem on client side configuration.Instead of installing the certificate to trust root authorities folder i was installing it on personal folder.

    Thanks :)


    • Marked as answer by Nihal_dev Saturday, July 9, 2016 4:29 AM
    • Edited by Nihal_dev Saturday, July 9, 2016 4:30 AM
    Saturday, July 9, 2016 4:28 AM