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

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.

Generate an Ostick token for the data extraction user:

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:

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>'

You will receive an AuthTicket in the response, which must be URL-encoded before using it in the Data Extraction API.

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:

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:

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.
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)
SQL Server Management Studio (SSMS)

SSMS should be installed on the machine where you will be managing the SSIS packages and SQL Server objects.
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.
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:

Updating the MS SQL Database Automatically with Imprivata EAM Data

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

Creating a Database for Data Extraction
Running a Scheduled Job for Data Extraction

Creating a Database for Data Extraction

Open the SQL script

Open the create_imprivate_onsign_database.sql script file. To download the SQL script, refer to Imprivata EAM - Database script.
Update login credentials

Locate the line:
```
CREATE LOGIN <Customer> WITH PASSWORD = '<2Strong2Remember!>';
```
1. Replace <Customer> with the desired username.
2. Replace <2Strong2Remember!> with your chosen password.
Replace all placeholders

Replace every occurrence of login credentials throughout the script with the login credentials you updated.
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:

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.

Configure runtime variables in SSISDB.

In SSMS, go to the deployed package under SSISDB.
Right-click the package > Configure.

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

Select Save and Close.

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]
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.
Under Schedules, define frequency (for example, Daily at 2:00:00).
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).

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:

In SSMS, go to SQL Server Agent > Jobs > New Job.
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.
Under Schedules, define your execution schedule.
Select Save to save the job.

Verify Execution of SQL Server Agent Job

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

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

Documentation Feedback

Feedback is provided as an informational resource only and does not form part of SailPoint’s official product documentation. SailPoint does not warrant or make any guarantees about the feedback (including without limitation as to its accuracy, relevance, or reliability). All feedback is subject to the terms set forth at https://developer.sailpoint.com/discuss/tos.