Environment
NetIQ Self Service Password Reset
SSPR 3.x
SSPR 3.x
Situation
How to confiugre SSPR to use Microsoft SQL database
Using MSSQL with SSPR
Using MSSQL with SSPR
Resolution
When using MSSQL as the SSPR database there are four settings to configure on the Settings, Database (Remote) page of the SSPR Configuration Editor namely, database class, connection string, database username, and database password. Configure these as follows:
Database Class - for SQL Server 2012 or SQL 2008 R2 the default driver class name is com.microsoft.sqlserver.jdbc.SQLServerDriver
Note that the jar or zip file for the driver com.microsoft.sqlserver.jdbc.SQLServerDriver needs to be copied into the SSPR web-inf\ lib directory, e.g. C:\Program Files\Apache Software Foundation\Tomcat 7.0\webapps\sspr\WEB-INF\lib
Database Connection String - Click the "?" for "show description for this setting" and copy the appropriate string from the examples shown. Paste into the box and modify as needed to include the MSSQL instance that SSPR will be using.
Database Username - this could be an AD user, a user that exists in MSSQL, or something else. MSSQL lets you configure this six or eight different ways. It might be necessary to check with the data base adminstrators to see how they have things configured. As far as SSPR is concerned, we really don't care where the db user exists, as long as a user is specified that can successfully access the MSSQL database. Note that this user must have "create" rights to the database in order for the tables and indexes to be created.
Database Password - click the button and the password for the MSSQL database user.
See screenshot below:
For more details, see "Setting Up Active Directory in the RDBMS Mode" in the online docs at:
https://www.netiq.com/documentation/sspr3/adminguide/data/b14gnfe6.html#b14gnof9
Database Class - for SQL Server 2012 or SQL 2008 R2 the default driver class name is com.microsoft.sqlserver.jdbc.SQLServerDriver
Note that the jar or zip file for the driver com.microsoft.sqlserver.jdbc.SQLServerDriver needs to be copied into the SSPR web-inf\ lib directory, e.g. C:\Program Files\Apache Software Foundation\Tomcat 7.0\webapps\sspr\WEB-INF\lib
Database Connection String - Click the "?" for "show description for this setting" and copy the appropriate string from the examples shown. Paste into the box and modify as needed to include the MSSQL instance that SSPR will be using.
Database Username - this could be an AD user, a user that exists in MSSQL, or something else. MSSQL lets you configure this six or eight different ways. It might be necessary to check with the data base adminstrators to see how they have things configured. As far as SSPR is concerned, we really don't care where the db user exists, as long as a user is specified that can successfully access the MSSQL database. Note that this user must have "create" rights to the database in order for the tables and indexes to be created.
Database Password - click the button and the password for the MSSQL database user.
See screenshot below:
For more details, see "Setting Up Active Directory in the RDBMS Mode" in the online docs at:
https://www.netiq.com/documentation/sspr3/adminguide/data/b14gnfe6.html#b14gnof9
Additional Information
SSPR has been tested and works with both the Microsoft JDBC driver and the JTDS JDBC driver. Make sure the correct driver is used for the version of java and MS SQL. For example, with jre7 (installed by SSPR 3.2), the Microsoft JDBC driver to use is sqljdbc41.jar from "Microsoft JDBC Driver 4.1 for SQL Server."
Also, there is no need to manually create the MS SQL database for SSPR. Each time SSPR loads it verifies that the database specified in the Database Connection String exists and is properly configured. Any necessary adjustments to the database will be made by SSPR without manual intervention. The database will be given the name specified in the "Database Connection String." For example the database connection string shown in the screen shot above will result in a database called "SELFSERVICEDB."
Also, there is no need to manually create the MS SQL database for SSPR. Each time SSPR loads it verifies that the database specified in the Database Connection String exists and is properly configured. Any necessary adjustments to the database will be made by SSPR without manual intervention. The database will be given the name specified in the "Database Connection String." For example the database connection string shown in the screen shot above will result in a database called "SELFSERVICEDB."