tracker issue : CF-4200457

select a category, or use search below
(searches all categories and all time range)
Title:

ColdFusion not maintaining connection to Azure Data Warehouse

| View in Tracker

Status/Resolution/Reason: Closed/Withdrawn/NotABug

Reporter/Name(from Bugbase): Bob Keleher / Bob Keleher ()

Created: 01/03/2018

Components: Database

Versions: 2016

Failure Type: Usability Issue

Found In Build/Fixed In Build: 2016.0.05.303689 /

Priority/Frequency: Normal / All users will encounter

Locale/System: English / Win 2012 Server x64

Vote Count: 0

Problem Description:
Our ColdFusion 2016 Enterprise server (Windows Server 2012 R2) is not maintaining connections to a Microsoft Azure Data Warehouse. The first Azure query on a page takes a second or more to run. Subsequent Azure queries on the same page take a fraction of a second

example:
test1 (Datasource=azureDev, **Time=3485ms**, Records=1) in D:\DW\dwtest\CF2016\bob\azureAdhoc.cfm @ 12:10:12.012
select count(*) cnt from dimpatient where name like 'smith%' and birthdate >'2014-02-01' 

test2 (Datasource=AzureDev, **Time=125ms**, Records=3) in D:\DW\dwtest\CF2016\bob\azureAdhoc.cfm @ 12:10:12.012
select * from dbo.dimPatientMergeStatus 

test3 (Datasource=azureDev, **Time=281ms**, Records=1) in D:\DW\dwtest\CF2016\bob\azureAdhoc.cfm @ 12:10:13.013
select count(*) cnt from dimpatient where name like 'jones%' and birthdate >'2004-02-01' 

It is apparent that CF is taking extra time to actually make the connection each time while running the first query on the page. We've tried with various queries and re-arranging their order and always end up with the same result.

We are connecting to Azure Data Warehouse using the latest MS jdbc driver (mssql-jdbc-6.2.2.jre8.jar) and 'Maintain Connections' is checked. 

We first attempted to connect using the built in Microsoft SQL Server driver but kept getting this error:

Connection verification failed for data source: AzureDev2
java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Error
fetching requested database meta-data info.

We do not see this issue when we run the queries in SSMS.
We do not have this issue with any of our (many) other data sources.

Attachments:

Comments:

Hi Bob, You can create datasource using the built-in MSSQL driver, you just have to use the following connection string: EncryptionMethod=loginSSL;ValidateServerCertificate=true;hostNameInCertificate=*.database.windows.net; You can specify the connection string under the "Advanced Settings". I have attached the image to help you understand where these settings are. Then , rerun your queries . In my queries the connections are maintained. Let us know what results you see. Thanks, Suchika  
Comment by Suchika S.
29722 | September 19, 2018 07:48:58 AM GMT
Hi Suchika, We previously tried this connection string based on recommendations from Microsoft/our DBA: EncryptionMethod=SSL;Encrypt=yes;TrustServerCertificate=no; Tried your suggested connection string but still get the same error: Connection verification failed for data source: AzureDev2 java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Error fetching requested database meta-data info The root cause was that: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Error fetching requested database meta-data info I'm guessing our setup may be different. Thanks Bob
Comment by Bob K.
29724 | September 19, 2018 06:04:56 PM GMT
Hi Bob, Can you send the entire stacktrace? It would be helpful to identify your problem. Also, What is the version of your ColdFusion? Thanks, Suchika
Comment by Suchika S.
29727 | September 20, 2018 11:03:42 AM GMT
Bob, can you share the requested info?
Comment by HariKrishna K.
29760 | October 05, 2018 05:18:32 AM GMT
Closing it for now, in case you have other queries get back to us.
Comment by HariKrishna K.
29832 | October 24, 2018 06:16:08 AM GMT