SQL Server doesn't send intermediate SSL certificates I am connecting to SQL Server (2008 R2) over open Internet and properly encrypting the connection is essential. I have COMODO certificate with two intermediate certificates between root CA and server certificate.

Now the problem is that SQL Server responds with truncated certificate chain where only the server certificate is included and intermediate certs are skipped. The client (JDBC in this case) refuses to validate server certificate due to incomplete certificate chain. I checked that the missing chain is the problem by running the program with -Djavax.net.debug=ssl,handshake.

Server (Windows Server 2008 R2) running the SQL Server has server certificate and its complete chain in its certificate database. SQL Server account (Network Service) was granted access to the private key. There's no option to grant access to intermediate certs, so I assume those are already available to all accounts on the computer.

Workaround #1: VPN

VPN is unfortunately out of question. It's very expensive to make VPN secure, highly available, and performant. VPN proved to be an obstacle to cloud service integration. So decision was made some time ago to kill it. The legacy VPN in the company is a performance and reliability nightmare.

Workaround #2: stunnel

I tried to sidestep the issue by wrapping SQL Server with stunnel, but SQL Server apparently uses some wrapped/modified SSL in order to support both encrypted and unencrypted connections on the same port. The JDBC client expects such non-standard SSL and fails to connect through stunnel. I could proxy the connection through stunnel client (essentially introducing point-to-point VPN), but this is unwieldy especially on developer machines.

Workaround #3: Azure SQL Database

I would really love to switch to Azure SQL Database, but other stuff in the legacy datacenter has grown used to the high LAN bandwidth to the SQL Server, so moving to Azure is a long-term prospect at best. I have to find a way to access SQL Server inside the datacenter securely.

Workaround #4: CA override in Java

So the last option I am considering is to embed the intermediate certificates in source code and override Java's default CA database (truststore), which can be done on per-connection basis. But then why did I purchase certificate from public CA? I could have just as well used self-signed certificate this way.



All Microsoft documentation tells me that SQL Server should be able to use certificate issued by public CA. So how do I make it do so?
To configure server side SSL for SQL Server, you will need to place the public CA certificate and private key in the certificate store of the service account running the database engine and place the certificate without the private key in the certificate store of the java VM initiating the connection. Then use SQL Server Configuration Manager to open the connection properties and set force encryption to yes and select the certificate to use, which would be the public CA certificate, for encrypting the connection. When an SSL connection is initiated, the SQL Server will present the certificate to the java VM and the java VM will find it in its store of trusted certificates and validate it directly against the CA without the need for walking the chain of trust for intermediate certificates. Since the server is on the Internet, it wouldn't hurt to configure ipsec so all traffic is encrypted. Also, It's good that you have a certificate from a public CA because if no certificate is configured, then SQL Server will used a self signed certificate and that configuration will leave you vulnerable to a man in the middle attack.