• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

ColdFusion 2018 and DSN - Timed out trying to establish a connection

New Here ,
Aug 16, 2021 Aug 16, 2021

Copy link to clipboard

Copied

I'm currently having issues with our ColdFusion 2018 Application Server in production. If we need to restart the ColdFusion 2018 service or the server reboots after patching, the SQL Server DSN fails to connect to the database with the message that it "Timed out trying to establish a connection."

 

The only way we have found to resolve this issue is to delete the DSN and recreate it.

 

The operating system is Windows Server 2016 and the Java JVM is Java SE 11.0.12. Our instance of ColdFusion is up-to-date with current patches. The database is SQL Server 2019. We are using a trusted connection to the database (i.e., the ColdFusion AD service account has a login entry in the database).

We are running ColdFusion 2016 in development and connecting to SQL Server 2019 and do not have these issues.

 

We have various other DSNs that connect to an Oracle 19c database and none of these DSNs experience connectivity issues after service restart or server reboot. It is only the SQL Server. We have also confirmed the TCP/IP protocols for SQL Server are enabled.

 

In development, I tried the MS SQL Server JDBC driver. It worked well with mixed mode, but I ran into configuration/permissions issues with the mssql-jdbc_auth-9.2.1.x64.dll to enable integrated security. Additionally, using this library is not supported by Adobe.

 

Any assistance anyone can offer is greatly appreciated.

TOPICS
Database access

Views

1.1K

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
community guidelines
New Here ,
Aug 16, 2021 Aug 16, 2021

Copy link to clipboard

Copied

In my post, I misspoke and said we could not duplicated this scenario in development.

 

We can actually duplicate this scenario in development.  My apologies for any confusion.  

 

I wanted to update the post, but there is no means for me to edit it.

Votes

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
community guidelines
Community Expert ,
Aug 18, 2021 Aug 18, 2021

Copy link to clipboard

Copied

Robert, there are 3 broad issues raised, and I have thoughts/answers for each.

 

1) First, let me say first that you can indeed edit your previous note here, as long as you are logged in. (If you cannot, try it from another browser.)

 

2) Second, I will note that I run CF2018 using trusted auth to SQL Server, and I do not have this problem. So I'm not inclined to think it's a general CF2018 problem. (Granted, I am not using the exact same SQL Server and Windows version, and that COULD matter, but I suspect it does not.)

 

And I realize you say "it works with CF2016", so that makes it all the more damning of CF2018. But you haven't confirmed: are you running CF2016 and 2016 from the same machine (dev or prod), talking to the same sql server? You can (easily and for free), and if you are not doing that, it could be very useful to prove the point of whether this is really a CF2018 problem or not. If it happens for you in both (from the same CF machine), then instead the question is what's different about that MACHINE.

 

And indeed, if it happens for you in both (CF2016 and 2018) on one machine (dev, for example),  then do the same in prod. All this would be valuable to understanding WHERE the problem really is.

 

I appreciate that you likely didn't want to consider doing those things, or perhaps you already have (just clarify that). It's just very useful to know if you've tried it, before we might chase lots of ghosts.

 

3) Finally, as for the mssql driver, that can be made to work also. Note first that Adobe does at least "support" you using "other" drivers (in that they offer the "other" option for setting up drivers). Whether they "support" any specific one is another question. But really, for most people, if it "works" that's good enough for them, whether Adobe formally supports it.  So your call.

 

But as for the problem you are having, there are a couple of possibilities. One is that you need to put the DLL in the right place--and that's not the normal place that DLLs are placed for CF (cfusion/lib or instancename/lib). Instead, it needs to go into the bin folder of the jre you are using. So that depends on what CF is set to point to as its java home in the CF admin jvm page. Put it in the bin of whatever that specifically points to (and restart CF for it to take effect).

 

Then second you may or may not know that you need to add integratedSecurity=true to the jdbc url/connection string.

 

FWIW, this is discussed in a Stack Overflow question on using the ms driver and this sort of trusted/integrated security (not with CF but in general), and you'll see I shared there the observation about WHERE to put the DLL while others had offered the suggestion of that connection string.

 

Let us know if any of the above helps.


/Charlie (troubleshooter, carehart.org)

Votes

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
community guidelines
New Here ,
Aug 18, 2021 Aug 18, 2021

Copy link to clipboard

Copied

Thank you for your response to my post.

 

Weirdly, I still do not see a link or option to edit my post.  I tried in Edge, Chrome, and IE.  

 

Regardless, as I mentioned in my reply to my initial post, I could duplicate this issue on both prod and dev so I have two different versions of ColdFusion (2016 and 2018) having this issue on Windows Server 2016.  In both instances, both versions of ColdFusion are current with patches and the requisite JVM.

 

I'm a little confused about your second point. Are you saying that I should attempt to install ColdFusion 2018 on the same IIS Server in development to see if I have the same issue in dev?

 

Regarding your 3rd point, I placed the *.dll in the bin folder of the JRE and I did add the integratedSecurity=true to the JDBC URL.  The interesting thing was that testing the connection in the ColdFusion administrator resulted in a status of OK, but accessing the pages in ColdFusion resulted in 500 errors.  The coldfusion-error.log contained the following error:    access denied ("java.lang.RuntimePermission" "loadLibrary.mssql-jdbc_auth-9.2.1.x64.dll").  

 

I had zero idea of where to add the grant for the dll so after some experimentation, adding the grant to catalina.policy seemed to bear positive results.  However, I haven't been able to find any documentation on whether this is actually the appropriate place to do so.  If I recall correctly, I seemed to have run into the same issues as the ColdFusion SQL Server driver when starting and stopping the application server and the DSN.

 

Soooo....yeah.

 

Thank you again for your response.  I appreciate the StackOverflow link.  It is definitely useful.  I'm going to continue plugging away at this.  If you or anyone else has further input, I definitely open to it.

Votes

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
community guidelines
Community Expert ,
Aug 18, 2021 Aug 18, 2021

Copy link to clipboard

Copied

I'd definitely install CF 2018 on the same server that's running CF 2016. It doesn't have to be connected to IIS to see what's going on with trusted connections to SQL Server. Also, make sure both CF instances are using the same user account to run.

 

Comparing SQL Server trusted connections to, well, almost anything else over JDBC is kind of problematic, because the client itself has to support Windows authentication. In this case, CF is the client of course. But on your CF 2018 servers, it's entirely possible that the user running the CF service simply can't make the connection, with CF or anything else.

 

All that said, though, the DSN timing out trying to establish a connection doesn't sound like an authentication problem. It sounds like ... something else. I'm not sure what! You could try testing this by going into the data source settings and disabling connection pooling for that data source.

 

Dave Watts, Eidolon LLC

Votes

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
community guidelines
New Here ,
Aug 18, 2021 Aug 18, 2021

Copy link to clipboard

Copied

Thanks for the response.

 

I think I'm going to give your connection pooling suggestion a shot first.  It's the lowest hanging fruit on the troubleshooting tree.  

 

I'm making the assumption that by disabling the connection pooling, you're forcing the client (i.e., ColdFusion) to create a new connection everytime it needs one.

 

To me it doesn't sound like an authentication issue either.  Once you add the DSN back into the CF data sources, you're good and there are no issues connecting and executing SQL.  It doesn't seem like a networking issue because it's reproducible in both dev and prod.  Keeping mind, also, that these are two totally separate networks.

 

I will try to find some time to do the CF 2018 install on development once I try the other option mentioned earlier.

Votes

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
community guidelines
Community Expert ,
Aug 19, 2021 Aug 19, 2021

Copy link to clipboard

Copied

You mention some libraries external to ColdFusion, hence the first 2 suggestions. The third suggestion follows from the error message you got, the fourth is an experiment.

 

  1. Remove any foreign/external JDBC libraries you may have imported into ColdFusion. (Requires a restart). Does that help?
  2. Ensure that the MS SQL datasource concerned is created using the driver that ships with ColdFusion. The driver should be (CLASS: macromedia.jdbc.MacromediaDriver, DRIVER: MSSQLServer). You can get the driver details by running
    <!--- getDatasources.cfm --->
    <!--- Login into Coldfusion Administrator. --->
    <cfset  createObject("component","cfide.adminapi.administrator").login("your_cfAdmin_password")>
    
    <!--- Instantiate the data source object. --->
    <cfset  datasourceObject = createObject("component","cfide.adminapi.datasource")>
    
    <!--- Get a structure containing all the data sources --->
    <cfset datasources = datasourceObject.getDatasources()>
    <cfdump var="#datasources#" label="All available data sources">​
  3. Ensure that the timeout values in the dump have reasonable values.
  4. (Assuming the above suggestions haven't helped, proceed next in your test environment only!) Delete the MS SQL datasource you - I would presume - created in the ColdFusion Administrator. Recreate it programmatically, using the following code:
    <cfscript>
        // Login is always required.
        adminObj = createObject("component","cfide.adminapi.administrator");
        adminObj.login("*******");  //your CF admin password.
        
        // Instantiate the data source object.
        myObj = createObject("component","cfide.adminapi.datasource");
        
        // Create a DSN.
        myObj.setOther(driver="macromedia.jdbc.MacromediaDriver",
           url="jdbc:macromedia:sqlserver://localhost:1433;databaseName=testSQLServerDatabase",      
         class="macromedia.jdbc.MacromediaDriver",
         name="myCustomMSSQLDSN",
         pooling = true,
         maxpooledstatements = 100,
         enableMaxConnections = "true"
         );
    </cfscript>
    done creating MS SQL datasource​

    Running this code as a CFM page will create an MS SQL datasource called "myCustomMSSQLDSN", having default settings. After running it, refresh the ColdFusion Administrator datasource page. Click on the button to edit the newly created datasource. Enter the MS SQL username and password, and press Submit.

    The question to be answered now is whether this new datasource also produces the error "Timed out trying to establish a connection".  
     

Votes

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
community guidelines
New Here ,
Aug 19, 2021 Aug 19, 2021

Copy link to clipboard

Copied

LATEST

Thank you so much for your input and the code!  I will definitely take a look at this in our development/production environments.

 

 

 

Votes

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
community guidelines
Resources
Documentation