Database connections in ArcGIS for Desktop
You can connect to databases from ArcGIS to view, query, and analyze the data they contain. 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 ArcGIS for Desktop.
When you add a database connection under the Database Connections node in the Catalog tree in ArcGIS for Desktop, a file is created on the client computer that contains the connection information to a database that you provide. The default location for the file is \\<computer_name>\Users\<user_name>\AppData\Roaming\ESRI\Desktop<release#>\ArcCatalog, but you can move the connection to another location. Just be sure users who need to make a connection have read access to the directory where you place the connection file.
Tip:If you use the data from this connection as the source for a service, such as a geoprocessing or geocoding service, you may need to place the connection file in a shared location on your network. See Preparing resources for publishing as services for more information about sharing a connection file.
When you connect to a database, you choose the type of database to which you will connect, then provide information to help ArcGIS locate the database and authentication information to allow you to log in to the database. The following is an example of a connection to a PostgreSQL database:
Tip:If you have connection files that were created prior to 10.1, they will still work. However, if you need to create a new connection to your geodatabase using an ArcSDE service, use the Create ArcSDE Connection File geoprocessing tool.
Use the Create ArcSDE Connection File geoprocessing tool if you need to connect to an enterprise geodatabase using an ArcSDE service.
Database platform and properties
The value you choose from the Database Platform drop-down list indicates the type of database to which you want to connect. The fields available on the dialog box and, therefore, the information you provide to make the connection depends on the database platform type you choose.
The following sections explain when to choose each database platform and what additional information is needed to connect:
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 Datasource 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.
Note:You cannot use a DSNless connection when enabling geodatabase functionality or upgrading a geodatabase.
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.
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.
Netezza
Choose Netezza from the Database Platform drop-down list to connect to a database in a Netezza data warehouse appliance.
When you connect to a Netezza database, you must provide the ODBC data source name for the Netezza database in the Datasource text box.
Note:When setting up the ODBC data source name on Windows, be sure to choose the option to Optimize for ASCII character set. When setting up the ODBC data source name on Linux, set the CharacterTranslationOption to latin9 and the UnicodeTranslationOption to utf16.
If you do not configure an ODBC data source name, you can use the following syntax to connect instead:
ServerName=<host>;Port=<port number>;Database=<database name>
Specify the information appropriate for your site. For example, if your Netezza data warehouse appliance is on server ndwa, communicating through port 5480, and the database name is spatial, the connection string would be as follows:
ServerName=ndwa;Port=5480;Database=spatial
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:
//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.
PostgreSQL
Choose PostgreSQL from the Database Platform drop-down list to connect to a PostgreSQL database, or connect directly to a geodatabase in a PostgreSQL database.
When you connect to PostgreSQL, you must specify an instance and database.
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.
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.
SQL Server
Choose SQL Server from the Database Platform drop-down list to connect to a database in Microsoft SQL Server or Windows Azure SQL Database, or connect directly to a geodatabase in a SQL Server database.
When you connect to SQL Server or SQL Database, you must specify an instance and database.
The instance is the name of the SQL Server or SQL Database instance. For example, if your 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.
The database is the name of the specific database on the SQL Server or SQL Database instance 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.
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 SQL Database.
Authentication Type
There are two login options for creating a connection to a database or enterprise geodatabase: database authentication and 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.
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.
Note:When you use operating system authentication in SQL Server, the operating system login is mapped to a database user; in Oracle, the operating system login is prefixed with an os_authent_prefix string (by default, OPS$) and stored in the USERNAME table. The maximum number of characters allowed for the user name when connecting to the geodatabase is 30. If your user name contains special characters or escape characters, such as a dollar sign or backslash, ArcGIS must place quotes around the user name. Those quotes count toward the 30 character total.