Symantec IGA

Hi,

I am running a CA IDM 12.6 sp2 set-up (integrated with CA Siteminder 12.51) on JBoss 5.1 GA AS. I am using MS SQL Server 2008 as the DB. Unfortunately, my SQL server installation went corrupt and after troubleshooting it for a while, it appears that the only way to get it back up is to uninstall and re-install. I have been searching in the CA IDM resources to find any suitable resource to deal with this situation. However, I have not been able to find anything that could help me understand if I could reinstall MS SQL Server without having to re-install CA IDM. I am still a learner when it comes to these technologies. Could anyone please look into this one and at least point me to some resource to help me get started?

Many thanks for reading this post-

I first started using Microsoft SQL Server in 1997 with version 6.5.  I have a ton of experience with it, including my own "uh-oh" moments, which means I also have experience in recovering from "uh-oh" moments.  I began using CA Identity Manager in 2011 with 12.5 SP9 and now use 12.6 SP2.  Your statement that your SQL Server install "went corrupt" means there are some caveats to my response, namely:

• do you have an up-to-date (or at least reasonably recent) backup?
• have you determined the root cause of the corruption in order to prevent it from reoccurring?
• have you already performed the reinstall?  This is rarely needed unless the corruption was to the executable program files, and a reinstall will actually make your recovery longer and more difficult.
• are there any special issues to consider in restoring your database? (e.g., restoration of only some data, out-of-band changes to user data)

Any of these (and probably others that I haven't thought of yet) could significantly change what actions you need to take to get back in operation.  I am assuming that your user store is in CA Directory or some other LDAP directory, but even if your user store is part of your SQL Server install that part should not radically change what you need to do.  You aren't finding information on this in the CA documentation because this is outside the scope of Identity Manager.  In this situation, the Microsoft SQL Server Books Online is your friend.  I can also recommend online resources like the SQL Server / Windows Users Group (SSWUG) - http://www.sswug.org/.

I recommend these steps to recover your system:

1. Shut down your IME / JBoss server(s).  You don't want the user console trying to hit the database until you're completely done with the restore.
2. Ensure that the SQL Server is in a stable state and ready to accept data.  If you already performed the reinstall, this would mean that the master, model, and msdb databases are up, running, and available.  Any other databases on the server should be in a "normal" state - i.e., online, read-write, multi-user access.
3. Using the data backups, restore your CA IM databases.  This could be one database or up to eight, depending on how you configured your CA IM install:
   • Archive
   • Audit
   • Java Message Store
   • Object Store
   • Reports
   • Transaction Persistence
   • User Store (if using RDBMS configuration)
   • Workpoint / workflow
4. If you did not reinstall your SQL Server software, you're pretty much done at this point.  You should go through and spot check your data to see that it is there and at least somewhat sane.  This isn't the easiest job, since a lot of the data isn't human-friendly, but if it doesn't look like complete garbage, it's probably okay.
5. If you did a full reinstall of SQL Server, you have something else to fix.  SQL Server has two levels of logon accounts: server level and database level.  Your restoration of the databases took care of the database-level user accounts, but not the server-level accounts.  Additionally, the name of the user account isn't the important part, it's the UUID (universally unique identifier), also known as a SID (system identifier).  For a user account to have access to the data in a database, it has to exist at the server level, exist at the database level with the same UUID as the server level, pass authentication, have server-level logon authority, and have database-level authority to at least logon and execute queries and / or stored procedures.  Your reinstall of SQL Server means that even if you recreate the server-level accounts, they will have a mismatched UUID from the accounts in the databases.
   1. sp_addlogin will add a SQL Server logon to the server (at the server level).  You can use the "sid" parameter to this stored procedure to specify the UUID of the new logon account so it will match the UUID in the database.
   2. sp_grantlogin will add a Windows integrated logon.
   3. sp_addsrvrolemember will add a logon account to a server role, where rolename is one of:
      • sysadmin
      • securityadmin
      • serveradmin
      • setupadmin
      • processadmin
      • diskadmin
      • dbcreator
      • bulkadmin
   4. sp_addrolemember will add a logon account to a database role
   5. sp_grantdbaccess will provide a logon account with access to a database.  It must be run in the context of the database to provide access.
6. At this point, you should be able to restart your JBoss / IME server(s).  If you have more than one, you should start with only one server.  As it starts, monitor the server log.  The app will attempt to connect to SQL Server and if it does, it will verify the database schema before proceeding.  If everything starts without error, you should be good to go.  Handle errors as you would normally, with standard troubleshooting procedures and without assuming it is a database problem (unless the error messages point in that direction).

Good luck!

What Eric said

If this is a non-production system, and you wish to quickly rebuild the tables for IM, you may drop the database login/app ID with the associated tables, then recreate the database login/app ID

IM, when it restarts, will check and rebuild or update the database schema for the six (6) IM databases.

Example for Oracle (used in SQL statement or paste in SQL command line window), to start the solution's DB in a "clean state".

(Below example uses a db login ID  idmdba001  that will own a database  called  "idmdba001"  

drop user idmdba001 cascade;

create user idmdba001 identified by Password01;

grant connect, resource to idmdba001;

grant create table, create view, create session, create trigger, create sequence to idmdba001;

grant create tablespace, drop tablespace, manage tablespace, unlimited tablespace to idmdba001;

Cheers,