ColdFusion 10 error attempting to query MSSQL database (SSL)

[norrisrg]

Hello,

Thanks for taking a look and for any ideas you might have.

Rob

ColdFusion app errors when attempting to query MSSQL 2008 R2 databse via SSL connection.

The datasource verifies fine.  

I have EncryptionMethod=SSL;validateServerCertificate=false for connection string.

Setting a trace on the SQL server doesn't show it trying to hit the database at all.

I have a copy of the same database on a SQL Server 2012 box (this one is server core if it matters).   I can edit the datasource to just point to this server and the app works fine.

Both SQL servers require SSL.

Here is the first few lines of a coldfusion log of database calls.

spy(Thread-66)(2014/07/15 16:07:53.353)>> Connection[1].getMetaData()

spy(Thread-66)(2014/07/15 16:07:53.353)>> java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Object has been closed. ErrorCode=0 SQLState=HY000

java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Object has been closed.

  at macromedia.jdbc.sqlserverbase.ddca.b(Unknown Source)

  at macromedia.jdbc.sqlserverbase.ddca.a(Unknown Source)

  at macromedia.jdbc.sqlserverbase.ddb9.b(Unknown Source)

  at macromedia.jdbc.sqlserverbase.ddb9.a(Unknown Source)

[stevenj1]

I am having the same problem connecting to the same type of sql server.  I thought it was because the connection string had to be more detailed.

[norrisrg · Accepted solution]

I figured my problem out today, hope it helps with yours.

See this link.

http://support.microsoft.com/kb/2653857

I installed an old JRE (6 update 27) and it fixed the problem.

We are catching the patches up on our server tonight.

Rob

[carl type3]

Hi Rob,

Interesting fix. I wonder if the more recent java 7 does not have SQL trusted ssl certificate information in it's keystore?
It would be interesting to use current java 7 with the keystore file jre\lib\security\cacerts from jre 6u27.

Just pondering, Carl.

[norrisrg]

Just to be clear, we'll go back to java 7 after we get our server patched according to the info in the article.

[norrisrg]

Server was patched overnight, I removed the old version of Java this morning.  Everything looks good.

Rob

[stevenj1]

As far as simply making ssl the connection, I found a nice and very helpful step through checklist at Re: SSL Encryption for Data Sources: which I'm describing here:

1. The SQL database admin sent me a certificate with a public key to which I had to use RegEdit to keep outlook from blocking it, and I uploaded it to the web server into the root C:\ drive;
2. I created a simple keystore on the web server's root C: drive: at the dos command prompt: C:\>keytool -keystore sqlstore.jks -genkey -alias sqlstore ....then answered a series of security questions;
3. Imported the uploaded public key into this keystore: C:\>keytool -importcert -file NameOfPublicKeyCertificate.crt/.cer -keystore sqlstore.jks -storepass Password -alias sqlserver
4. After verifying the ip address & port number on the remote server would receive the connection either via a studio express connection from my end or an odbc connection, I entered these parameters into the coldfusion admin datasource advanced settings:   EncryptionMethod=SSL; trustStore=C:/sqlstore.jks; trustStorePassword=Password; ValidateServerCertificate=false;

I had a very difficult time finding this information so I hope this can be helpful.