Make database connections in ArcGIS Insights enterprise version – Part 2
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.
In Part 1 of this Guide, we discussed five steps involved in making a successful database connection in ArcGIS Insights Enterprise version and covered the first two steps in detail.
- 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
Now let’s move on to the remaining three steps.
Step 3: Confirm the database server allows connections from the hosting server
In this step, I will use SQL Server as an example to discuss how to verify if your database server allows connections from Insights in ArcGIS Enterprise at all. The same principles can be applied to other relational databases.
First to clarify, Insights in ArcGIS Enterprise connects to a database via the portal’s hosting server, which is why we upload the database client files (i.e. JDBC drivers) to ArcGIS Server. Therefore, when we check if a database server allows connections from Insights, we are really checking if the database server allows connections from the hosting server.
Assume I want to connect to a SQL Server instance called dbserver.domain.local\SQLEXPRESS, where dbserver.domain.local is the server’s fully qualified domain name and SQLEXPRESS is the instance name. This instance is configured with a static port 1433.
(Note with SQL Server, you may not have a named instance but use the default instance, in which case, you only specify the server name for connection. Check with your database administrator if you are not sure.)
The first question is: “Can I resolve dbserver.domain.local from the hosting server machine?” There are many commands that can help answer this question, e.g. Ping, Telnet, Nslookup. You could search the Internet to learn about them if needed.
If I can reach the database server from my hosting server machine, next I need to ask: “Can I connect to this database server via port 1433?” I often run Telnet hostname port command for this task. If it fails, I will get a message like below; if it works, Command Prompt will temporarily turn into a blank window.
Fail connecting to the database server via port 1433
There are many causes that could fail the test above, which can be roughly categorized into either database server configuration or network configuration issues.
Network configuration issues may be, to list a few, the hosting server machine not able to resolve the database server name (while the server’s IP address may be resolvable), the firewall on database server only allowing incoming traffic through port 1433 from certain IP addresses, anti-virus blocking network traffic on either end, etc. These issues can be best handled by network administrators.
Database server configuration issues, although still network configuration issues, may fall onto database administrators’ shoulders, as they are specific to a database instance installed on the server machine. For example, if my telnet <server name> <port> connection test keeps failing, but I can resolve the database server name and the firewalls are off, I may need to check in SQL Server Configuration Manager if the TCP/IP protocol is enabled for my SQLEXPRESS instance or not.
Protocols for a SQL Server instance
If you’re the GIS Administrator, you may need to rely on your IT and/or DBA at this step. Before reaching out to them, one doable test is to install a database client on the hosting server machine (e.g. ODBC Driver 17 for SQL Server) along with a database management application (e.g. SQL Server Management Studio) and see if you could make a connection in the management application with the server name, instance name and database user credential provided by your IT or DBA. If not, you got some network and/or database server configuration issues to fix.
Step 4: Prepare the database user used in the connection
Even if you can connect to the targeting database using a database management application from the portal’s hosting server, you may still encounter an error such as “Something went wrong” when you attempt to create a database connection in Insights.
One test is to have your DBA create a database connection in Insights using their superuser credential (e.g. sa) and if it works, the issue is with your database user.
With SQL Server, first note Windows authentication is currently not supported in Insights as per Esri documentation. You need to use a SQL Server user instead of domain user for connections in Insights.
If that is not the issue, it's likely your connection user doesn't have proper privileges granted to it. There are two types of data operations in ArcGIS Insights:
- Connect to and browse contents of a relational database, without data caching.
- Connect to and browse contents of a relational database, with data caching.
With data caching, the connection user needs more privileges, such as creating tables in the database. In the Required database privileges documentation, you will find SQL codes to grant privileges to read-write users and read-only users, for all the supported relational databases.
Step 5: Fill out the properties correctly when create the database connection
Now we’re ready to create a database connection after choosing which database type to work with. Here is a filled-out form for connecting to a SQL Server database called monsterdb hosted on database server dbserver.domain.local instance SQLEXPRESS.
SQL Server Database Connection
For SQL Server database connections, Instance Name may or may not apply depending on if you work with a named instance or the default instance. Also, Port Number can be blank, especially when the instance is using dynamic ports.
When the database type switches, this form gets updated accordingly.
Database Connections to Oracle, PostgreSQL and SAP HANA
Mandatory connection properties, as marked with an asterisk, vary from one database type to another. The requirements of database connections in Insights should generally match how you make such connections outside of Insights, for example, in a database management application.
If you encountered an error after clicking adding a new connection, hopefully the error message would give you a clue. You may find the problem documented in Troubleshoot a database connection and/or recorded in the hosting server’s DEBUG log.
ArcGIS Server Debug log with Request ID (at 10.7 and later) generated by Insights
Sometimes, a closer examination of the filled in properties is all you need to resolve a connection issue (e.g. removing the optional port for a SQL Server database connection, using IP address instead of server name, switching uppercase letters to lowercase letters or the other way around) or you may need to revisit the steps mentioned in this guide.
Thank you for reading the Guide. If you’d like to watch a demonstration of the steps, please check out this video as well. Hopefully it’ll be smooth sailing next time when you make database connections in Insights.
Happy data analysis!