Use the JDBC-ODBC Bridge to implement ArcIMS authentication with SQL Server 2000/2005

Summary

Instructions provided describe how to use Java Database Connectivity (JDBC) authentication with a Microsoft SQL server database.

Procedure

1. Start Enterprise Manager/Management Studio and create a new database named 'ArcIMS_ACL'.
2. Create two tables, one named 'acl_users' and the other named 'acl_permissions'.
   
   'acl_users' contains the list of usernames and passwords for the people, who are given access to the MapServices. It also contains a unique user ID that ArcIMS uses to relate this table to the 'acl_permissions' table.
   
   'acl_permissions' associates each map service with one or more usernames and passwords. It is linked to the 'acl_users' table using the 'userid' field.
    
   A. Add three fields to the 'acl_users' table with the following properties:
   
   Column Name: userid
   Data Type: int
   Length: 4
   
   Column Name: username
   Data Type: nvarchar
   Length: 64
   
   Column Name: password
   Data Type: nvarchar
   Length: 64
   
   B. Add, as an example, the following values as a single record and save the changes.
   
   userid = 1
   username = test
   password = pass
   
   C. Add seven fields to the 'acl_permissions' table with the following properties:
   
   Column Name: userid
   Data Type: int
   Length: 4
   
   Column Name: service
   Data Type: nvarchar
   Length: 64
   
   Column Name: active
   Data Type: int
   Length: 4
   
   Column Name: expiration
   Data Type: smalldatetime
   Length: 4
   
   Column Name: tclients
   Data Type: nvarchar
   Length: 16
   
   Column Name: ftags
   Data Type: nvarchar
   Length: 16
   
   Column Name: roles
   Data Type: nvarchar
   Length: 16
   
   Leave all other field properties blank or at their default values.
   
   D. Add, for this example, the following values.
   
   userid = 1
   service = <EnterServiceNameHere>
   active = 1
   
   Leave all other fields blank.

   Note:
   For more information on the service restrictions defined in this example, see the ArcIMS Help documentation under "Enabling authentication with a JDBC-based ACL" or "ACL attribute reference".

   E. Give an existing or new SQLServer user access permissions to the new database and the tables. The sa (system administrator) user will be used in this article.
3. Create an ODBC connection to SQL Server.
    
   A. Start the ODBC Administrator on the same machine where the ArcIMS Servlet Connector is installed:
   
   Click Start > Settings > Control Panel > Administrative Tools > Data Sources (ODBC).
   B. Select, in the ODBC Administrator dialog box, the tab 'System DSN'(Data Source Name) and click Add.
   C. Select SQL Server and click Finish.
   D. Enter the name 'ArcIMS_ACL' and the information required to connect to the SQL Server instance. Select the SQL Server authentication and put the username and password of the sa user or the according user, who is owner of the created tables.
   E. Select the database created in Step 1, 'ArcIMS_ACL'.
   F. Confirm that the connection was added to the 'System DSN' tab in the ODBC Administrator.
4. Modify the Esrimap_prop file.
   A. Navigate to the location of the 'Esrimap_prop' file in the ArcIMS Servlet Connector's directory associated with the Web Server/Servlet Engine.
   
   For ServletExec:
   <drive>:\Program Files\New Atlanta\ServletExec ISAPI\Servlets
   For Tomcat:
   <drive>:\<Tomcat Install Location>\webapps\servlet\WEB-INF\classes
   
   B. Rename the existing 'Esrimap_prop' file to 'Esrimap_prop_original' for backup.
   C. Copy the file 'Esrimap_prop_original' and rename the copy to 'Esrimap_prop' in the same location.

   Note:
   The original 'Esrimap_prop' file settings are stored in the backup file 'Esrimap_prop_original'.

   D. Edit or confirm, in the new 'Esrimap_prop' file, the following attributes and values:

   Note:
   Some attributes may need to be made active by removing the # commenting symbol.

   Code:
   authenticate=True
   authMethods=Basic
   authenticateWithSessions=True
   useJdbc=True
   jdbcDriver=sun.jdbc.odbc.JdbcOdbcDriver
   jdbcUrl=jdbc:odbc:ArcIMS_ACL
   jdbcUser=<user>
   jdbcPassword=<password>
   jdbcPermTable=acl_permissions
   jdbcUserTable=acl_users
   jdbcUidColumn=userid

   Note:
   The jdbcDriver value 'sun.jdbc.odbc.JdbcOdbcDriver' references the Java class containing the JDBC-ODBC Bridge functionality. It is included with the JRE/JDK. The jdbcUrl value 'jdbc:odbc:ArcIMS_ACL' is standard syntax for referencing the ODBC system DSN that the JDBC-ODBC Bridge is using.

   Note:
   Note that a jdbc user and password are not required when using an Access database. See the ArcIMS Help documentation under "Enabling authentication with a JDBC-based ACL" for more details on attributes and values.

   Note:
   It is possible to use a third-party JDBC driver. For example, Oracle or Microsoft JDBC drivers can connect directly to the RDBMS. These drivers often can be found at the corresponding software manufacturers' support sites. When using these drivers, ensure that the driver is available in the servlet engine's common library directory. For example, Tomcat maintains the common library directory in $CATALINA_HOME/common/lib. In most cases, copy one or more JAR files associated with the JDBC driver to this directory.

   E. Save the Esrimap_prop file.
   F. Restart the Web server and servlet engine.

   Note:
   If using IIS as the Web server, it may be necessary to configure IIS for authentication. For more information, refer to step 8 in the ArcIMS Help topic, "Enabling authentication with a JDBC-based ACL".