Hi team,
We are upgrading the IIQ version to 8.4 version, we are encountering with below error.
I have verified IIQ properties file and default schema for identity schema both looks good. i have used Alter command as well
Alter user identityiq default_schema=’identityiq’
Can you please suggest on this issue
Hello Suresh,
Based on the error message, it appears that the database table was not updated before running the IIQ upgrade command. Could you please revisit this and ensure that the upgrade_identityiq_tables-* update query was executed?
Thanks,
Anirban
I have executed all upgrade script and saw the updated schema version in table
open the upgrade script, and login to the database, can you run the below query
“SELECT * FROM spt_database_version;” and it should returns the version, from the upgrade sql script, run the command to update the database version. Once done, run the upgrade script post that.
Please make sure all the database details in iiq.properties files are correct.
I can see the version details in the identityiq db and updates all details in iiq properties file
Run the database script first before running the iiq upgrade. Then the error will resolve.
Thanks,
Harikrishna
can you run the upgrade script one more time, open the file in notepad ++. Copy everything, and open your database and paste everything, Once done, run the queries, for already updated one, you will get details are already there, and then check your database version, It should be updated as per the identityiq version. Try it and let me know.
Please make sure the database details is iiq.properties files are correct
Thanks for the response, as i said that i ran all upgrade db scripts and kept identityiqah db details in iiq properties file, DB schema version upgraded sucessfully but the issue with invalid object spt_database_version
My question to you, was is the db details in iiq properties file correct??
if yes, ok. and if you are able to login to the database and able to query the spt_database_version table, that means table is there, so it should not return invalid object details.
is the version in select * from identityiq.spt_database_version is showing in align with the version mentioned in your upgrade database script??
Hi @sureshbomm ,
Please check the database user identityiq properties something is missing.
Look at the database properties.
Security – > Users – > right click on identityiq and “Access History database name” select properties → General
verify the User name, Login name and Default schema
or
Security – > Logins → right click on identityiq or “Access History database name” select properties – > User Mapping and Status
Verify the User and Default Schema
Hello @sureshbomm, from your prompt, what I observed is that the command you are using is slightly different. It might be that the default schema was not updated. Could you please try running the following command again?
ALTER USER identityiq WITH DEFAULT_SCHEMA = identityiq;
Hi @sureshbomm
That error is almost always a DB/schema resolution issue, not “didn’t run scripts.” IIQ runs a plain lookup on spt_database_version; on SQL Server that resolves through the login’s default schema.
Run this in the IdentityIQ DB to see where the table actually exists:
SELECT s.name AS schema_name
FROM sys.tables t
JOIN sys.schemas s ON s.schema_id=t.schema_id
WHERE t.name='spt_database_version';
If it’s under dbo (common), but your IIQ user default schema is identityiq, IIQ will look for identityiq.spt_database_version and fail. Fix is to align the login default schema to where the tables are:
ALTER USER [identityiq] WITH DEFAULT_SCHEMA = [dbo];
If you truly want schema identityiq, then the 8.4 upgrade DDL must be edited to use that schema (SailPoint notes upgrade scripts must be modified if DB/schema differs from default).
8.4 introduced access history which requires a new separate database whether you use it or not. Did you have that database created properly and did you update your IIQ.properties to point to it with the correct credentials?
Thank you everyone for your responses. I have resolved the issue by updating the iiq.properties file. Earlier, I was using an older version of the iiq.properties file that contained identity iq and IdentityIQ AH connection details. Later, I realized that some required configurations were missing. After updating to the latest iiq.properties file, which includes both connection details, the issue was resolved.
Correct, Iiq.properties file was the issue, That’s why I asked you check that file multiple times. Glad that you resolved it.