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?