Encrypt Incoming Connection from ColdFusion

Question

Encrypt Incoming Connection from ColdFusion

amy26

SSCrazy Eights

Points: 8926
More actions
October 26, 2017 at 10:20 am

#329391

Hello,
My web developer is trying to encrypt her ColdFusion connection coming into my database server. We do not have "force encryption" turned on. So far, she has been unable to get the connection to work and cannot find any help to assist in why it won't work.
The error message says:
JDBC driver error: SSL handshake failed - unable to find valid certification path to requested target
Is there something in SQL Server I need to do to make this work? I don't want to force encrypted connections for everything cause it will break other stuff. Do I need to load a certificate into SQL somewhere?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

jasona.work SSC Guru Points: 50117 More actions · Answer 1

You do need to have a certificate loaded into the server OS on the SQL Server, and then that certificate needs to be available to SQL to use.

You need to make sure that your SQL Server service account is able to read the certificate, which is detailed here:
Using a self-signed SSL certificate with SQL Server
You don't need to have "force encryption" set, but this is the first step.

amy26 SSCrazy Eights Points: 8926 More actions · Answer 2

Ok we did all that. I got the certificates loaded in SQL and we exported it from the SQL Server machine and then imported it to the keystore in the CF server. Then, in CF when we try the data source, if I put the verify server certificate parameter to true the connection attempt fails and it says it can't find the path the certificate. If I set that value to false, the connection works. we have encrypt = true and encryptmethod = ssl ... so I'm not sure if its actually using SSL if we're telling it not to verify the certificate first?

jasona.work SSC Guru Points: 50117 More actions · Answer 3

Is it a self-signed certificate, or did it come from a trusted certificate authority? In my example above it's from my home AD CA, at work it's from a "real" CA.
If it's self-signed, there are, I believe, other steps that need to be taken, but I don't know what they are.

Here's the other part of the solution when I had to get it working with CF at work:
https://www.sqlservercentral.com/Forums/FindPost1806486.aspx

amy26 SSCrazy Eights Points: 8926 More actions · Answer 4

No, they are official certificates from our .mil people.

How do I verify if a connection is encrypted on sql server?

jasona.work SSC Guru Points: 50117 More actions · Answer 5

amy26 - Thursday, October 26, 2017 1:04 PM
No, they are official certificates from our .mil people.
How do I verify if a connection is encrypted on sql server?

Well, at least we work for the same employer...
🙂

As for checking if a connection is encrypted, the following query should help:
use [master]; go

select * from sys.dm_exec_connections;
Check the "encrypt_option" column. You can either use the "client_net_address" column to figure out if the connection is coming from your CF server, or join to sys.dm_exec_sessions to go by login names and host names.

You can also try connecting to your SQL Server in SSMS, but before you make the connection, hit the Options>> button on the connect dialog and check the box for "Encrypt connection" and see if that succeeds. If it does, you should see an encrypted connection with the query above, if it fails, then it could be a problem getting to the CA to verify the certificate.

amy26 SSCrazy Eights Points: 8926 More actions · Answer 6

Thank you!!! Yup, its encrypted.... awesomesauce!

jasona.work SSC Guru Points: 50117 More actions · Answer 7

amy26 - Thursday, October 26, 2017 1:18 PM
Thank you!!! Yup, its encrypted.... awesomesauce!

You're quite welcome!