Participant

Question

Cold Fusion 11 MIcrosoft SQL Drivers and Azure SQL

Forum|Forum|10 years ago
July 20, 2015
4 replies
3237 views

I know that folks are using the JDBC connectors (with some gymnastics) to attach ColdFusion X to Azure SQL. I had previously done this in coldFusion 9 but found that under a load, it was less than desirable (allowing timeouts and conection refusals in the connection pool).

So in coldFusion 10 I came across being able to use the Microsoft SQL driver by simply adding this line to make it use SSL:

EncryptionMethod=SSL;

(in the connection string box)

I can't find the chatter on this, but it makes more sense to me to use the built in sql driver instead of JDBC with some JAR updating and restarting.. (feels 3rd party to me!)

All was well, for over a year. These work very nicely and perfectly. Impressive, even.

So I now do the EXACT SAME on ColdFusion 11 and I get:

Connection verification failed for data source: cadxApp
java.sql.SQLException: Timed out trying to establish connection
The root cause was that: java.sql.SQLException: Timed out trying to establish connection

Like nothing is answering. Using Telnet to go to the server via port 1433 reveals an answer, so I know its (the azure DB) answering on port 1433 as expected. I will install management studio and verify I can actually connect as well..

What would be different between the coldFusion 10 Microsoft SQL driver and the cold fusion 11 Microsoft SQL driver that would cause this? Do I need more nuggets in my connection string? I really want to avoid the JDBC hack I see here if I can:

jdbc - ColdFusion 11 and Azure Database - Stack Overflow

Or, is JDBC better for some reason???

I'd like to sell the powers that be on upgrading our CF servers, but this is currently a deal stopper!

I updated CF to the latest, greatest (5 I think), no change.

This topic has been closed for replies.

D

Dave Watts

Community Expert

I'm not sure this is going to be that helpful, but every SQL Server connection from CF uses JDBC. Even connections to other databases use JDBC at least on the CF side. SQL Server drivers, both Microsoft's own driver and the one from DataDirect that is shipped with CF, are "pure JDBC" drivers that use JDBC for the entire connection with no bridge. Adding extra connection string values isn't really a JDBC hack.

Anyway, I'd recommend you download whatever JDBC drivers Microsoft provides for Azure SQL, then install them in CF as "other". That will probably fix your connection problems and it should work just fine.

Dave Watts, Eidolon LLC

R

rjofficial86

Participant

Hi,

I am sorry for me adding this here a random request, I am being tasked work on a migration plan and due to the way the project structured - I would not able to ask client any questions but all I know is the site currently functions with CF 11 and want to migrate into Adobe Magento Platform (One which am familiar with)

I am attempting to find how Db structure of default CF would be so I will have some understanding of data migration can be done to commit to a timeline. Can I request from you to get list of default tables + schema of those tables or if ERD available with you that might be greatly helpful. Appreciate reading my request.

Charlie Arehart

Community Expert

I gather you're admitting to being new to cf. To be clear: there is no notion of a default db for cf or a cf app. A cf app can even be written without any db at all, though indeed most do use a db to persist their data. That said, a cf developer typically either creates a db as well as the app, or they may write an app based on some existing db.

You say you need to migrate the app to Magento without any interaction with the client--by which I assume you mean you have no access to the app, let alone the db. In my sincere and experienced opinion, you're going to be hard-pressed to do with this with any but the most simplistic web app.

All you can hope to do is replicate the ui, and it's calls to the server. Being blind to the app and db processing would make most such migrations impossible, or nearly so.

Maybe your situation will be different, or maybe someone else here will offer a very different answer.

That said, this question really has nothing at all to do with the cf version you're using (cf11), and so offering the question in this post is also inappropriate and may even preclude others seeing it. Again, I realize you may be new to cf so couldn't know this.

Some good news is that as an Adobe Community Professional (NOT an Adobe employee), I have the authority to move your question and its replies to a new thread. Let me know if you agree with that idea, and feel free to pick a new title/subject, as I can enter a new on for your question if I move it.

Or maybe this answer will suffice and there's no need to bother with that. Again, you can be the judge of that.

Hope all that's helpful.

/Charlie (troubleshooter, carehart. org)

C

carl type3

Legend

Hi,

I just reminded myself of something I have previously noted with CF11 datasources to a SQL instance.

Configuring datasource would need EG:

CF11

CF Data Source Name DSN

Database DATA

Server server\instance

Port

U&P

CF10

CF Data Source Name DSN

Database DATA

Server server\instance

Port 1433

U&P

With CF11 if port values are defined the following error occurs:

When the instance name is specified, it is invalid to specify the port number.

Is the SQL in azure like a traditional SQL instance? Perhaps remove the port number details on your CF11 azure datasource.

Regards, Carl.

len-andersonAuthor

Participant

Azure SQL is similar to regular Microsoft SQL, but different..

It tends to be more "Old School" and is not as robust or smart, but once you get it working it feels the same. Transact SQL is almost the same (with some Syntax differences)

I am sure if this was a regular Microsoft SQL Server database then I would not be having these problems! (and i wouldn't need the EncryptionMethod=SSL; in the connection string..

C

carl type3

Legend

Hi Len,

I think the built in CF driver for SQL datasource is macromedia_drivers.jar in \ColdFusion11\cfusion\lib.

As far as I can tell the CF10 (update 15) and CF11 (update 4) macromedia file is the same.

Perhaps you could check the connection syntax in neo-datasource.xml is the same for a connection to azure that works versa one that does not connect.

HTH, Carl.

len-andersonAuthor

Participant

Bear with me, I don't really get into the nuts and bolts often!

Is the file you are referring to the "Microsoft SQL Server" in the pull down menu choice, or the java driver? I had previously made the Sql Database Driver work and it seemed to work better than the Java driver, I consider it the adobe "Built in" sql driver and it seem like more of a "core" approach..

So are you saying to replace that file with the file in coldFusion 10, thereby making the drive the same, then verify in the XML file that the settings are the same?

Seems a little much for simply upgrading and filling the same screens out! (attached is a screenshot of what works on CF 10 and doesn't work on CF 11)

Does anyone from Adobe watch this forum?? (queue for magic slam-dunk answer, with up-sell!)

Removing the port (just wiping out 1433 in port) produced this error:

Connection verification failed for data source: cadxApp

java.sql.SQLException: Timed out trying to establish connection

The root cause was that: java.sql.SQLException: Timed out trying to establish connection

I could just save the $$ and NOT upgrade to CF 11 to solve the problem, but I have permission and I am tired of getting 5 versions behind! (Just went from 8 and MX to 11)

Does coldFusion 10 run fine on Windows 2012 R2 and the new IIS (8 I believe)??

??

C

carl type3

Legend

I think \ColdFusion11\cfusion\lib\macromedia_drivers.jar is the jdbc file used when you select "Microsoft SQL Server" in the pull down menu.

It would be a little radical to replace CF11 macromedia_drivers.jar file with one from CF10 but I suppose if you take a copy of first then you can always put file back.

On occasion I have noticed the datasource graphic environment not tell you everything about the datasource information so you can open neo-datasource.xml to compare like for like tho the password would possibly be encrypted differently.

Sometimes Adobe staff respond which can be very helpful.

Will CF Java need the azure SQL SSL certificate CER file added to CF keystore to make the SSL datasource connection?

Regards, Carl.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded