Make database connections in ArcGIS Insights enterprise version – Part 1
When attempting to make database connections in ArcGIS Insights in ArcGIS Enterprise, often you may encounter errors such as “Invalid host”, “Data connection issue” or “Something went wrong”. It’s not always easy to pinpoint what exactly went wrong. In my experience, there is a better chance of making a successful database connection if one carefully follows the five steps in this guide.
Among the three flavours of ArcGIS Insights (i.e. Insights in ArcGIS Online, Insights in ArcGIS Enterprise and Insights Desktop), creating database connections are available in Insights in ArcGIS Enterprise and Insights Desktop. The desktop version out of box includes the necessary JDBC drivers to connect to the supported databases and needs no additional configuration.
On the other hand, it requires some work to configure Insights in ArcGIS Enterprise to connect to your databases and often you may encounter errors such as “Invalid host”, "Data connection issue" or “Something went wrong”. It’s not always easy to pinpoint what exactly went wrong. In my experience, there is a better chance of making a successful database connection if one carefully follows the steps below:
- Verify all requirements are met
- Register the relational data store type with ArcGIS Enterprise
- Confirm the database server allows connections from the hosting server
- Prepare the database user used in the connection
- Fill out the parameters correctly when create the database connection
This guide will mainly use SQL Server as an example to discuss each of the steps and hopefully provide some troubleshooting tips and tricks along the way. Considering its length, it’s split into two parts: Part 1 covers the first two steps and Part 2 the rest of the steps.
Before we start, I assume you have set up your ArcGIS Enterprise base deployment, installed the portal and server components of Insights, prepared an ArcGIS Enterprise named user who can launch Insights and when you click Connections, you can reach the webpage below. (If you’re not here yet, refer to the Required base ArcGIS Enterprise deployment and Required portal configuration settings sections in this documentation to ready your environment.)
Ready to make a new connection
Now let’s get started.
Step 1: Verify all requirements are met
Verify compatibility with ArcGIS Enterprise, supported browsers and other system requirements
Such verification is not specific to making database connections in Insights; nevertheless, it’s crucial. When you're working with unsupported/uncertified setup, you may encounter unexpected issues during any task. It’s not rare that after some extensive troubleshooting someone finally checks the versions and realizes “oh, Insights 3.3 is not compatible with ArcGIS Enterprise 10.7.x and later versions”.
Make sure you review the following documentations at least once:
Also be aware, as in the documentation, if the hosting server is configured with an ArcGIS Web Adaptor which has administrative access disabled, you must configure a CA-signed certificate with ArcGIS Server to support creating database connections.
Verify if your targeting database is supported
Insights allows you to connect to supported Oracle, Microsoft SQL Server, PostgreSQL and SAP HANA database versions.
Currently Insights does not support versioned data, and only those geodatabase user tables NOT created under the SDE user schema can be viewed and accessed from Insights.
For a full list of supported database types and their respective versions, check the Supported databases documentation. It’s also a good idea to check if your targeting data types are supported, and whether the data type (e.g. ST_GEOMETRY in Oracle) requires additional configuration.
Once you have confirmed all the system and database requirements are met, we could move on to the next step.
Step 2: Register the relational data store type with ArcGIS Enterprise
This step could be broken into the following:
- Verify and download the required database client file(s)
- Upload the file(s) to the portal’s hosting server
- Register the relational data store type using the uploaded file item
- Restart the hosting server’s service for the registration to take effect
When an application (e.g. ArcGIS Server, ArcGIS Desktop) connects to a database, it requires a database client. The same applies to Insights in ArcGIS Enterprise. The supporting database client files (JDBC drivers in this case) need to be downloaded from the database vendors and uploaded to the portal’s hosting server via ArcGIS Server Administrator Directory (e.g. https://server:6443/arcgis/admin), to complete registering a relational data store type (e.g. Oracle, SQL Server) with ArcGIS Enterprise.
This step is well documented in the Register relational data store types section of Esri documentation.
One thing you may want to know is the JDBC driver uploaded through ArcGIS Server Administrator Directory is only used by Insights in ArcGIS Enterprise. If you need to register databases with ArcGIS Server so your map services can reference data in your databases, you still need to install a 64-bit database client on the ArcGIS Server machine for those databases.
Once you have registered the relational data store type(s), next time when you click on Connections/New Connection/Database in Insights, you will find all the currently registered relational data store type(s) listed as below.
Registered relational data tore types (SAP HANA not registered)
Before picking one from the list and starting to fill out the connection properties, we have more work to do.
We need to confirm if the targeting database allows connections from the ArcGIS Enterprise’s hosting server, then prepare a database user to be used in the connection and also understand how to correctly fill out the parameters when creating a database connection in Insights. In Part 2 of the guide, we’ll cover all of them.
Take care until then.