Unable to Connect to Azure SQL Database

[MichaelMirobelli]

We are unable to connect to an Azure SQL Database (12.0.2) through ArcGIS Pro (2.4 & 2.6.1), we are using the most recent Microsoft ODBC Driver 17.5.2. We are trying to publish/ share the content to our On Prem Enterprise Hosting server (10.8).

We are receiving the following error:

"Connection Error Failed to connect to the specified DBMS instance. A database connection exception has occurred. Connection was attempted with an older version of SQL Server client communications software that is not supported."

Derek Law‌

[MichaelMirobelli]

I tried from ArcGIS for Desktop (10.6.1) as well and received the following (slightly different) message there:

"Failed to connect to the specified server. Do you want to continue? Underlying DBMS error [ [Microsoft] [ODBC Driver 17 for SQL Server] [SQL Server]' CONTEXT_INFO' is not a recognized built-in function name. No extended error.]

[George_Thompson]

I just want to understand your workflow:

- You are connecting to your SQL Server DB in Azure from you local machine (office, house, etc.)

- Once connected you want to publish content to your ArcGIS Enterprise deployment that is located in the office (a guess on location)

Questions:

- Is the Azure DB a geodatabase or just a database?

- Have you attempted to connect from ArcCatalog (hate to ask, but curious)?

- You did install the 64-bit ODBC driver, correct?

- Are there other Azure databases that you can connect to successfully?

- Do you have a client in the same Azure location that can connect?

Additional information:

Connections from ArcGIS software to databases in the cloud must originate from machines in the same cloud. For best performance, connections should originate from machines in the same cloud region. In the majority of cases, connection and query performance is poor when you connect from ArcGIS clients installed on-premises to databases in the cloud or make connections between clients and databases in different cloud regions, and connections may time out.

Requirements for using ArcGIS 10.8.x and ArcGIS Pro 2.5 and 2.6 with databases in the cloud—System R... 

Geodatabase‌

[MichaelMirobelli]

Workflow:

Connect to SQL Azure database (not geodatabase) from either an on prem machine or Azure VM (received same error in both cases) and publish content to an On Prem server. Although we cannot get past the connection part at the moment.

Questions:

• The Azure SQL Data Warehouse (version 12) is just a database - geodatabase functionality is not enabled.
• Yes, I did attempt to connect from ArcCatalog and received the same error as from Desktop.
• Yes, the 64-bit ODBC driver is installed.
• Yes, I am able to connect to another SQL Server (12.0.2) that we have in Azure.
• Do you mean a client in the same Azure region? If so, no I am unable to connect to the database from an Azure VM in the same region.

[George_Thompson]

Ok, so this is an issue with a specific Azure DB as you can connect to another SQL Server in Azure.

What are the differences between them (open ports, accepting connections, security groups for access, etc.)?

As we also note in the docs you should be making the connection to the Azure DB from a VM in the same Azure region

ArcGIS connections to Microsoft Azure must originate from machines within Microsoft Azure and connections should originate from machines within the same Azure region.

Microsoft Azure database requirements for ArcGIS 10.8.x and ArcGIS Pro 2.5 and 2.6—ArcGIS Enterprise... 

Unfortunately I do not have an Azure instance to test. This may be best to have a technical support case opened to work with you on this.

Once you do get connected, I am not sure I would recommend publishing data to an on-prem deployment. Having the DB in the same location (physical or cloud region) is important for a good user experience.

[MichaelMirobelli]

George - Thank you for the advice and insight. I believe someone else working on the issue has opened up a technical support case, I'll verify that with them.

[MichaelMirobelli]

George Thompson‌ I have one point from my original post to clarify. It is not an Azure SQL Database as I indicated in my original post. The database that we are trying to connect to is an Azure SQL Data Warehouse.

[ShannonShields]

Michael,

unfortunately connections to SQL Server Data Warehouse, Azure SQL Data Warehouse, or Azure Synapse are not currently supported from ArcGIS clients. However, in building a road map for data sources we may support in the future we are interested in more information about your workflow, the type of data you are storing there, and how you want to interact with it.

Thanks,

Shannon

[PatriKKasic]

Hello Shannon,

@ShannonShields I was wondering if anything changed since post from 09-08-2020 on connection to Azure Synapse ? I am able to connect to Azure SQL database with ODBC but not to Azure SQL Server Data Warehouse(former SDW)?

I a apologies if this is not a proper workflow for tapping to existing thread but I am new to this forum.

Cheers,

Patrik 

[George_Thompson]

Michael Mirobelli‌ thanks for the clarification on the DB type in Azure. Shannon Shields‌ thanks for replying to this thread.