Creating spatial database connections
Release 9.3 Last modified May 9, 2012	Print all topics in : "Building the Catalog"

About creating spatial database connections

Note: This topic was updated for 9.3.1.

With ArcCatalog, you can explore and manage geographic data stored in a relational database management system (RDBMS) through ArcSDE. Similarly, SDE for Coverages lets you access coverage, ArcInfo Librarian, and ArcStorm databases the same way you access data from an RDBMS. To access these databases, you must add a spatial database connection to the Catalog tree.

When you create a spatial database connection, a file is created on the client computer* that contains the connection information you provide through the Spatial Database Connection dialog box. Some of the information you provide through the spatial database connection dialog box is mandatory; other information is optional, depending on the requirements at your site. For instance, you can choose to save or not save any version and database user name and password information as part of 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 may need to place the connection file in a shared location on your network. See the topics Preparing resources to publish as services (in the ArcGIS Server help) and Accessing ArcSDE data in tools for more information about sharing a connection file.

Database information
You specify information pertinent to the database connection in the first three fields on the Spatial Database Connection Properties dialog box.

• In the Server field, type the name of the server on which the database you're connecting to resides. If you are making a direct connection, this information is not required but it is recommended you provide it anyway. If you do not provide the server name, the server name will not appear on the Source tab of the table of contents in ArcMap.
  
• In the Service field, you must type either the port number for the ArcSDE service, the name of the ArcSDE service, or the direct connection string specific to the type of DBMS to which you are connecting. (These connection strings are described in the sections on adding a direct connection in this topic.) It is usually simpler to specify the port number when connecting through an ArcSDE service; if you use the service name instead, there must be an entry in the services file on the client machine that contains the service name and its corresponding port number. If you have many clients connecting through an ArcSDE service, keeping these files up–to–date on every client machine would be more time consuming than simply specifying the port number for the service in the Spatial Database Connection Properties dialog box.
  
• Type the name of the database to which you are connecting. If you are connecting to an Oracle database, leave this field blank.
  

Login information
There are two login options for creating a connection to a spatial database: database authentication and operating system authentication.

• Database authentication
  
If you check Database authentication in the Spatial Database Connection dialog box, you must provide a database user name and password to connect to the geodatabase. User names can be a maximum of 30 characters.

Uncheck Save 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 have to provide a user name and password every time you connect.


• Operating system authentication
  
If you check Operating system authentication, you don't need to type a user name and password in the connection dialog box—the connection will be made with 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 geodatabase login, the connection will fail.

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 that 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.

Also note that if you are creating a connection to a geodatabase stored in Oracle, DB2, or Informix using operating system authentication, you have to use a direct connection to the database. For information on direct connections, see Properties of a direct connection to an ArcSDE geodatabase.

Connection details
In the connection details section of the Spatial Database Connection Properties dialog box, specify the geodatabase version to which you want to connect. The default connection is to a version named sde.DEFAULT. If you want to connect to a different version, click the Change button. This opens the Connection Details dialog box. From here, you choose either a transactional version or a historical version to which to connect.

NOTE: To read what versions are available, ArcCatalog must be able to make a connection to the geodatabase. Therefore, if the server or login information you provided is incorrect, you cannot change versions.

If you leave the Save the transactional version name with the connection file box checked, the user will always connect to the specified transactional version using this connection file. If unchecked, the user will be prompted to choose a version to connect to every time he or she reconnects using this connection file. Note that if you have chosen a historical version to connect to, this check box will be labeled Save the historical details with the connection file. If left checked, the user will always connect to the specified historical marker or date and time when using this connection file.

Situations for which you would want to save the version are if you are using SQL Server and created your system tables in the dbo schema, you want to create a specific connection file for a user schema geodatabase in Oracle, or you need to connect to the same transactional or historical version the majority of the time.

Learn more about versioning.
Learn more about historical versions.
Learn more about using user-schema geodatabases in Oracle.

If you often need to connect to different transactional or historical versions of the geodatabase, you should uncheck the option to save the version with the connection file. Doing so means you will be prompted to enter the missing connection properties every time the connection file is used.

Making sure it works
After you have specified all the information needed for the spatial database connection, it is recommended that you click Test Connection at the bottom of the Spatial Database Connection dialog box. If the connection test fails, contact the database administrator to ensure the database is operational and all the information you provided in the dialog box is correct. You can still add this connection to ArcCatalog by clicking OK, but you will be unable to retrieve data until the problem is resolved.

The following sections provide instructions on how to create a spatial database connection using an ArcSDE service, using direct connect, and connecting to a specific geodatabase version.

How to create spatial database connections

Connecting to a spatial database with an ArcSDE service

These instructions allow you to connect to an ArcSDE enterprise geodatabase using an ArcSDE service. Instructions for connecting directly to an ArcSDE geodatabase are covered in the "Adding a direct connection to..." sections.

1. Click the Database Connections folder in the Catalog tree.
   
2. Double-click Add Spatial Database Connection to open the Spatial Database Connection Properties dialog box.
   
3. Type the name or Internet Protocol (IP) address of the server to which you want to connect in the Server text box.
   
4. Type the name or port number of the service to which you want to connect in the Service text box.
   
If you want to connect to a geodatabase in a user's schema in an Oracle database, type the port number and schema in the Service text box separated by a:; for example, 5153:daisy.

5. If the data is stored in a SQL Server, IBM DB2, Informix, or PostgreSQL RDBMS, type the name of the database to which you want to connect in the Database text box. If the data is stored in Oracle, skip this step.
   
6. If using database authentication, type your user name and password for the database. If you don't want to save the user name and password with this connection file, uncheck Save username and password.
   
7. If using operating system authentication, click the Operating system authentication button. The database authentication information becomes unavailable.
   
8. If you are connecting to a geodatabase in a user's schema in an Oracle database or to a dbo-schema geodatabase in SQL Server, you must change the version to which you are connecting by clicking Change in the Connection details section and choosing the geodatabase from the list of transactional versions. For details on connecting to a specific version of a database, see Connecting to a specific version of the database.
   
9. If you do not want to save the version connection information you provided in the last step, uncheck Save the version with the connection file.
   
10. Click Test Connection.
    
If the test is successful, the button becomes unavailable. If the test fails, you are unable to retrieve data until you've provided the correct information or the database problem has been resolved.

11. Click OK.
    
12. Type a new name for the database connection.
    
13. Press Enter.
    

Tips

When you click OK in the Spatial Database Connection Properties dialog box, a connection file is created on disk and is added to the Catalog. However, ArcCatalog does not automatically establish a connection. Double-click the connection in ArcCatalog to access the database's contents. If you do not want to save the username/password and version properties with the connection file, uncheck the appropriate check boxes on the Spatial Database Connection dialog box. If there is an existing connection to an ArcSDE geodatabase in the ArcMap or ArcCatalog instance, any subsequent connections matching the original Server, Instance, and Authentication Mode properties will use the original connections properties. This means that if you connect to the same ArcSDE geodatabase with a connection file that does not save user name and password or version and there is already a connection to the same ArcSDE geodatabase server and instance that does save the user name and password or version, you will not be prompted to fill in the missing properties; the properties from the original connection with the saved login or version properties will be used. If you want to connect to an Oracle, Informix, or DB2 RDBMS with operating system authentication, you must use direct connect. See the appropriate direct connection section in this topic for information on how to make a direct connection to these databases. Data stored in a geodatabase in SQL Server Express can only be accessed via a direct connection. The section Adding a direct connection to a geodatabase in SQL Server below also applies to setting up a spatial database connection to a geodatabase in SQL Server Express. Nonadministrative users of ArcSDE Personal or Workgroup geodatabases stored in SQL Server Express can connect to these geodatabases using a spatial database connection as described in this topic. Administrators of geodatabases stored in SQL Server Express should access these geodatabases by making a connection to a database server, then connecting to the geodatabase. Connecting through the database server node of ArcCatalog is the only way you can administer both the database server and the geodatabases stored there. Learn more about database servers. Learn how to add a database server.

Adding a direct connection to a geodatabase in Oracle

The first set of steps details how to add a direct connection to a geodatabase in Oracle if you are using database authentication. The second set of steps instructs you how to add a direct connection to a geodatabase in Oracle if you are using operating system authentication. The last set of steps instructs you on how to create a connection to a geodatabase in a user's schema.

Making a direct connection using database authentication

1. Double-click the Database Connections folder in the Catalog tree.
   
2. Double-click Add Spatial Database Connection.
   
3. In the Server text box, type the name of the server on which the Oracle database resides.
   
4. If you are connecting to Oracle using an Oracle9i client, in the Service text box, type: "sde:oracle9i".
   
If you are connecting to Oracle using an Oracle 10g client, type: "sde:oracle10g".
If you are connecting to Oracle using an Oracle 11g client, type: "sde:oracle11g".

5. Type your user name in the User Name text box.
   
6. Type your password, followed by @<oracle network service name>, in the Password text box.
   
7. Uncheck Save username and password if you don't want your login information saved with the connection.
   
8. Click OK.
   
9. Type a new name for the spatial database connection.
   
10. Press Enter.
    

Making a direct connection using operating system authentication

1. Double-click the Database Connections folder in the Catalog tree.
   
2. Double-click Add Spatial Database Connection.
   
3. In the Server text box, type the name of the server on which the Oracle database resides.
   
4. If you're connecting to Oracle using an Oracle9i client, type "sde:oracle9i:/;LOCAL=<SQL_Net_alias>" in the Service text box.
   
If you're connecting to Oracle using an Oracle 10g client, type "sde:oracle10g:/;LOCAL=<SQL_Net_alias>".
If you are connecting to Oracle using an Oracle 11g client, type "sde:oracle11g:/;LOCAL=<SQL_Net_alias>".
The SQL Net alias was set when your computer was configured to use a direct connection. Contact your system administrator if you do not know what value to substitute here.

5. Click the Operating system authentication button. The database authentication information becomes unavailable.
   
6. Click OK.
   
7. Type a new name for the spatial database connection.
   
8. Press Enter.
   

Making a direct connection to a user-schema geodatabase

1. Double-click the Database Connections folder in the Catalog tree.
   
2. Double-click Add Spatial Database Connection.
   
3. In the Server text box, type the name of the server on which the Oracle database resides.
   
4. If you're connecting to Oracle using an Oracle9i client, type "sde:oracle9i:/:<schema_name>" in the Service text box.
   
If you're connecting to Oracle using an Oracle 10g client, type "sde:oracle10g:/:<schema_name>".
If you are connecting to Oracle using an Oracle 11g client,type "sde:oracle11g:/:<schema_name>".

5. Type your user name in the User Name text box.
   
6. Type your password, followed by @<oracle network service name>, in the Password text box.
   
7. Uncheck Save username and password if you don't want your login information saved with the connection.
   
8. Spatial database connections are made to the sde.DEFAULT version by default. You must change to a user-schema version. Click Change in the Connection details section.
   
9. When the Connection Details dialog box opens, choose the version of the user-schema geodatabase to which you want to connect. This could be <schema>.DEFAULT or any other version of that geodatabase. For example, if the schema the geodatabase is in is tubor, and you want to connect to the qa77 version, choose tubor.qa77 from the transactional version list.
   
10. Click OK on the Connection Details dialog box to close it.
    
11. Click OK.
    
12. Type a new name for the spatial database connection.
    
13. Press Enter.
    

Tips

You must create an Oracle network service name on your client machine before you can create a direct connection to an Oracle database. You can create a direct connection to a geodatabase in Oracle using database authentication or operating system authentication. However, if you use operating system authentication, you must use a direct connection and not an ArcSDE service. Learn more about direct connections. If you always connect to the same Oracle database using direct connect and operating system authentication, you can set the LOCAL variable in the tnsnames.ora file. Consult your Oracle documentation for details on setting this variable in the tnsnames.ora file. If you want to make a direct connection from an ArcGIS 9.3 client to a 9.2, 9.1, or 9.0 release geodatabase, you can specify the geodatabase release number in the connection string to decrease connection times. For example, to connect to an ArcSDE 9.0 geodatabase in Oracle9i, type "sde90:oracle9i" in the Service text box. You must have the proper direct connect drivers installed with ArcGIS Desktop to make this sort of connection.

Adding a direct connection to a geodatabase in SQL Server

You can use a direct connection for an ArcSDE geodatabase stored in SQL Server. When reading the steps, note that there are slight differences in how you make that direct connection based on the type of ArcSDE geodatabase.

1. Double-click the Database Connections folder in the Catalog tree.
   
2. Double-click Add Spatial Database Connection.
   
3. In the Server text box, type the name of the server on which the SQL Server database resides.
   
4. To connect to an ArcSDE geodatabase on a default instance of SQL Server, type the following in the Service text box: "sde:sqlserver:<name or IP Address of the server>".
   

To connect to an ArcSDE geodatabase on a named instance of SQL Server, type the following in the Service text box: "sde:sqlserver:<name or IP Address of the server>\<instance_name>".

If connecting to an ArcSDE geodatabase on SQL Server Express, type the following in the Service text box: "sde:sqlserver:<server_name>\<instance_name>".


NOTE: You could also create a spatial database connection to an ArcSDE geodatabase on SQL Server Express by right-clicking the geodatabase under the Database Servers node and clicking Save Connection.



5. Type the name of the database to which you want to connect in the Database text box.
   
6. If using database authentication, type your user name and password.
   

NOTE: You cannot use database authentication for a connection to an ArcSDE geodatabase on a database server (an instance of SQL Server Express).



7. Uncheck Save username and password if you do not want your login information saved with the connection.
   
8. If using operating system authentication, click the Operating system authentication button. The database authentication information becomes unavailable.
   
9. If you are connecting to an ArcSDE geodatabase with a dbo schema, you must change the transactional version to dbo.DEFAULT rather than sde.DEFAULT.
   
For instructions on connecting to a different version of the database, see Connecting to a specific version of the database.

10. Click OK.
    
11. Type a new name for the spatial database connection.
    
12. Press Enter.
    

The following is an example of connecting to an ArcSDE geodatabase that resides on a database server.

Tips

If you want to make a direct connection from an ArcGIS 9.3 client to a 9.2, 9.1, or 9.0 release geodatabase, you can specify the geodatabase release number in the connection string to decrease connection times. For example, to connect to an ArcSDE 9.1 geodatabase, provide the following information in the Service text box: sde91:sqlserver:<SQL_Server_instance_name> You must have the proper direct connect drivers installed with ArcGIS Desktop to make this sort of connection. When creating a spatial database connection for a geodatabase on an ArcSDE database server, you can use the Save Connection command on the geodatabase context menu accessed from the Database Servers node in the Catalog tree. See Saving a geodatabase connection for details.

Adding a direct connection to a geodatabase in DB2 or Informix

1. Double-click the Database Connections folder in the Catalog tree.
   
2. Double-click Add Spatial Database Connection.
   
3. In the Server text box, type the name of the server on which the DB2 or Informix database resides.
   
4. If you are connecting to DB2, type "sde:db2" in the Service text box. If you are connecting to DB2 on an IBM z/OS, type "sde:db2zos" in the Service text box.
   

If you are connecting to Informix, type "sde:informix".

5. For the database name in DB2, type the alias of the database to which you want to connect. (The alias was specified when you set up the DB2 Configuration Assistant.)
   

For database name in Informix, type the Open Database Connectivity (ODBC) data source name.

6. If using database authentication, type your user name and password.
   
7. Uncheck Save username and password if you don't want your login information saved with the connection.
   
8. If using operating system authentication, click the Operating system authentication button. The database authentication information becomes unavailable.
   
9. Click OK.
   
10. Type a new name for the spatial database connection.
    
11. Press Enter.
    

Tips

Learn more about direct connections. If you want to make a direct connection from an ArcGIS 9.3 client to a 9.2, 9.1, or 9.0 release geodatabase, you can specify the geodatabase release number in the connection string to decrease connection times. For example, to connect to an ArcSDE 9.2 geodatabase stored in DB2 for LUW, provide the following information in the Service text box: sde92:db2 You must have the proper direct connect drivers installed with ArcGIS Desktop to make this sort of connection.

Adding a direct connection to a geodatabase in PostgreSQL

1. Double-click the Database Connections folder in the Catalog tree.
   
2. Double-click Add Spatial Database Connection to open the Spatial Database Connection Properties dialog box.
   
3. In the Server text box, type the name of the server on which the PostgreSQL database resides.
   
4. To connect to a geodatabase on an instance of PostgreSQL, type the following in the Service text box: "sde:postgresql:<name of the server>".
   

NOTE: If you are making a direct connection to a local PostgreSQL database, type "sde:postgresql:localhost".



5. Type the name of the database to which you want to connect in the Database text box.
   
6. If using database authentication, type your user name and password.
   
7. Uncheck Save username and password if you do not want your login information saved with the connection.
   
8. If using operating system authentication, click the Operating system authentication button. The database authentication information becomes unavailable.
   
9. Click OK.
   
10. Type a new name for the spatial database connection.
    
11. Press Enter.
    

Tip

If your connection fails with the error "Failed to connect to the specified server. Bad login user", make sure the pg_hba.conf file on the database server is configured to allow your client machine to connect to the database.

Connecting to a specific version of the database

1. Click the Database Connections folder in the Catalog tree.
   
2. Follow the instructions in one of the previous sections for connecting to a geodatabase.
   
3. Click Change in the Connection details section of the Spatial Database Connection Properties dialog box.
   
4. On the Connection Details dialog box, click the version to which you want to save a connection. This can be a transactional version or a historical version. Note that the default geodatabase versions for geodatabases stored in user schemas in Oracle (<user_schema>.DEFAULT) and the default version for the geodatabase versions for dbo schema geodatabases in SQL Server (dbo.DEFAULT) are also transactional versions.
   
For transactional versions, click This connection refers to a transactional version, and choose a version from the list.
For a historical version, click This connection refers to a historical version, and click either Connect using a historical marker or Connect using a specific date and time. For each of these options, you need to specify the historical marker or date and time you want to use.

5. Click OK to close the Connection Details dialog box.
   
6. If you don't want to connect to this version of the database each time you start ArcCatalog, uncheck Save the <transactional or historical> version name with the connection file.
   
7. Click OK.
   
8. Type a new name for the database connection.
   
9. Press Enter.
   

Tips

If you uncheck Save the version name with the connection file, the next time you connect to the geodatabase through ArcCatalog or ArcMap using this connection file, you will be prompted to choose the desired version. Learn about versioning.

Please visit the Feedback page to comment or give suggestions on ArcGIS Desktop Help.
Copyright © Environmental Systems Research Institute, Inc.