How to configure IdentityIQ to connect to Microsoft SQL Server using Windows Authentication versus SQL Auth

[#1]

JTDS Download Location:

http://sourceforge.net/projects/jtds/files/

JTDS FAQ (Instructions on configuring JTDS JDBC driver):

http://jtds.sourceforge.net/faq.html

A. Configure 3rd party application to use JTDS driver with Windows auth.

1) Install 3rd party database browser, such as dbVisualizer.

2) Download latest JTDS driver.

3) Install driver and configure dbVisualizer to utilize said driver.

4) Setup a new connection to your SQL Server database using the JTDS driver. The "Database URL" should look similar to this for proper windows authentication. You may need to change host, port, database name, domain, user and password values per your database environment:

jdbc:jtds:sqlserver://localhost:1433;DatabaseName=identityiq;domain=SUPPORTVM;user=jdoe;password=xyz123

5) Click the "Connect" button. Connection should be successful, assuming proper configuration.

6) Proceed to configure JTDS to work with identityIQ below.

B. Configure identityIQ to use JTDS driver with Windows auth.

IIQ 5.1p6

SQL Server 2008

Windows 2008 R2 SP1

Tomcat 6.0

1) Shutdown application server.

2) Download latest JTDS driver.

3) Remove existing driver:

identityIQ/WEB-INF/lib/sqljdbc-1.2

4) Attempt to run "iiq console". Should see error about missing driver.

5) Install downloaded JTDS driver into proper location:

identityIQ/WEB-INF/lib/jtds-1.2.5.jar

6) Modify identityIQ/WEB-INF/classes/iiq.properties per JTDS FAQ instructions. Here is my example:

dataSource.url=jdbc:jtds:sqlserver://localhost1433;DatabaseName=identityiq;domain=SUPPORTVM;user=jdoe;password=xyz123

dataSource.driverClassName=net.sourceforge.jtds.jdbc.Driver

7) Attempt to run "iiq console" Should see a similar error:

C:\Sailpoint\identityiq\WEB-INF\bin>iiq console

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'versionChecker' defined in class path resource [configBeans.xml]: Initialization of bean failed; nested exception is java.lang.RuntimeException: Unable to check IdentityIQ database version: Invalid object name 'spt_database_version'.

Previous error is due to the fact that my "jdoe" login does not own the database or the schema associated with the identityiq database tables. The database was originally created to be owned by a user named "identityiq" with a schema of "identityiq". Your database administrator will need to handle corrections here in the SQL Server database. Once that is done, you should be authenticating via Windows Authentication Mode.

9) After proper database configuration, "iiq console" makes proper connection.

10) Startup application server.

11) Login to IdentityIQ.

[joseph_leal]

I am trying to use a windows domain account to connect to a SQL server database (identityiq), will this approach work for IIQ 6.2.p1, Tomcat 7, Java 7, SQL server 2012 , Windows 2008 r2

Thanks in advance.

[#3]

I have a very similar configuration as Joseph Leal, IIQ 6.2, Tomcat 7, Java 7, SQL Server 2008, Windows 2008 r2.  I just modified the iiq.properties file and used the "iiq console" to encrypt the password.

[muzammil_k]

Hi,
Thanks for such a detailed post.

I know it's been some time since this post was made. I followed through your steps and at Step 7 "iiq console" i get a similar error, but instead of getting "Invalid object name 'spt_database_version'." ... I'm getting "Invocation of init method failed; nested exception is java.lang.AbstractMethodError" .

Any pointers on what the issue might be?

Full error:
iiq console
Setting iiq.hostname to WIN-XXXXXX-console
2022-03-01T07:30:17,632 WARN main springframework.context.support.ClassPathXmlApplicationContext:557 - Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'versionChecker' defined in class path resource [configBeans.xml]: Invocation of init method failed; nested exception is java.lang.AbstractMethodError
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'versionChecker' defined in class path resource [configBeans.xml]: Invocation of init method failed; nested exception is java.lang.AbstractMethodError

[Gilly]

We accomplished windows auth to the IIQ DB in a different manner than outlined here.  We still use the JTDS JDBC driver but our connection string in IIQ.properties is as follows:

dataSource.url=jdbc:sqlserver://[FQDN of DB Server];[db Name],integratedSecurity=true

Then on each server running Tomcat we have the Tomcat service running under the credentials of the windows account that has been granted access to the IIQ DB.

We set this up 3+ years ago, I do recall lots of trial and errors but eventually we were able to get it working with the above.  Hope that helps.

[aniksngh]

@muzammil_k I am facing similar issue. You found any fix for this?

[muzammil_k]

Hi @aniksngh ,

Are you using Windows Application servers for deployment?
 
If yes, I would suggest not to use the jTDS drivers instead Microsoft’s JDBC drivers does support native/windows authentication. jTDS drivers are not great towards SSL communication (I believe they didn’t release any new version since long) while Windows JDBC has great improvements recently.
 
Anyhow, to use it following steps are required.

- Use the latest (relevant to MS SQL server version) Microsoft JDBC driver in lib folder
https://docs.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view...
 
- setup your iiq.properties as following (example)
 
//comment out the username and password parameters as they  are not required.
#dataSource.username=
#dataSource.password=
 
dataSource.url=jdbc:sqlserver://;servername=seri.sailpointdemo.com;domainname=SERI;integratedSecurity=true;
dataSource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
 
- Now open command prompt with “run as another user” and use your DB Account (AD) with password
- Go to WEB-IN/bin and open iiq console
- If it works to connect with DB, it means you are all set to use Windows Native Authentication
 
More detail is here in this Microsoft’s Article.
https://docs.microsoft.com/en-us/sql/connect/jdbc/building-the-connection-url?redirectedfrom=MSDN&vi...
 
After this (if above works), to use the Windows Native Authentication with Tomcat.
- From Windows Services, open “Appache Tomcat9-SailPoint” service.
- Open “Log On” tab and enter user/password for DB Service Account.
- Start the server

This should help you.