Highlighted

CF 11: Configuration a datasources to MS SQL Backend with encryption enforced by SQL Server

Community Beginner ,
Dec 10, 2014

Copy link to clipboard

Copied

Hello List;

I have the following problem: Configuration a datasources to MS SQL Backend with option encryption enforced (by SQL Server).

I goggled in the internet for the configuration of the datasource and find multiple articles/advices:

Configuration a datasource type other with jtds.jdbc.Driver and manual configuration of the connection string:

1. Downloading the driver and copy to the cfusion/lib directory: Done, the driver was recognized by coldfusion after a restart of the service.

2. Copying the ntlmauth.dll to the bin directory of jre/bin: Done

3. Configuration of the jdbc URL: jdbc:jtds:sqlserver://xxxx.xxx.xxx.net:1433/db: Works, I don’t  get a connection timeout

4. User and PW: Works: I don’T get a login failure.

5. Advanced settings: Connection String: Maybe here is something wrong: EncryptionMethod=SSL; TrustStore=Path\sqlstore.jks; TrustStorePassword=xxx; ValidateServerCertificate=true; HostNameInCertificate=xxx.xxx.xxx.net;


Error Message (Coldfusion logs/stack trace):  I/O Error: DB server closed connection. SQLException while attempting to connect: java.sql.SQLException: I/O Error: DB server closed connection..

Has anybody experiences with this topic/can give me advices/send me screenshots.


frank

Views

2.0K

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

CF 11: Configuration a datasources to MS SQL Backend with encryption enforced by SQL Server

Community Beginner ,
Dec 10, 2014

Copy link to clipboard

Copied

Hello List;

I have the following problem: Configuration a datasources to MS SQL Backend with option encryption enforced (by SQL Server).

I goggled in the internet for the configuration of the datasource and find multiple articles/advices:

Configuration a datasource type other with jtds.jdbc.Driver and manual configuration of the connection string:

1. Downloading the driver and copy to the cfusion/lib directory: Done, the driver was recognized by coldfusion after a restart of the service.

2. Copying the ntlmauth.dll to the bin directory of jre/bin: Done

3. Configuration of the jdbc URL: jdbc:jtds:sqlserver://xxxx.xxx.xxx.net:1433/db: Works, I don’t  get a connection timeout

4. User and PW: Works: I don’T get a login failure.

5. Advanced settings: Connection String: Maybe here is something wrong: EncryptionMethod=SSL; TrustStore=Path\sqlstore.jks; TrustStorePassword=xxx; ValidateServerCertificate=true; HostNameInCertificate=xxx.xxx.xxx.net;


Error Message (Coldfusion logs/stack trace):  I/O Error: DB server closed connection. SQLException while attempting to connect: java.sql.SQLException: I/O Error: DB server closed connection..

Has anybody experiences with this topic/can give me advices/send me screenshots.


frank

Views

2.0K

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
Dec 10, 2014 0
Participant ,
Dec 16, 2014

Copy link to clipboard

Copied

We just went through the same process.  All you need is to add is EncryptionMethod=SSL ValidateServerCertificate=false; to the connection string field in Advanced Settings.

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...
Dec 16, 2014 0
Community Beginner ,
Dec 16, 2014

Copy link to clipboard

Copied

Hi Stephen;

Thank's a lot.

We will try.

Would be wonderful,if the solution is so easy!!

frank

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...
Dec 16, 2014 0
Community Beginner ,
Dec 17, 2014

Copy link to clipboard

Copied

Hi Stephen;

we tried: add EncryptionMethod=SSL; ValidateServerCertificate=false; to the connection string;

And it doesn't work.. Are you sure, that you have enabled encryption enforced in your SQL-Server Settings.

Oherwise the connection works, but the Connection is not encrypted:

Coldfusion lies!

You can controll this by veryfining the open connections on the sqlserver with the query:

SELECT net_transport, protocol_type, encrypt_option ,auth_scheme, program_name FROM sys.dm_exec_connections AS c JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id cross apply sys.dm_exec_sql_text(most_recent_sql_handle) AS d

There you can see the jtds Connectionand the (programname) and the encryt_option (must be true).

frank

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...
Dec 17, 2014 0
Participant ,
Dec 17, 2014

Copy link to clipboard

Copied

When our DBA forced encryption, all ColdFusion datasources failed.  We added the string and all were working again. The DBA confirmed secure communication. Our environment SQL 2008R2 on Windows 2008R2, ColdFusion 10 and 11.

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...
Dec 17, 2014 0
Community Beginner ,
Dec 17, 2014

Copy link to clipboard

Copied

Hi Stephen;

You don't wrote, that you're not using the jdts Driver.

I tried your advise in the configuration of the existent datasources with Microsoft SQL Driver:

Adding the string EncryptionMethod=SSL; ValidateServerCertificate=false; in the advacend Settings.

And it seem's to work.!


Thank you very much.!!!

frank

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...
Dec 17, 2014 1
Community Beginner ,
Dec 19, 2014

Copy link to clipboard

Copied

Hi Stephen;

Have you possibly also a solution for coldfusion 8 MS SQL Datasources.

We have an old server with an application, that is still on coldfusion 8.

We don't migrate the application to Cold Fusion 11.

Because the server and the application will be closed down in the next 3 months.

But the application uses the same Microsoft SQL-Server (MS SQL 2012) as the coldfusion 11 applications.

So we cannot set the SQL Server to encryption enforced. Then the coldusion 8 application will not work.

The string EncryptionMethod=SSL; ValidateServerCertificate=false; does not work for the cold Fusion 8 MS SQL datasources.

I tested!

Now we have a problem with this one application using coldfusion 8 and the same SQL-Server.

frank

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...
Dec 19, 2014 0
Participant ,
Dec 19, 2014

Copy link to clipboard

Copied

We haven't had CF8 here for several years so I have no experience with that. Based on the CF8 documentation, that doesn't look like it was an option at the time. It looks like there might be a solution on Expert Exchange, but I am not a member. Setting up a ColdFusion 8 datasource with a SQL Server 2008 box using SSL

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...
Dec 19, 2014 0