topic Re: ArcGIS Pro 3.3.1 - Issue with Azure SQL DB Connection in ArcGIS Pro Questions

ArcGIS Pro 3.3.1 - Issue with Azure SQL DB Connection

Hua — Thu, 20 Feb 2025 02:39:46 GMT

Hi Esri community,

We are about to move on-prem SQL DB to Azure SQL Server. I had an empty SQL database created on the Azure SQL server instance, which can be connected from my local Azure Data Studio/VS Code. With the exact same settings, however, I can't connect from a database connection in ArcGIS Pro, v3.3.1.

Here are the connection settings in VSCode, working all good.

Here are the DB connection settings in ArcGIS Pro, which failed to connect with an error "Invalid column name".

Extra notes:

1. Was suspecting the dash character in the instance and database name, but didn't find any evidence from official source of documentation.

2. Tried other settings as well e.g. Port=1433, or change Yes to yes/true/True etc.

3. What made me think is that VSCode can connect successfully, but ArcGIS Pro can't do it with the exact same parameters from the same environment.

Was there any syntax error in my config? Much appreciated if someone can shed some light on it.

Thanks,

Hua

Re: ArcGIS Pro 3.3.1 - Issue with Azure SQL DB Connection

RichardDaniels — Thu, 20 Feb 2025 16:37:23 GMT

Have you tried with Encrypt=no? Previous to ArcGIS Pro 3.2 and ODBC 17.09 the default for this was no or false. With the upgrade to ArcGIS Pro 3.3 and ODBC 17.10 and higher the default is now yes or true. Also if you have a connecting string stored in a config file or settings check other attributes since there names may have changed.

For example, old connection string to SQL:

Data Source=<server name>;Initial Catalog=<database name> Security Info=True;User ID=<user_id>;Password=<password>;TrustServerCertificate=true</Value>
</Setting>

New Format:

Server=<server_name>;Database=<db_name>;PersistSecurityInfo=true;UID=<user_id>;Password=<password>;TrustServerCertificate=true;Encrypt=false

Re: ArcGIS Pro 3.3.1 - Issue with Azure SQL DB Connection

RichardDaniels — Thu, 20 Feb 2025 16:43:15 GMT

Also, I've never seen Encrypt=Mandatory as shown in your top photo. This would imply that you have Trusted Certificates installed on all servers. Can you change that to yes or no?

Re: ArcGIS Pro 3.3.1 - Issue with Azure SQL DB Connection

Hua — Thu, 20 Feb 2025 20:45:45 GMT

@RichardDaniels - Thanks for your reply. After more research, it was the database name exceeding the max number of characters (31) that ArcGIS can support. https://pro.arcgis.com/en/pro-app/latest/help/data/databases/connect-sqlserver.htm

Re: ArcGIS Pro 3.3.1 - Issue with Azure SQL DB Connection

RichardDaniels — Thu, 20 Feb 2025 21:46:39 GMT

That is a new limitation for me :-). good find.

Re: ArcGIS Pro 3.3.1 - Issue with Azure SQL DB Connection

AyanPalit — Mon, 24 Feb 2025 16:35:38 GMT

Thanks for posting the solution. There is a pending enhancement request; feel free to review and kudo to upvote: SQL databases with names longer than 31 characters