How can I get Secure Password Administrator to connect to a SQL 2005 Database? (NETIQKB71187)

  • 7771187
  • 06-Aug-2008
  • 05-Oct-2011

Environment

Secure Password Administrator 1.0

Situation

How can I get Secure Password Administrator to connect to a SQL 2005 or 2005 Express Database?

How do I install SPA with SQL 2005?

Database is created in SQL 2005 but can not connect to Admin site.

Page can not be displayed when launching SPA with a SQL 2005 database.

SPAService account is not created when installing SPA on SQL 2005.

No tables or stored procedures are created when installing SPA on SQL 2005.
Error: Secure Password Administrator could not connect to the SPA database. Ensure the database server is not stopped or paused and that the database login SPAService has access to the SPA database instance and all of its tables and stored procedures. When the database connection error is corrected, restart the NetIQ Secure Password Administrator service.

Resolution

You can configure an existing SPA installation to point to use a SQL 2005 database by following these steps:

  1. Locate the pool.properties file, the path is install drive/Program Files/NetIq/SPA/Config
  2. Make a back up copy of the pool.properties file.
  3. Open the pool. properties file with notepad.
  4. Locate the line that says "vigilent.url=jdbc:inetdae:KPGDC:1433?sql7=true".
  5. Modify this line by changing it to read "vigilent.url=jdbc:inetdae7:localhost:1433".
  6. Remove the end of the line that contains "?sql7=true".
  7. Save the file.
  8. Restart the SPA service.

To configure a new SPA installation to use SQL 2005, perform the following steps:

  1. You must first Install SPA with either MSDE or SQL 2000 mixed authentication mode with TCP/IP and named pipes enabled.
  2. The SPA installation will offer to install MSDE locally if that is the preferred option.
  3. (Your SQL 2005 server also needs to be in mixed authentication mode with TCP/IP and named pipes enabled.)
  4. Install SPA, pointing to the SQL 2000 or MSDE server.
  5. Open the SPA Database and verify that tables were created.
  6. Stop the SQL Service on the current repository.
  7. Copy the SPA,mdf and SPA_log.mdf files from the current repository to the data file location for the SQL 2005 server.
  8. Attach the SPA database using the SQL Server Management Studio.
  9. On the SQL 2005 Server, create a new login called SPAService associated with the SPA Database.    Make sure it meets the complexity requirements you have for passwords on the SQL 2005 server.  It is recommended that it be set to not expire.
  10. On the SPA Server, make a backup copy of the file ...\Program Files\NetIQ\SPA\config\pool.properties.
  11. Edit the original file above to modify this line:
      • vigilent.url=jdbc:inetdae:SQLSERVER:1433?sql7=true
      • Where SQLSERVER is the name of the original repository
      • To
      • vigilent.url=jdbc:inetdae7:SQLSERVER:1433
      • (Removed ?sql7=true and changed inetdae to inetdae7)
      • Where SQLSERVER is the name of the SQL 2005 Repository
  12. Restart the NetIQ Secure Password Administration Service.
  13. On the SPA server, in .. \Program Files\NetIQ\SPA\bin, run the file DatabaseCredentials.bat
  14. Identify the SPA Service Account and the password you created.
  15. Restart the NetIQ Secure Password Administration Service.

 

Cause

Secure Password Administrator does not currently support installing directly with SQL 2005 and will not connect to a SQL 2005 database without modifications.

Additional Information

Formerly known as NETIQKB71187

While SPA has not been fully regression tested to work with SQL 2005, using either of the above methods to point SPA to, or install it with SQL 2005 have been validated in a number of customer environments.