Database connections in ArcMap

You can connect to databases from ArcGIS to view, query, and analyze the data they contain. Some of the databases you access can contain geodatabase tables, functions, and procedures, but they don't have to; you can connect to any supported database and view the data from ArcMap.

Most types of databases require that you install and configure a database client on the machine that will connect to the database.

When you connect to a database, choose the type of database to which you will connect, provide information to help ArcGIS locate the database, and provide authentication information to allow you to log in to the database.

Note:

The information here does not apply to SQLite databases or GeoPackage files. See Connect to SQLiteor a GeoPackage from ArcGIS for instructions on connecting to these types of files.

Database connection files

You can create a database connection from the Database Connections node in the Catalog tree or run the Create Database Connection geoprocessing tool. Both methods create a file (.sde) on the client computer. When you create the connection in the Catalog tree, the file is created in \\<computer_name>\Users\<user_name>\AppData\Roaming\ESRI\Desktop<release#>\ArcCatalog, but you can move the connection file to another location. When you run the geoprocessing tool, you specify the location where the tool creates the file. Be sure that anyone or any application that needs to make a connection using this file has read access to the directory where you place the connection file.

If you use the data from this connection as the source for a service, such as a geoprocessing or geocoding service, you need to place the connection file in a shared location on your network that the ArcGIS Server site can access. See Make your data accessible to ArcGIS Server for more information about sharing a connection file.

Database platform and properties

The value you choose from the Database Platform drop-down list indicates the type of database to which you will connect. The fields available on the dialog box and, therefore, the information you provide to make the connection depend on the database platform type you choose.

The following sections explain when to choose each database platform, what additional information is needed to connect, and provides links to database-specific topics:

Dameng

Choose Dameng from the Database Platform drop-down list to connect to a Dameng database.

When you connect to Dameng, provide the name of the server where Dameng is installed in the Data source text box.

Use Database authentication, and provide a valid user name and password to connect. Operating system authentication is not supported.

See Connect to Dameng from ArcGIS for specific instructions on connecting from different ArcGIS clients.

Db2

Choose DB2 from the Database Platform drop-down list to connect to an IBM Db2 database on a Linux, UNIX, or Windows server, or connect directly to a geodatabase in a Db2 database on a Linux, UNIX, or Windows server.

When you connect to Db2, you must provide the name of the cataloged Db2 database in the Data source text box. Or, if the database is not cataloged, you can use the following syntax to connect instead:

HostName=<host>;Port=<port number>;Database=<database name>;

Specify the information appropriate for your site. For example, if your Db2 database is on server cube, communicating through DBMS port 50000, and the database name is spdata, the connection string would be as follows:

HostName=cube;Port=50000;Database=spdata;

This type of connection string is called a DSNless connection.

To complete the connection, choose the Authentication Type you'll use to log in to the database and provide credentials if needed.

See Connect to Db2 from ArcGIS for specific instructions on connecting from different ArcGIS clients.

Db2 for z/OS

Choose DB2 for z/OS from the Database Platform drop-down list to connect to a Db2 database subsystem on a z operating system (z/OS) or a geodatabase in a Db2 database subsystem on z/OS.

When you connect to a Db2 for z/OS database subsystem, you must provide the name of the cataloged Db2 database subsystem in the Datasource text box.

If you do not catalog the database subsystem, you can use the connection string as described for Db2 in the previous section.

To complete the connection, choose the Authentication Type you'll use to log in to the database and provide credentials if needed.

See Connect to Db2 from ArcGIS for specific instructions on connecting from different ArcGIS clients.

Informix

Choose Informix from the Database Platform drop-down list to connect to an IBM Informix database, or connect directly to a geodatabase in an Informix database.

When you connect to Informix, you must provide the Open Database Connectivity (ODBC) data source name for the Informix database in the Datasource text box.

If you do not configure an ODBC data source name, you can use the following syntax to connect instead:

HostName=<server where Informix is installed>;ServerName=<Informix server>;ServiceName=<service name defined in services file>;Database=<Informix database name>

Specify the information appropriate for your site. For example, if your Informix instance is on server hirise, the instance name is superids, it is communicating through port 54321, and the database name is gisdata, the connection string would be as follows:

HostName=hirise;ServerName=superids;ServiceName=54321;Database=gisdata

This type of connection string is called a DSNless connection.

Note:

Even though you do not have to configure an ODBC data source name to use a DSNless connection, you still must register the Informix server via SetNet32. Also note that you cannot use a DSNless connection to enable geodatabase functionality in an Informix database or upgrade a geodatabase.

To complete the connection, choose the Authentication Type you'll use to log in to the database and provide credentials if needed.

See Connect to Informix from ArcGIS for specific instructions on connecting from different ArcGIS clients.

Oracle

Choose Oracle from the Database Platform drop-down list to connect to an Oracle database, or connect directly to a geodatabase in an Oracle database.

When connecting to Oracle, either type the Oracle TNS name in the Instance text box, or provide one of the following Oracle Easy Connect strings:

• The name of the Oracle server/Oracle service name or ID

  For example, if Oracle is installed on myserver and myosvc is the Oracle service name, type the following:

  myserver/myosvc

• The name of the Oracle server:Oracle port number/Oracle service name or ID

  In this example, Oracle is installed on myserver, is listening on port 60000, and myoservice is the Oracle service name.

  myserver:60000/myoservice
• The URL of the Oracle server

  The URL for the same instance shown in the last example would be the following:

  //myserver:60000/myoservice
• The name of the Oracle server (You can use this if the Oracle listener on the server is configured to point to a default instance.)
• The IP address of the Oracle server/Oracle service name or ID

  For example, if the address of the server is 10:10:10:10, and the Oracle service name is orasvc, type 10:10:10:10/orasvc.

  For IPV6 addresses, place brackets around the address. For example: [4000:vu5:0:0:f666:h191:77f5:i2rs]/orasvc.

• The IP address of the Oracle server:Oracle port number/Oracle service name or ID

  In this example, the IP address is 10:20:30:40, port is 59999, and Oracle service is myomy1: 10:20:30:40:59999/myomy1.

  An IPV6 address for the same port and service would look like the following: [6543:eo4:0:1:f587:l249:12f9:w3ud]:59999/myomy110:20:30:40:59999/myomy1.

Be sure your Oracle instance is configured to allow Easy Connect. If you have the full Oracle client installed but want to use Easy Connect syntax to connect, be sure the sqlnet.ora file on the client is configured to allow the use of Easy Connect and the Oracle server is configured to allow Easy Connect syntax. Also note that if your Oracle instance is not listening on the default Oracle port number, you must use connection syntax that includes the port number.

To complete the connection, choose the Authentication Type you'll use to log in to the database and provide credentials if needed.

See Connect to Oracle from ArcGIS for specific instructions on connecting from different ArcGIS clients.

PostgreSQL

Choose PostgreSQL from the Database Platform drop-down list to connect to a PostgreSQL database, a Microsoft Azure Database for PostgreSQL, or a geodatabase in either one.

When you connect to PostgreSQL, you must specify an instance and database.

For PostgreSQL databases and geodatabases in PostgreSQL, the instance is the name or IP address of the server where PostgreSQL is installed. For example, if your PostgreSQL database cluster is on server ficus, type ficus in the Instance text box. If specifying an IPV6 address, enclose the address in brackets. For example, if the IPV6 address of the server is 1111:aa1:0:1:f111:a222:33f3:b4bb, type [1111:aa1:0:1:f111:a222:33f3:b4bb] in the Instance text box.

For Microsoft Azure Database for PostgreSQL or geodatabases in Microsoft Azure Database for PostgreSQL, specify the URL of the Azure instance, such as pgazure.postgres.database.azure.com.

If your PostgreSQL database cluster is listening on a port other than the default (5432), include the port number in the instance. For example, if PostgreSQL is installed on server mamabear and is listening on port 49200, type mamabear,49200 in the Instance text box.

The database is the name of the specific database on the PostgreSQL database cluster to which you want to connect. You can type the name of the database in the Database text box or choose it from the drop-down list. The database name is limited to 31 characters.

Use Database authentication, and provide a valid user name and password to connect. When connecting to Microsoft Azure Database for PostgreSQL, the user name must include the at sign (@) and instance. For example, for the user, editor1, in Azure instance pgazure.postgres.database.azure.com, type the user name as editor1@pgazure.

See Connect to PostgreSQL from ArcGIS for specific instructions on connecting from different ArcGIS clients.

SAP HANA

Choose SAP HANA from the Database Platform drop-down list to connect to an SAP HANA database.

Specify the ODBC data source name in the Data source text box.

Use Database authentication and provide a valid user name and password to connect. Operating system authentication is not supported.

See Connect to SAP HANA from ArcGIS for specific instructions on connecting from different ArcGIS clients.

SQL Server

Choose SQL Server from the Database Platform drop-down list to connect to Microsoft SQL Server or Microsoft Azure SQL Database.

When you connect to SQL Server or Azure SQL Database, you must specify an instance and database.

The instance is the name of the SQL Server or Azure SQL Database instance. For example, if your Azure SQL Database instance name is cloudy4u.database.windows.net, type cloudy4u.database.windows.net in the Instance text box. If you are using a SQL Server named instance of terra\gis, type terra\gis in the Instance text box.

If you are using a default SQL Server instance, you can specify the instance name or the IP address of the server in the Instance text box. If specifying an IPV6 address, enclose the address in brackets. For example, if the IPV6 address of the server is 2000:ab1:0:2:f333:c432:55f6:d7zz, type [2000:ab1:0:2:f333:c432:55f6:d7zz] in the Instance text box.

If your database is listening on a port other than the default (1433), include the port number in the instance. For example, if the SQL Server instance is basset\spatial and is listening on port 61000, type basset\spatial,61000 in the Instance text box.

Note:

You must provide additional information in the Instance text box to connect to a highly available SQL Server database.

The database is the name of the specific database on the SQL Server or Azure SQL Database instance to which you want to connect. You can type the name of the database in the Database text box or (if using SQL Server) choose the database name from the drop-down list. The database name is limited to 31 characters.

The database name is optional when connecting to a SQL Server instance, but only if you want to connect to the database that has been assigned as your default database in the SQL Server instance. The database name is required when connecting to Azure SQL Database.

To complete the connection to a SQL Server database, choose the Authentication Type you'll use to log in to the database and provide credentials if needed.

If you are connecting to Azure SQL Database, use Database authentication, and provide a valid user name and password to connect. Operating system authentication is not supported when connecting to Azure SQL Database.

See Connect to SQL Server from ArcGIS for specific instructions on connecting from different ArcGIS clients.

Teradata

Choose Teradata from the Database Platform drop-down list to connect to a Teradata database.

When you connect to Teradata, provide the ODBC data source name in the Data Source text box. If you have not configured an ODBC data source name, you can provide the IP address of the Teradata server in the format dbcName=<IP address>. For example, if the server has an IPV4 address of 200.30.200.10, type dbcName=200.30.200.10. If the server has an IPV6 address, enclose the address in brackets. For example, for a server with an IPV6 address of 3020:12z3:y4xw:5099:0:v678:u111:ts22, type dbcName=[3020:12z3:y4xw:5099:0:v678:u111:ts22].

Use Database authentication, and provide a valid user name and password. Operating system authentication is not supported.

See Connect to Teradata from ArcGIS for specific instructions on connecting from different ArcGIS clients.

Authentication type

There are two login options for creating a connection to a database or enterprise geodatabase: database authentication and operating system authentication. Note that not all databases support the use of operating system authentication.

Database authentication

If you choose Database authentication, you must provide a valid database user name and password in the User name and Password text boxes, respectively. User names can be a maximum of 30 characters.

Note:

Outside of ArcGIS, you can create user names in SQL Server that contain special characters. These user names must be delimited whenever they are used. ArcGIS will add the delimiter automatically when it is passed to SQL Server; you do not need to include delimiters with the user name. For example, if your user name is map.user, type map.user, not "map.user" in the User name text box. For more information on regular and delimited identifiers, see your SQL Server documentation.

As mentioned previously, you must use database authentication to connect to an Azure SQL Database, Dameng, PostgreSQL, SAP HANA, or Teradata database.

Uncheck Save user name and password if you prefer not to save your login information as part of the connection; doing this can help maintain the security of the database. However, if you do this, you will be prompted to provide a user name and password every time you connect. Also note that Save user name and password must be checked for connection files that provide ArcGIS services with access to the database or geodatabase, or if you want to use the Catalog search to locate data accessed through this connection file.

Operating system authentication

If you choose Operating system authentication, you do not need to type a user name and password—the connection is made using the login name and password used to log in to the operating system. If the login used for the operating system is not a valid database login, the connection fails. Some database management systems do not support operating system authentication.

Note:

When you use operating system authentication in SQL Server, the operating system login is mapped to a database user. When you use operating system authentication in Oracle, the operating system login is prefixed with an os_authent_prefix string (by default, OPS$) and stored in the Oracle USERNAME table. The maximum number of characters allowed for the user name when connecting to the database is 30. In these specific cases, ArcGIS will place quotation marks around your user name to pass it to the database. Those quotation marks count toward the 30-character total.