000021943 - How to run scripts using database for RSA SecurID Web Express

Document created by RSA Customer Support Employee on Jun 16, 2016Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 2Show Document
  • View in full screen mode

Article Content

Article Number000021943
Applies ToRSA SecurID Web Express 1.2
RSA Authentication Deployment Manager 1.2
Microsoft Windows 2000 Advanced Server SP4
Microsoft SQL Server 2000
IssueHow to run scripts using database for RSA SecurID Web Express
Resolution

 

 

Here is a quick how-to guide to set up Microsoft SQL 2000 to work with RSA SecurID Web Express. This document describes how to install the SQL scripts into the newly created database:

1.   Using the SQL Server Enterprise Manager create the following two databases under the Local Machine ? Databases?

2.   Create new Database ? RSAWE_Transaction_Database ?

3.   Create new Database ? RSAWE_Archive_Database ?

4.   Create two new users for each database

a.   From the  Enterprise manager select the new database and create a new user by selecting the pull down arrow:

 

b.   Select new:

 

c. Specify your username:

 

 

5.   Open the SQL Query tool using Windows Authentication.

      a. Try connecting to the new database with the new user that was just created. This function before we proceed.

      b. Repeat steps for both database and both users.

6.   In the view Window select one of the new RSAWE_??_Databases?

7.   Cut the Admins and paste in this window and once completed you can execute the go command by selecting the check.

8.   Repeat for both databases and requests.sql and costcenters.sql

a.   The admin script needs to be edited and have the last comit; line removed.
The below example can be used in the SQL 2000 query tool:

 

Example: Admins

--------------------------------------------------cut--------------------------------------------------
CREATE TABLE ADMINS(
           USERID VARCHAR(50), 
           FIRST_NAME VARCHAR(50), 
           LAST_NAME VARCHAR(50), 
           EMAIL VARCHAR(50), 
           EMAIL_NOTIFICATION VARCHAR(50), 
           PASSWORD VARCHAR(50), 
           MANAGER_ACCESS VARCHAR(50), 
           DIST_ADMIN_ACCESS VARCHAR(50), 
           CONFIGURATION_ACCESS VARCHAR(50), 
           EXTRA_FIELD1 VARCHAR(50), 
           EXTRA_FIELD2 VARCHAR(50),
                       PRIMARY KEY(USERID)
);

CREATE UNIQUE INDEX USERID_IDX ON ADMINS (USERID);

---------------------------------------------cut-------------------------------------------------------
Example: Requests
--------------------------------------------cut---------------------------------------------------------

CREATE TABLE REQUESTS(
            REQUEST_NUMBER NUMERIC(32,0), 
            USERID VARCHAR(50), 
            FIRST_NAME VARCHAR(50), 
            LAST_NAME VARCHAR(50), 
            COST_CENTER VARCHAR(50), 
            EMAIL VARCHAR(50), 
            ACCESS_LEVEL VARCHAR(50), 
            CUSTOM1 VARCHAR(50), 
            CUSTOM2 VARCHAR(50), 
            CUSTOM3 VARCHAR(50), 
            CUSTOM4 VARCHAR(50), 
            CUSTOM5 VARCHAR(50), 
            CUSTOM6 VARCHAR(50), 
            CUSTOM7 VARCHAR(50), 
            CUSTOM8 VARCHAR(50), 
            ACE_DB_FIRST_NAME VARCHAR(50), 
            ACE_DB_LAST_NAME VARCHAR(50), 
            ACE_DB_ACCESS_LEVEL1 VARCHAR(50), 
            ACE_DB_ACCESS_LEVEL2 VARCHAR(50), 
            ACE_DB_ACCESS_LEVEL3 VARCHAR(50), 
            ACE_DB_ACCESS_LEVEL4 VARCHAR(50), 
            ACE_DB_ACCESS_LEVEL5 VARCHAR(50), 
            ACE_DB_TOKEN_COUNT VARCHAR(50), 
            EXT_DB_USER_ID VARCHAR(50), 
            EXT_DB_FIRST_NAME VARCHAR(50), 
            EXT_DB_LAST_NAME VARCHAR(50), 
            EXT_DB_COST_CENTER VARCHAR(50), 
            EXT_DB_EMAIL VARCHAR(50), 
            EXT_DB_ACCESS_LEVEL VARCHAR(50), 
            EXT_DB_CUSTOM1 VARCHAR(50), 
            EXT_DB_CUSTOM2 VARCHAR(50), 
            EXT_DB_CUSTOM3 VARCHAR(50), 
            EXT_DB_CUSTOM4 VARCHAR(50), 
            EXT_DB_CUSTOM5 VARCHAR(50), 
            EXT_DB_CUSTOM6 VARCHAR(50), 
            EXT_DB_CUSTOM7 VARCHAR(50), 
            EXT_DB_CUSTOM8 VARCHAR(50), 
            ORIG_USER_ID VARCHAR(50), 
            ORIG_FIRST_NAME VARCHAR(50), 
            ORIG_LAST_NAME VARCHAR(50), 
            ORIG_COST_CENTER VARCHAR(50), 
            ORIG_EMAIL VARCHAR(50), 
            ORIG_ACCESS_LEVEL VARCHAR(50), 
            ORIG_CUSTOM1 VARCHAR(50), 
            ORIG_CUSTOM2 VARCHAR(50), 
            ORIG_CUSTOM3 VARCHAR(50), 
            ORIG_CUSTOM4 VARCHAR(50), 
            ORIG_CUSTOM5 VARCHAR(50), 
            ORIG_CUSTOM6 VARCHAR(50), 
            ORIG_CUSTOM7 VARCHAR(50), 
            ORIG_CUSTOM8 VARCHAR(50), 
            NEW_ACE_USER VARCHAR(50),
            REQUEST_STATE VARCHAR(50),  
            REQUEST_DATE VARCHAR(50), 
            APPROVAL_CODE VARCHAR(50), 
            APPROVAL_DATE VARCHAR(50), 
            APPROVED_BY VARCHAR(50), 
            APPROVED_TOKEN_SERIAL_NUMBER VARCHAR(50), 
            DISTRIBUTED_DATE VARCHAR(50), 
            DISTRIBUTED_BY VARCHAR(50), 
            COMPLETED_DATE VARCHAR(50), 
            ERROR_CODE VARCHAR(100),
            REJECTED_DATE VARCHAR(50), 
            EXTRA_FIELD1 VARCHAR(50), 
            EXTRA_FIELD2 VARCHAR(50),
        CUSTOM0  VARCHAR(50),
        CUSTOM0_TYPE VARCHAR(50),
        CUSTOM0_KEY VARCHAR(50),
        CUSTOM1_TYPE VARCHAR(50),
        CUSTOM1_KEY VARCHAR(50),
        CUSTOM2_TYPE VARCHAR(50),
        CUSTOM2_KEY VARCHAR(50),
        CUSTOM3_TYPE VARCHAR(50),
        CUSTOM3_KEY VARCHAR(50),
        CUSTOM4_TYPE VARCHAR(50),
        CUSTOM4_KEY VARCHAR(50),
        CUSTOM5_TYPE VARCHAR(50),
        CUSTOM5_KEY VARCHAR(50),
        CUSTOM6_TYPE VARCHAR(50),
        CUSTOM6_KEY  VARCHAR(50),
        CUSTOM7_TYPE VARCHAR(50),
        CUSTOM7_KEY VARCHAR(50),
        CUSTOM8_TYPE VARCHAR(50),
        CUSTOM8_KEY VARCHAR(50),
        CUSTOM9  VARCHAR(50),
        CUSTOM9_TYPE VARCHAR(50),
        CUSTOM9_KEY VARCHAR(50),
        CUSTOM10 VARCHAR(50),
        CUSTOM10_TYPE VARCHAR(50),
        CUSTOM10_KEY VARCHAR(50),
        CUSTOM11 VARCHAR(50),
        CUSTOM11_TYPE VARCHAR(50),
        CUSTOM11_KEY VARCHAR(50),
        CUSTOM12 VARCHAR(50),
        CUSTOM12_TYPE  VARCHAR(50),
        CUSTOM12_KEY VARCHAR(50),
        CUSTOM13 VARCHAR(50),
        CUSTOM13_TYPE VARCHAR(50),
        CUSTOM13_KEY VARCHAR(50),
        CUSTOM14  VARCHAR(50),
        CUSTOM14_TYPE VARCHAR(50),
        CUSTOM14_KEY VARCHAR(50),
        CUSTOM15 VARCHAR(50),
        CUSTOM15_TYPE VARCHAR(50),
        CUSTOM15_KEY  VARCHAR(50),
        CUSTOM16 VARCHAR(50),
        CUSTOM16_TYPE VARCHAR(50),
        CUSTOM16_KEY VARCHAR(50),
        TOKEN_TYPE_KEY VARCHAR(50),
        TOKEN_TYPE_VAL VARCHAR(50),
        EXT_DB_CUSTOM0 VARCHAR(50),
        EXT_DB_CUSTOM9 VARCHAR(50),
        EXT_DB_CUSTOM10  VARCHAR(50),
        EXT_DB_CUSTOM11 VARCHAR(50),
        EXT_DB_CUSTOM12 VARCHAR(50),
        EXT_DB_CUSTOM13 VARCHAR(50),
        EXT_DB_CUSTOM14  VARCHAR(50),
        EXT_DB_CUSTOM15 VARCHAR(50),
        EXT_DB_CUSTOM16 VARCHAR(50),
        ORIG_CUSTOM0 VARCHAR(50),
        ORIG_CUSTOM9  VARCHAR(50),
        ORIG_CUSTOM10 VARCHAR(50),
        ORIG_CUSTOM11 VARCHAR(50),
        ORIG_CUSTOM12 VARCHAR(50),
        ORIG_CUSTOM13 VARCHAR(50),
        ORIG_CUSTOM14 VARCHAR(50),
        ORIG_CUSTOM15 VARCHAR(50),
        ORIG_CUSTOM16 VARCHAR(50),
        REQUEST_PASSWORD VARCHAR(50),
        PRIMARY KEY(REQUEST_NUMBER)
);

CREATE UNIQUE INDEX REQUEST_NUMBER_IDX ON REQUESTS (REQUEST_NUMBER);
CREATE INDEX REQUEST_STATE_IDX ON REQUESTS (REQUEST_STATE);

----------------------------------------------cut-------------------------------------------------------------------------------
Example: costcenters
-----------------------------------------------------cut------------------------------------------------------------------------
CREATE TABLE COSTCENTERS(
            COST_CENTER VARCHAR(50), 
            MGR_USER_ID VARCHAR(50), 
            DIST_USER_ID VARCHAR(50), 
            EXTRA_FIELD1 VARCHAR(50), 
            EXTRA_FIELD2 VARCHAR(50),
                        PRIMARY KEY(COST_CENTER)
);

CREATE UNIQUE INDEX COST_CENTER_IDX ON COSTCENTERS (COST_CENTER);

INSERT INTO COSTCENTERS(COST_CENTER, MGR_USER_ID, DIST_USER_ID)
VALUES ('Engineering Department', 'admin', 'admin');

INSERT INTO COSTCENTERS(COST_CENTER, MGR_USER_ID, DIST_USER_ID)
VALUES ('Finance Department', 'admin', 'admin');

INSERT INTO COSTCENTERS(COST_CENTER, MGR_USER_ID, DIST_USER_ID)
VALUES ('Human Resources Department', 'admin', 'admin');

INSERT INTO COSTCENTERS(COST_CENTER, MGR_USER_ID, DIST_USER_ID)
VALUES ('Marketing Department', 'admin', 'admin');

INSERT INTO COSTCENTERS(COST_CENTER, MGR_USER_ID, DIST_USER_ID)
VALUES ('Sales Department', 'admin', 'admin');

INSERT INTO COSTCENTERS(COST_CENTER, MGR_USER_ID, DIST_USER_ID)
VALUES ('None', 'admin', 'admin');

------------------------------------------------------cut-------------------------------------------------------------------
Drivers need to be applied to the server to get things operational

http://www.microsoft.com/downloads/details.aspx?FamilyID=9f1874b6-f8e1-4bd6-947c-0fc5bf05bf71&displaylang=en

In the config.properties file:

1.   We needed to get the latest driver from MS and modify our config.properties file to use them:

a.   Driver Name: com.microsoft.jdbc.sqlserver.SQLServerDriver  

b.   Data Source: jdbc:microsoft:sqlserver://localhost:1433;database=RSAWE_Transaction_Database

C: Data Source: jdbc:microsoft:sqlserver://localhost:1433;database=RSAWE_Archive_Database

Example: Config.properties file. database connection:

########################################################################################
#
#   JDBC Configuration
#
########################################################################################

# Active Requests Database
#DRIVER_NAME=sun.jdbc.odbc.JdbcOdbcDriver
DRIVER_NAME=com.microsoft.jdbc.sqlserver.SQLServerDriver
DB_USER_ID=dude1
DB_PASSWORD=admin1234
#DB_URL=jdbc:odbc:RSAWE_Transaction_Database
DB_URL=jdbc:microsoft:sqlserver://localhost:1433;database=RSAWE_Transaction_Database
INIT_CONN_SIZE=1
MAX_CONN_SIZE=200

# jdbc=jdbc:JSQLConnect://server.edu/database=TESTPILOT

# Archive Requests Database
ARCHIVE_DRIVER_NAME=com.microsoft.jdbc.sqlserver.SQLServerDriver
ARCHIVE_DB_USER_ID=dude2
ARCHIVE_DB_PASSWORD=admin1234
#ARCHIVE_DB_URL=jdbc:odbc:RSAWE_Archive_Database
ARCHIVE_DB_URL=jdbc:microsoft:sqlserver://localhost:1433;database=RSAWE_Archive_Database
ARCHIVE_INIT_CONN_SIZE=1
ARCHIVE_MAX_CONN_SIZE=200

2.   The JAR files that came with the sql2000 driver need to be placed onto the Web Express server and dropped into the following directory:

C:\RSAWebExpress\JRun\servers\default\rsaswe\WEB-INF\lib

The jar files are ? MSBASE.JAR , MSSQLSEVER.JAR , MSUTIL.JAR ?
 

Lastly, stop and restart Web Express.

Legacy Article IDa25813

Attachments

    Outcomes