SQL Server Connectivity Broken at 3.2.x

950
7
05-14-2019 04:19 PM
WilliamCraft
MVP Regular Contributor

Has anyone been able to get a SQL Server database connection to work in Insights for ArcGIS 3.2.x ?  I had no issues connecting to SQL Server databases in version 3.0 and 3.1 (I tried in 3.2.0 and 3.2.1, although 3.2.0 is the only supported version of Insights on my 10.6.1 platform), but after upgrading to version 3.2.x my SQL Server connections stopped working.  I also tried re-creating the connections altogether, and even re-installing from scratch after a clean uninstall and file system cleanup.  The error I am getting is "Something went wrong. Verify your connection properties, or click here to troubleshoot this" which is actually supposed to be a Hana-specific error within Insights according to this article despite the fact that I'm trying to create a SQL Server connection.  This seems like a bug and, if so, a pretty bad one.  It is almost as if 3.2.x was never tested against SQL Server.  Surely someone would have seen this issue before that version of the software was released.  I have even tried re-uploading the JDBC drivers and re-associating their file upload with the respective SQL Server platform under the "Data" section of the ArcGIS Server Admin application.  No luck.  Connections work fine for Oracle, though.  

Environment:

  • Portal for ArcGIS 10.6.1 (with 4+ patches)
  • ArcGIS Server 10.6.1 (with 15+ patches)
  • ArcGIS Data Store 10.6.1
  • Windows Server 2012 R2
  • SQL Server 2014 SP2 with 10.6.1 Enterprise Geodatabase

Side note regarding Oracle database connectivity: In Insights version 3.1, I was unable to connect to an Oracle 11g database (I think the system requirements indicate that only 12c is supported).  Strangely enough, in version 3.2.x, I am able to connect to an Oracle 11g database.  I'm not sure if this is intended or not.  

Tags (1)
0 Kudos
7 Replies
Asrujit_SenGupta
MVP Regular Contributor

You can check the supported Databases for Insight from this link: Supported databases—Insights reference | ArcGIS 

The error that you are encountering, I think, I had observed this with SQL Server too, so don't think that error is HANA-specific.

0 Kudos
WilliamCraft
MVP Regular Contributor

According to the Esri documentation, this error is specific to SAP Hana.  See below.

Observed Error in Web UI

Description from Esri Documentation

0 Kudos
Asrujit_SenGupta
MVP Regular Contributor

Yes, I just checked again and recalled that I had got -- Something went wrong with this database connection. Check to see if the connection properties need to be updated. So that was a different message.

So the error in your case is definitely odd then.

0 Kudos
EarlMedina1
Occasional Contributor III

If the documentation lists that then I believe it may need to be corrected. As Asujit noted, I have recently seen this error occur with other databases including MSSQL and PostgreSQL. 

William, in my case (MSSQL 2017) I was able to resolve my connection issue by creating a new user with the db_datareader role.

0 Kudos
WilliamCraft
MVP Regular Contributor

Thanks for replying, Earl.  I gave my DB user the db_datareader role but that did not change the behavior.  I also tried with db_owner role too, but no luck.  After speaking with some folks at Esri last week while at the PUG conference, it looks like they are going to get a case open to troubleshoot this issue and potentially update the documentation (for this version and for previous versions of Insights).  The feeling I'm getting, from seeing their reaction to the application behavior, is that we could be looking at at least one bug.  I noted a few other issues while talking with them that I did not mention as part of this thread.  For example, when updating the relational data store types within the ArcGIS Admin endpoint, Insights does not reflect the changes nor does the corresponding .DAT file get updated on the file system.  I'll update again when I have more information.  

0 Kudos
Adam-Glover
Esri Contributor

Hi William.
You may also see this warning if the permissions in SQL have not been set correctly.
I would advise that you create a new user (to facilitate with testing), grant that user access to the database, and then use the Insights help pages to identify and grant the necessary permissions for the user. I suspect the "Select" permission may not have been granted.

0 Kudos
NidhinKarthikeyan
Regular Contributor II

I encountered the same error. Issue is not specific to Hana. It's SQL Server DB i'm working on. Issue was related to the version of JDBC Driver. Installed the exact driver mentioned in DB Vendor files requirement.

0 Kudos