CODEWITHSUNDEEP
sql-serverdelphisslencryptiondelphi-2007
Aktas
Aktas

Reputation: 13

Connection to SQL Server using SSL encryption

We want to make all traffic to SQL Server encrypted. So we have SSL certificate and installed it to our SQL Server.

Our .NET web application can connect to SQL server using encrypt=true feature to have encrypted traffic between SQL Server and Web Server.
We have also a Delphi 2007 desktop application and we want to have encrypted traffic between SQL Server and application user as well.

Although application users can connect to SQL Server via SQL Management Studio with the "encrypt connection" feature checked, we cannot connect via our Delphi application. We add "encrypt=true" in the OLEDB connection string but nothing changed.

What should we do to get all traffic encrypted?

Upvotes: 0

Views: 6006

Answers (1)

Michael Keleher
Michael Keleher

Reputation: 216

SSL connection encryption can be initiated either from the client, which can be an IIS website or Desktop application, or can be enforced at the server side. Either way, a certificate needs to be installed and available to the user or process establishing the connection that the other system can validate. Client side encryption is configured using "encrypt=true" or selecting a checkbox option on SQL Server Management Studio. Server side encryption for SQL Server is configured by adding a certificate and private key to the certificate store of the service account running the database engine and using SQL Server Configuration Manager to specify the certificate to use for encryption and set the Force Encryption property to true. It is recommended to use a certificate generated by your domain certificate authority or one purchased from a public CA because a self signed certificate is vulnerable to a man in the middle attack.

Your main issue with yur Delphi 2007 application is that the server can't validate the certificate presented by the application and refuses to trust it or no certificate is available on the client to use. Certificate validation issues are not uncommon. When I configured my servers for server side encryption on all connections, I had to also add the same domain CA certificate to any virtual machine, like java VMs, that manage their own certificate validation. Interesting that when I read about trustServerCertificate=true, the documentation states "When the encrypt property is set to true and the trustServerCertificate property is set to true, the Microsoft JDBC Driver for SQL Server will not validate the SQL Server SSL certificate." I have not used this property, but it may work instead of adding the certificate to the VM. Also, if you have replication you will come across challenges if one server forces encryption and the other does not.

You can also find out more information about how certificates are validated by researching "walking the chain of trust" and digital signatures for certificates. That will help you understand why this is happening.

Upvotes: 1

Related Questions