# Updating MS-SQL Database Configuration for Data Extraction

To keep the MS SQL database up to date with the Imprivata EAM managed system, you need to run the data extraction process periodically. The Imprivata EAM integration only aggregates data from the MS SQL database, so there is a risk of aggregating outdated data if the MS SQL database is not updated using the data extraction process.

You can complete the data extraction process for the MS SQL database using either of the following methods:

- [Manually Updating MS SQL Database with Imprivata EAM Data](<#manually-updating-ms-sql-database-with-imprivata-eam-data>)
- [Automatically Updating MS SQL Database with Imprivata EAM Data](<#automatically-updating-ms-sql-database-with-imprivata-eam-data>)

## Manually Updating MS SQL Database with Imprivata EAM Data

Follow these steps to perform the data extraction from the MS SQL database:

1. Create a data extraction user in the Imprivata managed system with the appropriate permissions:

   1. Create a designated service account (in any domain).

      > **Important**
      >
      > The Imprivata EAM integration is set up to only work with a `DataExtractionUser` account name. Ensure to use this name only while creating the service account.
   2. Create a user policy with only the password modality enabled and assign it to the previously created `DataExtractionUser` user account.
   3. Verify that the designated user can authenticate with the Imprivata EAM managed system.
2. Generate an Ostick token for the data extraction user:

   1. To execute the data extraction API, you need to get an OStick token by executing the authUser API with the following details:

      - **Username**: The username of the `DataExtractionUser`.
      - **Password**: The password of the `DataExtractionUser`.
      - **DomainName**: The domain name of the `DataExtractionUser`.

        Following is an example of the CURL request for the authUser API:

        ```text
        curl --location 'https://<Imprivata_IP>/sso/ProveIDWeb/v1/AuthUser' \
        --header 'Content-Type: text/xml' \
        --header 'Accept: text/xml' \
        --header 'isx-product: 9ac34f7f-22fc-40d0-aec3-9dc3d8bfc327' \
        --data '<Request>
            <ModalityAuthInput modalityID="PWD">
                <AuthRequest>
                    <PasswordVerificationRequest>
                        <UserIdentity>
                            <Username>DataExtractionUser</Username>
                            <Domain>Domain_Name</Domain>
                        </UserIdentity>
                        <Password>Password</Password>
                    </PasswordVerificationRequest>
                </AuthRequest>
            <CreateAuthTicket>true</CreateAuthTicket>
        </Request>'
        ```
   2. You will receive an AuthTicket in the response, which must be URL-encoded before using it in the Data Extraction API.
3. Run the Data Extraction API with the following details:

   - **server** (*Required*): Specifies the target database address. For example, IPName, or FQDN.
   - **port** (*Optional*): Defines the port on which the target database is running. The default is 1433.
   - **databaseName** (*Optional*): Defines the target database name. The default is `OneSignStaging`.
   - **user** (*Required*): Part of the credential used to access the target database.
   - **password** (*Required*): Part of the credential used to access the target database.

   Following is an example of the CURL request for the Data Extraction API:

   ```text
   curl --location '<https://<Imprivata_IP>>/sso/ProveIDWeb/v18/DataExtraction' \
   --header 'isx-product: 9ac34f7f-22fc-40d0-aec3-9dc3d8bfc327' \
   --header 'Content-Type: text/xml' \
   --header 'Accept: text/xml' \
   --header 'Authorization: OStick ostick.ticket=rO0ABXQAAzEuMnQAJGZlNmY0YzEzLTBkMzctNDFjNy1hZjRjLTEzMGQzN2UxYzcyZXQAJDEwNmJhZjNkLTRiODUtNDQ1YS1hYmFmLTNkNGI4NTI0NWE3MHNyAA5qYXZhLnV0aWwuRGF0ZWhqgQFLWXQZAwAAeHB3CAAAAZWkdpZleHNyABFqYXZhLmxhbmcuQm9vbGVhbs0gcoDVnPruAgABWgAFdmFsdWV4cAB0AAB3BAAAAAF0AANQV0R3BP%2F%2F%2F%2F93iAAAAMIAAACAYmoas2KMf5MH4yzQWIlT3Ljam%2Bg2FN3l65qaCAWTB4d6VYGuOw5mflD0W4X18jkDP0Si5CpfAsdc9F0EhJzOS8%2FjPD0c%2FjPI62F89rWhsV%2BbCQ8Dbvj6OsYKaja%2BESYCu4OvpqePjPdPKMiNOMMayHbG7NVZRNf6YVcHMpZY6h0%3D' \
   --header 'Accept-Encoding: identity' \
   --header 'Connection: Keep-Alive' \
   --data '<Request>
     <DataExtractionInput>
       <DataExtractionRequest>
         <rdbms>sqlserver</rdbms>
         <server>my-ms-sql-server-address</server>
         <port>1433</port>
         <databaseName>OneSignStaging</databaseName>
         <user>imprivata</user>
         <password>imprivata</password>
         <version>v1</version>
         <uctsStartTime>2025-01-01 00:00:00 +00:00</uctsStartTime>
         <directive>truncateTarget</directive>
         <directive>keepReportFiles</directive>
         <reportLimitDays>300</reportLimitDays>
         <reportLimitRows>10000000</reportLimitRows>
       </DataExtractionRequest>
     </DataExtractionInput>
   </Request>'
   ```

## Automatically Updating MS SQL Database with Imprivata EAM Data

Automatically updating an MS SQL database with Imprivata EAM data using an SSIS package for secure API-based data extraction and loading into SQL Server from Imprivata's OneSign system helps you with the following:

- Creates the required database and their tables.
- Automates AuthTicket based API authentication.
- Uses the AuthTicket to securely extract data via a second API.
- Loads retrieved data into SQL Server.
- Supports scheduled and repeatable execution through SQL Server Agent.

### Prerequisites

Before proceeding with this process, ensure that the following components are available on your system:

1. SQL Server Integration Services (SSIS)

   SSIS must be installed and enabled on your SQL Server instance. This is necessary for creating and executing SSIS packages.
2. Supported SQL Server Versions

   This process is compatible with the following SQL Server versions:

   - SQL Server 2017 (v14)
   - SQL Server 2019 (v15)
   - SQL Server 2022 (v16)
3. SQL Server Management Studio (SSMS)

   SSMS should be installed on the machine where you will be managing the SSIS packages and SQL Server objects.
4. SQL Server Agent

   The SQL Server Agent service must be enabled on your SQL Server instance. This is required for scheduling and automating the execution of SSIS packages.
5. Required Access Permissions

   Ensure that you have the following access permissions:

   - Deploy packages to SSISDB (if using SSIS Catalog)

     You must have permission to deploy SSIS packages to the SSISDB catalog if you plan to use the SSIS Catalog deployment method.
   - View and edit SSIS package parameters

     You should have permission to view and modify SSIS package parameters to configure the necessary settings for the data extraction process.
   - Create and manage SQL Server Agent jobs

     You must have permission to create and manage SQL Server Agent jobs, which will be used to schedule and execute the SSIS packages.
   - File access permission for `.dtsx` package (if using File System method)

     To deploy the SSIS package using the File System method, ensure that you have the necessary file access permissions to read and execute the `.dtsx` package file.
   - Read/write access to target SQL Server database

     You must have read and write permissions on the target SQL Server database where the extracted data will be loaded.

### Downloading MS SQL Configuration File

Refer to the following required versions to download the MS SQL Configuration file:

- [MS SQL Configuration 2017](<https://download.microsoft.com/download/5/A/7/5A7065A2-C81C-4A31-9972-8A31AC9388C1/SQLServer2017-SSEI-Dev.exe>)
- [MS SQL Configuration 2019](<https://go.microsoft.com/fwlink/?linkid=866662>)
- [MS SQL Configuration 2022](<https://go.microsoft.com/fwlink/p/?linkid=2215158&amp;clcid=0x409&amp;culture=en-us&amp;country=us>)

### Updating the MS SQL Database Automatically with Imprivata EAM Data

Follow these steps to update the MS SQL database automatically with Imprivata EAM data:

1. [Creating a Database for Data Extraction](<#creating-a-database-for-data-extraction>)
2. [Running a Scheduled Job for Data Extraction](<#running-a-scheduled-job-for-data-extraction>)

### Creating a Database for Data Extraction

1. Open the SQL script

   Open the `create_imprivate_onsign_database.sql` script file. To download the SQL script, refer to [Imprivata EAM - Database script](<https://community.sailpoint.com/knowledge/Imprivata-EAM-Setup-MS-SQL-Server-database>).
2. Update login credentials

   Locate the line:

   ```text
   CREATE LOGIN <Customer> WITH PASSWORD = '<2Strong2Remember!>';
   ```

   1. Replace `<Customer>` with the desired username.
   2. Replace `<2Strong2Remember!>` with your chosen password.
3. Replace all placeholders

   Replace every occurrence of login credentials throughout the script with the login credentials you [updated](<#update-login-credentials>).
4. Execute the script

   Run the modified SQL script on your Microsoft SQL Server instance that you are going to deploy to SSISDB.

### Running a Scheduled Job for Data Extraction

You can use either of the following methods to run a scheduled job for data extraction from the MS SQL database.

#### Method 1: Running Package Through SSISDB

Follow these steps to run the package using SSISDB:

1. Import the package into SSISDB.

   1. Go to **Integration Services Catalogs \> SSISDB \> \[`YourFolderName`\]**.
   2. Right-click the **Deploy Package** folder.
   3. Select **Package Deployment** from the dropdown at the right top corner.
   4. Browse the packages folder and then, select **Deploy**. After selecting the package, select **Refresh**.
   5. Select **Next** two times to go the **Select Destination** option.
   6. Select **Connect** and provide the path using the **Browse** option. (For example, `/SSISDB/Project/<YourProjectname>`)
   7. Select **Next** and then **Deploy** to complete the process.
2. Configure runtime variables in SSISDB.

   1. In SSMS, go to the deployed package under **SSISDB**.
   2. Right-click the package \> **Configure**.
   3. Under the Parameters tab, provide values for:

      | Parameters | Definition | Type of the Parameters |
      | --- | --- | --- |
      | AuthTicketUrl | The URL to get Auth token | String |
      | DataExtractionUrl | The URL to extract the data | String |
      | DatabaseUser | The SQL Server User | String |
      | DatabasePassword | The SQL Server login password | String |
      | DatabaseServer | The SQL Server Instance name or IP | String |
      | DatabaseName | Target SQL database name | String |
      | Username | Imprivata User | String |
      | Password | Imprivata password | String |
      | Domain | The domain for authentication | String |
      | ProductId | The ID of the product | String |
   4. Select **Save and Close**.
3. Schedule the package with SQL Server Agent.

   1. Go to **SQL Server Agent \> Jobs**.
   2. Right-click **Jobs \> New Job**.
   3. On the General tab, provide the following details:

      `Name: [Provide Name for the Event]`
4. Go to **Steps \> New**, provide the following details:

   - **Step Name**: Name of the step
   - **Type**: SQL Server Integration Services Package
   - **Package source**: SSIS Catalog.
   - **Server**: Your SQL instance.
   - **Package**: Select your deployed SSISDB package.
5. Under **Schedules**, define frequency (for example, Daily at 2:00:00).
6. Select **Save**, and then **Enable** the job.

#### Method 2: Running Package Directly from File System

This method allows you to schedule a `.dtsx` file without SSISDB or Visual Studio.

> [!NOTE]
>
> - You do not need to deploy the `.dtsx` package into SSISDB or import it into a Visual Studio project to schedule it.
> - You can directly schedule and execute the `.dtsx` file from the File System by referencing its path by following the steps in [SQL Server Agent Job Setup (File System)](<#sql-server-agent-job-setup-file-system>).

To run the package directly from the File System, ensure the following:

- The SQL Server Agent service account has permission to access the `.dtsx` file path.
- All necessary variables and configuration values (for example, API URLs and connection strings) are defined within the package or passed via command-line arguments (if needed).
- The `.dtsx` file is stored on a local or network path accessible to SQL Server.

##### SQL Server Agent Job Setup (File System)

Follow these steps to schedule and execute the `.dtsx` file from the File System using a SQL Server Agent job setup:

1. In SSMS, go to **SQL Server Agent \> Jobs \> New Job**.
2. Under the **Steps** tab **\> New**:

   - **Type**: Enter the SQL Server Integration Services Package
   - **Package Source**: Enter the File System
   - **Package**: Browse or type the full path to the `.dtsx` file.
   - Configure logging and error handling, if needed.
3. Under **Schedules**, define your execution schedule.
4. Select **Save** to save the job.

##### Verify Execution of SQL Server Agent Job

Follow these steps to manually test SQL Server Agent job execution:

1. In **SQL Server Agent \> Jobs**, right-click your job.
2. Select **Start Job at Step**.
3. Monitor progress in real time.
4. Review history logs after execution for errors or success confirmation.