Creating spatial database connections
Release 9.2 Last modified August 2, 2010	Print all topics in : "Building the Catalog"

About creating spatial database connections

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.

NOTE: The name of the connecting client machine (in other words, the computer from which you are making the spatial database connection) cannot exceed 32 characters.

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 aren't required to type your user name and password to create a connection; however, if you don't, you will be prompted to enter them when a connection is established.

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 user name and password used to log into the operating system. If the login used for the operating system is not a valid geodatabase login, the connection will fail. 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 are 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

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

1. Click the Database Connections folder in the Catalog tree.
   
2. Double-click Add Spatial Database Connection.
   
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 colon; for example, 5153:daisy.

5. If the data is stored in a SQL Server, IBM DB2, or Informix 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, check the box next to Operating system authentication. The database authentication information will be made inactive.
   
8. If you are connecting to a geodatabase in a user's schema in an Oracle database or 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 was successful, the button becomes unavailable. If the test fails, you won't be able 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 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 checkboxes 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 SQL Server Express geodatabase can only be accessed via a direct connection. The section Adding a direct connection to a SQL Server geodatabase below also applies to setting up a spatial database connection to a SQL Server Express geodatabase. 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 an Oracle geodatabase

The first set of steps detail how to add a direct connection to an Oracle geodatabase if you are using database authentication. The second set of steps instruct you how to add a direct connection to an Oracle geodatabase if you are using operating system authentication.

Using database authentication

1. Double-click the Database Connections folder in the Catalog tree.
   
2. Double-click Add Spatial Database Connection.
   
3. Type the name of the server on which the database resides in the Server text box.
   
4. If you're connecting to Oracle9i, in the Service text box, type:

   sde:oracle9i

   
   
If you're connecting to Oracle 10g, type:


sde:oracle10g



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

Using operating system authentication

1. Double-click the Database Connections folder in the Catalog tree.
   
2. Double-click Add Spatial Database Connection.
   
3. Type the name of the server on which the database resides in the Server text box.
   
4. If you're connecting to Oracle9i, in the Service text box, type:
   

sde:oracle9i:/;LOCAL=<SQL_Net_alias>


If you're connecting to Oracle 10g, type:


sde:oracle10g:/;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. Check the box next to Operating system authentication. The database authentication information will become inactive.
   
6. Click OK.
   
7. Type a new name for the spatial database connection.
   
8. 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 an Oracle geodatabase using database authentication or operating system authentication. However, if you use operating system authentication, you must use a direct connection, not an ArcSDE service. Learn more about direct connections

Adding a direct connection to a SQL Server geodatabase

You can use a direct connection for ArcSDE Enterprise, Workgroup, or Personal 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. Type the name of the server on which the database resides in the Server text box.
   
4. To connect to an ArcSDE Enterprise 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 Enterprise geodatabase on a named instance of SQL Server, type the folowing in the Service text box:


sde:sqlserver:<name or IP Address of the server>\<instance_name>



If connecting to an ArcSDE Personal or Workgroup geodatabase, 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 Personal or Workgroup geodatabase 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 for an ArcSDE Enterprise geodatabase, type your user name and password.
   

NOTE: You cannot use database authentication for a connection to an ArcSDE Workgroup or Personal geodatabase.



7. Uncheck Save username and password if you do not want your login information saved with the connection.
   
8. If using operating system authentication, check the box next to Operating system authentication. The database authentication information will become inactive.
   
9. If you are connecting to an ArcSDE Personal or Workgroup geodatabase or an ArcSDE Enterprise 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.
    

Below is an example of connecting to an ArcSDE Personal geodatabase.

Adding a direct connection to a DB2 or Informix geodatabase

1. Double-click the Database Connections folder in the Catalog tree.
   
2. Double-click Add Spatial Database Connection.
   
3. Type the name of the server on which the database resides in the Server text box.
   
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, check the box next to Operating system authentication. The database authentication information will be grayed out.
   
9. Click OK.
   
10. Type a new name for the spatial database connection.
    
11. Press Enter.
    

Tip

Learn more about direct connections

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 preceding sections for connecting to a geodatabase.
   
3. Click Change in the Connection Details section.
   
4. In 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 geodatabases for those stored in user schema's in Oracle (<user_schema>.DEFAULT) and the DEFAULT databases 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 will indicate 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 open 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.