5 Replies Latest reply on Jul 21, 2015 3:19 PM by carl type3

    Cold Fusion 11 MIcrosoft SQL Drivers and Azure SQL

    len-anderson
    len-anderson Jul 20, 2015 11:26 AM

      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.

      • 960 Views
      • Tags:
        • 1. Re: Cold Fusion 11 MIcrosoft SQL Drivers and Azure SQL
          carl type3 Level 4
          carl type3 Jul 20, 2015 3:41 PM (in response to len-anderson)

          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.

          • 2. Re: Cold Fusion 11 MIcrosoft SQL Drivers and Azure SQL
            carl type3 Level 4
            carl type3 Jul 20, 2015 7:45 PM (in response to len-anderson)

            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.

            • 3. Re: Cold Fusion 11 MIcrosoft SQL Drivers and Azure SQL
              len-anderson Level 1
              len-anderson Jul 20, 2015 9:01 PM (in response to carl type3)

              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)??


              cfScreen.jpg??

              • 4. Re: Cold Fusion 11 MIcrosoft SQL Drivers and Azure SQL
                len-anderson Level 1
                len-anderson Jul 20, 2015 9:04 PM (in response to carl type3)

                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..

                • 5. Re: Cold Fusion 11 MIcrosoft SQL Drivers and Azure SQL
                  carl type3 Level 4
                  carl type3 Jul 21, 2015 3:19 PM (in response to len-anderson)

                  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.