SSO database migrate

[tsd_david]

Dear all, I had migrate the SQL from server-a to server-b by detach database in MS SQL Management Studio then attach to the new SQL Server.

Then I follow the KB http://kb.vmware.com/kb/2034928 to change the database-hold from old to new.

Meanwhile i use ssocli to change the database and it report:

C:\Program Files\VMware\Infrastructure\SSOServer\utils>ssocli configure-riat -a

configure-db --database-host icondemo-vc4.icon-demo.local --database-port 1433 -

m P@ssw0rd

ERROR: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get

JDBC Connection; nested exception is java.sql.SQLException: Login failed for use

r 'rsa_user'.

Then I use sql script to drop and create user. it now display:

C:\Program Files\VMware\Infrastructure\SSOServer\utils>ssocli configure-riat -a

configure-db --database-host icondemo-vc4 -m P@ssw0rd

ERROR: org.springframework.dao.PermissionDeniedDataAccessException: StatementCal

lback; SQL [SELECT COUNT(*) FROM IMS_CONFIG_VALUE]; The SELECT permission was de

nied on the object 'IMS_CONFIG_VALUE', database 'RSA', schema 'dbo'.; nested exc

eption is java.sql.SQLException: The SELECT permission was denied on the object

'IMS_CONFIG_VALUE', database 'RSA', schema 'dbo'.

Then I use create schema script to run it return as error (fail to create), service is up but with error in *the*attachment* and SSO Administrator web page still cannot load.

[rcporto · Accepted solution]

If you only detach and attach the database of SSO, you lose the users, cause they are defined in master database, not in SSO database.

To fix it, go to the new SQL Server, and using SQL Management Studio, create the logins of SSO database and map to SSO database.

Here are some help about detach/attach SQL Server database:

Understanding and dealing with orphaned users in a SQL Server database

http://www.serverintellect.com/support/sqlserver/sql-database-attach/

[Ethan44]

Hi

Welcome to communities.

you meet all prerequisites before installing .

this occurs when the SSO database RSA (default SSO DB name) does not meet the prerequisites for the SSO installation and the RSA DB table

[tsd_david]

Dear all, thanks for your information. May I explain my scenario again

I have SQL 2008 R2 originally named "sql1" then now detached RSA database and copy those three file to my new SQL server call "vc4".

After attach the database, I cannot create RSA_DBA nor RSA_USER since those user already exist in RSA database.

Then I run the vCenter SSO bundle script "rsaIMSLiteMSSQLDropUsers.sql" and run SetupUsers again.

Then I seem got the Permission denied. I ran rasIMSLiteMSSQLDropScheme.sql and run CreateScheme it still can't start! please kindly to help, thank you!

original sql1

[rcporto]

Remove the users from RSA database and recreate the logins making the mapping to RSA database.

[tsd_david]

I found the script to show unlinked account, create it then link it.

sp_change_users_login report

sp_change_users_login ‘update_one’, ‘RSA_USER’, ‘RSA_USER’