0

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?

Improve this question

1 Answer 1

Reset to default
1

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.

Improve this answer
8
  • This is my workaround #4 except you are recommending WIndows certificate store over per-connection override on JVM level. Tinkering with Windows certificate store is a bit dangerous though. Developers won't like suspicious certificate installed globally on their computers. Not to mention it's laborious per-computer setup.
    – Robert Važan
    Jul 30, 2016 at 11:33
  • Actually, it depends on if there are already certificates in the java trusted certificate store. Since the certificate is from a public CA, it may already be trusted by your java VM. The cert I used was a root domain cert, so I needed the additional configuration. Managing certificates is part of regular administration for system administrators. All of the security methods will require a certificate to use SSL\TLS. The cert on the server goes into the users certificate store only for the service account and no one else.
    – Michael Keleher
    Jul 30, 2016 at 12:48
  • If you configure the server with the certificate and set force encryption, then test with SSMS and check your connection using select * from sys.dm_exec_connections to verify, then consider using trustservercertificate since it will be trusting the CA certificate, Using a self signed certificate with your SQL Server on the Internet is very risky.
    – Michael Keleher
    Jul 30, 2016 at 12:51
  • After some research, there is a good chance that all you need to do is configure the SQL Server since you are using a public CA certificate and I've read that the jave store contains some certs by default.
    – Michael Keleher
    Jul 30, 2016 at 13:36
  • "all you need to do is configure the SQL Server since you are using a public CA certificate" That's what I tried first, of course. SQL Server won't send the full certificate chain (like webservers do), only the last certificate in the chain, and Java won't download intermediate certificates on its own (as is usual for TLS clients).
    – Robert Važan
    Jul 30, 2016 at 22:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.