ArcGIS Pro 3.3.1 - Issue with Azure SQL DB Connection

[Hua]

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

 

[RichardDaniels]

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

[RichardDaniels]

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?

[Hua · Accepted solution]

@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

[RichardDaniels]

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

[AyanPalit]

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