English

How To: 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

Verify that the following software products are installed before following these instructions:
- ArcIMS 9.x
- SQL Server 2000/2005

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