Changing DB table authentication in ALM/PC from the existing system to the upgraded system.

  • Questions
  • Changing DB table authentication in ALM/PC from the existing system to the upgraded system.
Question ID: 106467

We are upgrading our ALM/PC 11.52 system that uses Windows authentication to connect to the Database, and have changed the ALM 12.20 to use SQL authentication. When we try to upgrade a Site Admin schema, the upgrade process fails.

Marked as spam
Posted by (Questions: 144, Answers: 1)
Asked on October 27, 2015 1:27 pm
Answers (1)
Private answer

ALM can connect to Microsoft SQL server by using SQL authentication or Windows authentication. For each of these methods, a different user owns the tables of a project:

- SQL Authentication. Table owner is
the user td.

- Windows Authentication: Table owner
is the user dbo (a user mapped to the
operating system user that runs the
ALM server).

If you create a project with one type of authentication (for example, SQL), and then restore it with the other type of authentication (for example, Windows), these tables cannot be accessed. In this case, new tables are created with owners that are different from those of the old tables. You will not be able to work with the project. It is likely that the upgrade will fail.

To prevent this problem, the duplicate ownership validator checks that the owner of all of the tables in the project database user schema matches the connection method that ALM is using to connect to the server.
In the Database you must switch the authentication to access the DB.

To fix table ownership manually, do one of the following:

SQL Authentication: Run the following query to make td the table owner:

EXEC sp_changeobjectowner '', 'td'

Windows Authentication: Run the following query to make dbo the table owner:

EXEC sp_changeobjectowner 'td.', 'dbo'

Re-run the configuration wizard

On the Database opens, change the authentication.

On the next page Site Administration Database schema select Connect to existing schema / second node. If you are reinstalling ALM, and would like to reconnect to the same Site Administration database schema, please refer to the following.

Once the ALM server is reconfigured, create a test project to get a valid Dbid.xml file to use to compare. If the Dbid.xml needs to be altered take the following steps:

1. Deactivate the project.
2. Ask your database administrator to rename the database user schema to a name that does not
include special characters, or periods for SQL databases.
3. Remove the project from Site Administration.
4. Update the Dbid.xml file to point to the new database user schema name. (DB_USER_PASS and replace on the projects dbid.xml (Third arrow))
5. Restore the project by using the updated Dbid.xml file.
6. Run the verification process again to make sure the problem is resolved.

Marked as spam
Posted by (Questions: 0, Answers: 152)
Answered on October 27, 2015 1:38 pm
Hi @bsmith Present I am working on this only I changed table authentication from dbo to td but facing ''Schema differences were found '' issue in upgrade process then i started searching on this issue ,I got struck at one point i.e on the restored database i changed only table names from dbo to td but here we have ''views'' also, still views are listed dbo.V_LINK_CYCLE... like this we have 7 views. I think we have to change from dbo.V_LINK_CYCLE to td.V_LINK_CYCLE . If you have any idea on this please share with me. Thank you, Praveen
( at October 6, 2016 4:45 am)