Highlighted

ColdFusion 10 error attempting to query MSSQL database (SSL)

Community Beginner ,
Jul 15, 2014

Copy link to clipboard

Copied

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)

Correct answer by norrisrg | Community Beginner

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

Views

1.3K

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

ColdFusion 10 error attempting to query MSSQL database (SSL)

Community Beginner ,
Jul 15, 2014

Copy link to clipboard

Copied

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)

Correct answer by norrisrg | Community Beginner

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

Views

1.3K

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Jul 15, 2014 0
New Here ,
Jul 17, 2014

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 17, 2014 0
Community Beginner ,
Jul 17, 2014

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 17, 2014 0
Guide ,
Jul 17, 2014

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 17, 2014 0
Community Beginner ,
Jul 17, 2014

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 17, 2014 0
Community Beginner ,
Jul 18, 2014

Copy link to clipboard

Copied

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

Rob

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 18, 2014 0
stevenj1 LATEST
New Here ,
Jul 18, 2014

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 18, 2014 0