Need help/direction for Migrating existing IdentityIQ instance from MSSQL to RDS/MySQL DB

IdentityIQ (IIQ) IIQ Discussion and Questions
1

Which IIQ version are you inquiring about?

IdentityIQ 8.4

Please share any images or screenshots, if relevant.

Share all details about your problem, including any error messages you may have received.

Hi,

I am fairly new to working on Sailpoint IdentityIQ (8.4). We have an instance in our dev lab with the following 3-machine (on AWS) configuration:

  • DB Machine: Windows with MS SQL 2022 installed
  • Sailpoint UI Machine: RHEL 9 with Sailpoint IdentityIQ 8.4 installed
  • Sailpoint Task Machine: RHEL 9

The Sailpoint software on the UI machine is running in a Podman container.

The above configuration was deployed in our dev environment by another person, who is no longer here.

We also have a new MySQL Database configured in AWS RDS.

I was tasked to start working on this about a week+ ago. I have quite a lot of previous experience working on other IDM/ICAM products, including Oracle (OAM/OIM) and MS (MIIS/FIM), etc., on both Windows and RHEL, but this is my 1st time working with Sailpoint (though I have heard about it for quite a while), so this is both a time of dread (since no experience :slight_smile: ! ) and interest :wink: !

At this point, I have been to get the above configuration up and running again, and beyond that, the first thing that I’ve been asked to look into doing is to modify the Sailpoint environment to use the Mysql database on AWS RDS, instead of the on-premise MS SQL DB instance, and I was just able to confirm yesterday that the RDS database was working (all the machines are fairly “fresh” and, especially the RHEL9 machines were missing a lot of configuration, e.g. firewall setup, etc.).

My apologies, in advance, for the LONG setup, but I wanted to provide as much info as possible.

Also, I have included a screenshot of the Identity Warehouse in the Sailpoint web app, so you all can discern how much (or how little) information has been populated in the system. If there are other screenshots or info that might be needed, please let me know?

Anyway, as can be seen from the screenshot, there is just a small number of users configured, and also I’ve been told, they are all right if we either just switch to the RDS database and manually re-populate the database, or, if we actually do a full migration including both schema and data. I feel like I am not knowledgeable enough at this point to decide that yet, so I would be interested in feedback about that?

Also, FYI, I found. am aware of, this thread: [Restarting discussion] - Anyone migrated the Sailpoint IdentityIQ database schema and data from Oracle to Postgres after 8.4 version upgrade?

So, besides recos for doing full migration vs. just re-create the schema on the RDS MySQL DB and re-configure Sailpoint to use the new RDS MySQL DB instead of the MS SQL DB, I am looking for information about HOW to do either one of those approaches?

Thanks, and again, apologies in advance for the rather long 1st post :frowning: !!

Jim

2

Sorry, here’s the screenshot of the Identity Warehouse that I mentioned:

image
image1582Ă—1197 152 KB

3

Hello, @jimcpl1000

Welcome to the SailPoint community! It’s great that you’re taking on this new challenge with enthusiasm. Given your background with other identity management systems, you’ll find some familiar concepts, but there will also be unique aspects to SailPoint IdentityIQ (IIQ).

Recommendation

Given the small amount of data currently in your system and your newness to SailPoint, I recommend starting fresh with the MySQL database on AWS RDS rather than attempting a full migration from MS SQL Server.

Rationale

  1. Simplicity: Migrating databases across different database management systems (from MS SQL Server to MySQL) can be complex and error-prone due to differences in SQL dialects, data types, and indexing mechanisms.
  2. Clean Slate: Starting anew allows you to set up the database optimized for MySQL from the ground up, avoiding any legacy issues or misconfigurations carried over from the previous setup.
  3. Learning Opportunity: Setting up the database and configuring SailPoint IIQ will give you valuable hands-on experience, which is especially beneficial since you’re new to the platform.
  4. Minimal Data Loss: With only a few users and configurations, the amount of data to re-enter is manageable.

How to Proceed with Re-Creating the Schema and Reconfiguring SailPoint IIQ

Step 1: Prepare the AWS RDS MySQL Database

  • Create the Database Instance: Ensure your AWS RDS MySQL instance is up and running. Note the endpoint, port, username, and password.
  • Security Groups: Configure AWS security groups to allow connections from your SailPoint UI and Task machines.

Step 2: Install the MySQL JDBC Driver

  • Download the Driver: Obtain the MySQL Connector/J from the official MySQL website.
  • Place the Driver: Copy the JDBC driver JAR file into the WEB-INF/lib directory of your SailPoint IIQ installation. Since you’re using a Podman container, you’ll need to ensure the driver is included in the container’s classpath.

Step 3: Configure SailPoint IIQ to Use MySQL

  • Modify iiq.properties: Edit the iiq.properties file to point to your new MySQL database.
database.driver=com.mysql.cj.jdbc.Driver
database.url=jdbc:mysql://<RDS_ENDPOINT>:3306/<DATABASE_NAME>?useSSL=false&serverTimezone=UTC
database.username=<DB_USERNAME>
database.password=<DB_PASSWORD>
  • Adjust Connection Pool Settings: Ensure the connection pool settings are appropriate for MySQL.

Step 4: Initialize the Database Schema

  • Use SailPoint’s Scripts: SailPoint provides SQL scripts for database initialization, located in the WEB-INF/database directory.
  • Run the Scripts:
    • Create Tables: Execute create_identityiq_tables.mysql.
mysql -h <RDS_ENDPOINT> -P 3306 -u <DB_USERNAME> -p<DATABASE_NAME> < create_identityiq_tables.mysql

Step 5: Start SailPoint IIQ

  • Restart the Application: If it’s already running, restart your SailPoint IIQ application so it picks up the new database configuration.
  • Monitor Logs: Check the SailPoint logs to ensure there are no errors connecting to the MySQL database.

Step 6: Re-Populate Data

  • Manual Entry: Since the data volume is low, manually recreate the users and configurations via the SailPoint IIQ UI.
  • Import Data (Optional):
    • From Files: If you have exports or backups in XML or CSV format, you can use SailPoint’s import utilities.
    • Scripts and Workflows: Re-deploy any custom scripts or workflows you might have.

Step 7: Verify Functionality

  • Testing: Perform thorough testing to ensure all functionalities are working as expected with the new database.

Alternative: Full Migration (Not Recommended in This Case)

If you decide that a full migration is necessary, here’s an outline of how you might approach it:

  1. Export Data from MS SQL Server:
  • Use SQL Server Management Studio to generate scripts for both schema and data.
  1. Convert Scripts for MySQL:
  • Adjust data types and syntax to be compatible with MySQL.
  • Tools like SQLines or commercial solutions can assist with this process.
  1. Import into MySQL:
  • Run the converted scripts against your MySQL database.
  1. Update SailPoint Configuration:
  • Modify iiq.properties as described earlier.
  1. Test the Migration:
  • Start SailPoint IIQ and verify that all data is intact and functional.

Challenges with Full Migration:

  • Data Type Differences: MS SQL Server and MySQL have different data types, which can cause issues during migration.
  • Syntax Variations: SQL scripts may need significant adjustments to run correctly in MySQL.
  • Time-Consuming: The process can be lengthy and may not be justified given the small amount of data.

Additional Tips

  • Backup Everything: Before making changes, back up your current SailPoint IIQ configurations and any custom code.
  • Documentation: Refer to the SailPoint IdentityIQ Installation and Administration Guide for detailed instructions, especially regarding database configurations.
1 Like
4

Amit,

THANKS for all the info!!

I am in agreement with your suggested approach (actually, kind of relieved, because I wasn’t sure if that was even possible ;( ! ).

I will review in detail and post any other questions.

Thanks again!!

Jim

5

Amit,

  • For this part in your step 3:

" * Adjust Connection Pool Settings : Ensure the connection pool settings are appropriate for MySQL."

I wanted to confirm that those connection pool settings you mentioned are also in the Sailpoint config inside the container?

  • Also, I checked in the container, and it looks like there are 2 instances of the iiq.properties file:

/opt/tomcat/webapps/identityiq/WEB-INF/classes/iiq.properties
/run/parsableFiles/iiq.properties

Do you know which of those files need to be edited to to configure it for the MySQL?

Thanks,
Jim
Also

6

[CORRECTION - after I checked more closely, it looks like both are the same… in the /usr/share, one is a softlink of the actual jar file:

[root@ip-10-50-2-5 CONVERT-SAILPOINT]# ls -al /usr/share/java/
total 2536
drwxr-xr-x.   2 root root      67 Oct 11 03:17 .
drwxr-xr-x. 120 root root    4096 Oct  9 19:09 ..
lrwxrwxrwx.   1 root root      21 Jun  9 04:35 mysql-connector-java.jar -> mysql-connector-j.jar
-rw-r--r--.   1 root root 2590119 Jun  9 04:35 mysql-connector-j.jar

So the mysql-connector-j.jar is the actual/real file.

P.S. Amit or anyone, I’d still appreciate response to the questions I asked above/earlier? Thanks!

Hi - I started doing some of the preparation for the installations, and I downloaded the MySQL connector RPM and ran it, but when I checked the files that get installed, there are 2 different mysql-connector files:

  • /usr/share/java/mysql-connector-j.jar
  • /usr/share/java/mysql-connector-java.jar

So I was wondering which one of those JARs I should copy into the container?

I did a bit of searching, and found this:

and that seems to be saying that the “mysql-connector-j.jar” (the first one above) is the JAR file that I should be using.

Can anyone confirm that that is correct?

Please advise.

Thanks,
Jim

7

Amit (et al),

I found the Sailpoint installation document, and I am doing things slightly differently.

The UI machine already had the mysql client already installed (on the actual machine, outside the container) previously, so I pulled the “create_identityiq_tables-8.4.mysql” file from Podman container. Then, I ran the mysql client and connected to the RDS MySQL DB, and then I did “source… create_identityiq_tables-8.4.mysql;”, and it looks like it completed the schema:

Database changed
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| identityiq         |
| identityiqPlugin   |
| identityiqah       |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
7 rows in set (0.00 sec)

Also it looks like the Podman container on UI machine already has the MySQL Jar files in-place… so i think if I edit the conf file to comment out the MSSQL lines and uncomment the MySQL lines (and fix the parameters for my DB), maybe it will work?

I’ll start on that tomorrow/this morning (currently 03:00 here now :slight_smile: )

Thanks,
Jim

9

That’s excellent progress! It sounds like you’re on the right track with your approach.

Your approach is correct, and you should proceed

  • Modify Database Settings:

    • Comment Out MSSQL Lines: Place a # at the beginning of the MSSQL configuration lines.
    • Uncomment and Update MySQL Lines: Remove the # from the MySQL lines and update them with your AWS RDS MySQL database details.
# MS SQL Server Configuration (Commented Out)
#database.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
#database.url=jdbc:sqlserver://<MSSQL_SERVER>:1433;databaseName=<DATABASE_NAME>
#database.username=<DB_USERNAME>
#database.password=<DB_PASSWORD>

# MySQL Configuration
database.driver=com.mysql.cj.jdbc.Driver
database.url=jdbc:mysql://<RDS_ENDPOINT>:3306/identityiq?useSSL=false&serverTimezone=UTC
database.username=<DB_USERNAME>
database.password=<DB_PASSWORD>
10

Amit,

So, in order to test the modified configuration, I made the change to the iiq.properties file inside the Podman container, but every time I try to restart the container (podman restrart xxxxx), it is replacing the modified iiq.properties with the original iiq.properties.

I’ve even tried doing a “kill -hup ” inside the container, but the same thing happens (the iiq.properties gets changed back to original).

I think we may need to rebuild the container, but I need to wait for someone to do that.

Also, FYI, so you know, there were several sets of properties in the iiq.properties that had to be changed… there’s a set of properties for the identityiq database, and another for the haq one, and another for the plugins one.

I don’t think the person is going to be around today…

Jim

11

Actually the “Podman” person I mentioned did come in and he’s surprised that the file is being replaced.

So I’m starting to wonder if maybe it isn’t Podman that is doing the replacing, but maybe something in Sailpoint that is somehow replacing the file ever time?

Is that possible?

Thanks,
Jim

12

Also… as mentioned, what is happening is that the iiq.properties file keeps getting replaced with the version of the iiq.properties file that has MSSQL configured.

The weird thing is that the original MSSQL version of the iiq.properties file is LITERALLY NOT ON THE SYSTEM AT ALL, so I don’t even know HOW or from where the original file could be being copied in FROM :(…

Is there some other property file that has some property that says “Use MSSQL”?

Please advise.

Thanks,
Jim

13

Hej Jim,

how are you creating the containers? Are you using SSB / SSD for the deployment? It looks to me like when you start the container, podman uses your repository, in which it is specified that for that environment it should use the particular iiq.properties file.

At the same time I can guarantee, that in SailPoint it does not exist a flag, which would change the iiq.properties file and so on also not use MSSQL.

Br,
Renad

14

We are trying to figure out what is going on… , but the thing is, I have even tried testing where I am going into the running container and

  • Putting the modified iiq.properties on the filesystem, then
  • use “ps” command I can see the tomcat running, then
  • I do “kill -hup <tomcat_pid>”, and
  • when i look at the iiq.properties after that, it is back to the original MSSQL version.

NOTE that, in this scenario, the above is all being done inside the container, so it is normal Linux stuff.

Also, tailing the log, I see this:

Changing logging...
Replacing passwords in /opt/tomcat/webapps/identityiq/WEB-INF/database/create_identityiq_tables-*.sqlserver and /opt/tomcat/webapps/identityiq/WEB-INF/classes/iiq.properties and /opt/tomcat/conf/tomcat-users.xml
\n
Check if we need to execute SQL scripts...
Database exist!
Applying patch to the schema (if provided)...
Adjust iiq.properties to allow communication with the database '10.50.2.4'
Executing sql script: /opt/tomcat/webapps/identityiq/WEB-INF/database/upgrade_identityiq_tables-8.4p1.sqlserver
Changed database context to 'identityiq'.

Does any of the above logging look familiar?

Thanks,
Jim

15

In this case you would need to find out, the custom script in your system, as the above logging snippet is not being shipped from SailPoint.

It looks to me like you have a war file on tomcat, which tries to get auto deployed.
Br,
Renad

16

So I noticed that (I think) that it is deploying/installing a Sailpoint 8.4 update or something. Is it possible that that logging might be coming from the updating/patching processing?

FYI, this is what is under the Tomcat webapps dir:

root@sp1-t:/opt/tomcat/webapps# ls -al
total 88
drwx------. 1 identityiq identityiq 4096 Oct 11 11:04 .
drwx------. 1 identityiq identityiq   85 Oct 11 11:03 ..
drwx------. 1 identityiq identityiq   24 Sep  3 09:13 ROOT
drwx------. 1 identityiq identityiq  111 Sep  3 09:13 identityiq
drwx------. 1 identityiq identityiq  114 Sep  3 09:12 manager
-rw-r--r--. 1 root       root       2276 Sep  3 11:56 patch_8_4p1_090324_115623.log
.
.

The ROOT dir has a single HTML file in it… and I think the manager dir is the normal Tomcat manager web app.

It looks like the identityiq dir has the exploded files:

root@sp1-t:/opt/tomcat/webapps/identityiq# ls
META-INF            appPage.xhtml             css               exception.xhtml                   external
.
.
17

Yes exactly, based on the logging also, the running script is doing each time the patching to 8.4p1 (maybe also upgrade but can’t say it from the provided logs). It looks like the script is just adding modifying /opt/tomcat/webapps/identityiq/WEB-INF/database/upgrade_identityiq_tables-8.4p1.sqlserver and then executing the database script. I would assume that afterwards also the patch of the system is executed with ./iiq patch 8.4p1 but also this is an assumption as it is not the provided log.

Did you check if there is a .war file in the webapps folder of tomcat?

Br,
Renad

18

Ok, so it looks like it is not doing an auto deployment.

I would look into the /run folder, to check if you have there a script, which gets triggered on tomcat restart.

19

Renaud,

Sorry I didn’t answer about the WAR explicitly (I thought the info I provided implied the answer ) - no there is no WAR under the webapps or ROOT dir.

Where is the /run directory that you mentioned?

Jim

20

In this thread above you mentioned this file
/run/parsableFiles/iiq.properties, so i was wondering if in this directory there are some more scripts to be found.

21

Ok, thanks for pointing that out.

It DOES look like there is another iiq.properties file in the parsableFiles directory, but nothing executable (just the other iiq.properties file plus 2 XML files).

I need to check with the person that setup the Podman stuff to check on what is going on with that.

Thanks for your patience, and sorry I don’t have all the information on my own :(!!

Jim

Ahh! Ok I was poking around and I just found a directory with some shell scripts and it looks like one of the script is where that logging that I posted is coming from!!

I am not exactly sure what all that is about but it is specifically modifying some of the Tomcat conf files plus the iiq.properties, and has things that are specific for the MSSQL DB configuration (which is the current configuration).

I think whoever worked on that stuff is going to have to re-do a lot of that if they want to switch to using the MySQL DB instead of the MSSQL…

Thanks!!

Jim