idea Databases > Create New Connection > We could connect to the database. in ArcGIS Insights Ideas

Databases > Create New Connection > We could connect to the database.

TimMinter — Thu, 20 Oct 2022 21:15:43 GMT

Just an idea. I'm evaluating ArcGIS Insights Desktop 2022.2.0.4957 to add to our barrel of BI products. I installed it on my workhorse PC, Windows 10 Enterprise 21H2. I tried to connect to my solid, fully functional 10.9.1.2.9 enterprise geodatabase in SQL Server 2019 Enterprise. ArcGIS Insights Desktop didn't even get past the first thing I would wanted to do. SQL Server Management Studio, Power BI Desktop, Tableau, ArcGIS Pro, ArcGIS Enterprise, ArcMap, QGIS, Excel, etc. can do it, no problem.

My idea is that ArcGIS Insights could do it, too. Fastest software evaluation I've ever done.

tim

Re: Databases > Create New Connection > We could connect to the database.

FrancesZhu1 — Fri, 21 Oct 2022 15:36:57 GMT

Hi Tim,

Sorry to hear that. In order to determine the root cause, we need more information about this issue.

1. The authentication method: according to your screenshot, I believe you are using Windows authentication to connect to your SQL Server database. If this is right, please check the next point.

2. There are required privileges for SQL Server database users that can make the connections from Insights to the SQL Server database. Please check the help doc at Required database privileges—ArcGIS Insights | Documentation.

3. The logs in insights_service.log can help us troubleshoot the issue. For Windows system, it is located at C:\Users\<username>\AppData\Roaming\Esri\ArcGIS Insights\logs. Repeat the issue and check the last few records there. Please send us the records. Note, before you start checking the log, make sure that set the log level to Debug from the Insights Desktop settings.

We will check the logs and determine the root cause.

Frances

Re: Databases > Create New Connection > We could connect to the database.

TimMinter — Fri, 21 Oct 2022 22:52:14 GMT

Hi Frances,

The account I'm using meets the ArcGIS Insights "Without data caching" permission requirements in the database, and I use that account to connect and use data without error or warning messages via all of the clients I initially mentioned. I've tried IP address and server name and CNAME (Alias) with fully qualified domain name in the Insights database connection dialog "Server name (Fully qualified domain name)" field.

The insights_service.log shows a driver getting excited about a "Tag mismatch!" here:

2022-10-21 15:25:13,719 [qtp111819772-28] ERROR c.e.i.s.IntegratedSecurityLogin - IntegratedSecurityLogin failed:
com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "Tag mismatch!". ClientConnectionId:<redacted guid-looking string>

Re: Databases > Create New Connection > We could connect to the database.

FrancesZhu101 — Tue, 25 Oct 2022 01:20:45 GMT

Hi Tim,

This is Frances using another account as my previous account was somehow blocked.

For the error 'Secure Sockets Layer (SSL) encryption. Error: "Tag mismatch!"', we believe it is the issue with SSL handshake. Our suggestion is that try a newer version of SQL Server driver.

The SQL Server driver delivered with Insights Desktop 2022.2 is mssql-jdbc-9.4.1.jre11.jar. Let's try the newer driver mssql-jdbc-10.2.0.jre11.jar (https://github.com/microsoft/mssql-jdbc/releases/download/v10.2.0/mssql-jdbc-10.2.0.jre11.jar).

The SQL Server driver is under C:\Program Files\ArcGIS\Insights\insights-service if you are using default path in installation. Remove the old SQL Server drvier mssql-jdbc-9.4.1.jre11.jar and copy the new driver mssql-jdbc-10.2.0.jre11.jar there. You need to close the Insights Desktop app before replacing the driver.

Start the Insights Desktop after the new driver is in place. Try to create the SQL Server database connection again. Let me know if this works.

Re: Databases > Create New Connection > We could connect to the database.

TimMinter — Tue, 25 Oct 2022 15:08:32 GMT

Hi Frances,

This try failed with the same error message.

2022-10-25 07:52:11,914 [qtp1463877470-31] ERROR c.e.i.s.IntegratedSecurityLogin - IntegratedSecurityLogin failed:
com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "Tag mismatch!". ClientConnectionId:<redacted guid-looking string>

I have logging all the way up and can send you the log file.

tim

Re: Databases > Create New Connection > We could connect to the database.

TimMinter — Wed, 26 Oct 2022 16:59:21 GMT

A few more data points...

Found that the most recent MSSQL JDBC driver is 11.2. tried, failed. https://learn.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver15
Noticed that I did not take a step to check or reset Classpath to the new driver. Looked around to see if I could do that. Probably not on my end in the role of "user." Maybe the Insights code goes sniffing and sets it automatically? idk. https://learn.microsoft.com/en-us/sql/connect/jdbc/using-the-jdbc-driver?view=sql-server-ver15
SQL Server 2019 Enterprise instance Is Clustered = False
SQL Server 2019 Enterprise security configuration:
- Force Encryption = Yes
- Certificate unexpired, issued by parent organization
- service account under which instance runs has read permission on certificate

Hmm. Similar?

"try removing this connection string property hostNameInCertificate=*.database.windows.net" from here https://github.com/microsoft/mssql-jdbc/issues/1926

gotta get back to work.

tim

Re: Databases > Create New Connection > We could connect to the database.

FrancesZhu101 — Wed, 26 Oct 2022 17:12:39 GMT

Hi Tim,

These are very good points. Thank you.

We are also trying to reproduce the problem on our end, but haven't made it yet. Give me more time. I will give you update asap.

Frances

Re: Databases > Create New Connection > We could connect to the database.

FrancesZhu101 — Fri, 28 Oct 2022 17:32:55 GMT

Hi Tim,

The issue is definitely related to "Force encryption" with a certificate. The error message is thrown from the SQL Server JDBC driver, which is not controlled by Insights. The workaround is turning off the enforce encryption from your SQL Server 2019 database.

One thing I want to clarify is that ArcGIS Pro is using different connection type to connect SQL Server database, which is ODBC. The SQL Server ODBC driver is used.

We are still investigating the issue and trying to create the same environment as yours. It takes time. So, please create a support ticket for this issue. I will update you through Esri support.

Frances

Re: Databases > Create New Connection > We could connect to the database.

FrancesZhu101 — Fri, 28 Oct 2022 19:19:25 GMT

Hi Tim,

I found the reason of why newer JDBC driver doesn't work. We forget updating the mssql-jdbc_auth DLL when we updated the jdbc driver. The version of JDBC driver and the jdbc_auth DLL must be the same version.

Please try the mssql-jdbc-10.2.0.jre11.jar and mssql-jdbc_auth-10.2.0.x64.dll (https://search.maven.org/artifact/com.microsoft.sqlserver/mssql-jdbc_auth/10.2.0.x64/dll) first.

Put mssql-jdbc-10.2.0.jre11.jar to "ArcGIS\Insights\insights-service", and mssql-jdbc_auth-10.2.0.x64.dll to "ArcGIS\Insights\resources". Make sure remove the old jar and old dll files.

If you want to use the latest driver v11.2.1, you need to get the same version of the dll file.

Let me know if the problem is solve.

Thanks,

Frances

Re: Databases > Create New Connection > We could connect to the database.

TimMinter — Fri, 28 Oct 2022 21:58:50 GMT

Hi Frances,

One of my teammates opened support case #03181219, which resulted in #BUG-000153360. From a quick scan, it looks like the bug fix recommendation and your info above about the accompanying DLL converged.

I've had to move along to other assignments and have uninstalled Insights. Hopefully, my idea will come to fruition the next time I download and install it.

Thank you, cheers, and have a proper weekend,

tim