How to change ALM on MSSQL from Windows Authentication to SQL Authentication

0 Likes

It might be desirable to change MSSQL from Windows Authentication to SQL Authentication

1) On the MSSQL server, create the user "td" in MSSQL using the following script...

USE [master]
GO
CREATE LOGIN [td] WITH PASSWORD=N'tdtdtd', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

ALTER LOGIN [td] ENABLE
GO

Note: the  password "tdtdtd" is  used in the above example. Specify the desired password. Remember this password as it will be used later

2) Add an internal 'admin' user to MSSQL by issuing the following query...

USE [master]
GO
CREATE LOGIN [alm_admin] WITH PASSWORD=N'rootroot', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER LOGIN [alm_admin] ENABLE
GO
ALTER SERVER ROLE [dbcreator] ADD MEMBER [alm_admin]
ALTER SERVER ROLE [securityadmin] ADD MEMBER [alm_admin]
GO

Note: In the above example the user name is "alm_admin" and the password is "rootroot"
Specify different values as desired.
Remember both the username and password specified

3) Login to the Site Administration
4) Click the DB Servers tab
5) Click "New" to create a new database server
6) In the "Database Type" specify "MS-SQL (SQL Auth.)"
7) In the "Database Name" specify a unique name. Remember this name as we use it in the query used in Step 21
8) Specify the exact same database server host and port as is used for Windows Authentication
9) Specify the "DB Admin User" and "DB Admin Password" as was specified in the query in Step 2
10) Click "Ping" the ping should complete successfully
11) Click OK to complete the add
12) In the main view click the "Application lifecycle Management User Password" item
13) A new dialog will open. Specify the password in both space for the user "td" as was used in Step 1. Be sure to type this accureately. Click OK when complete
14) Create a new blank project and specify the database create in Steps 5 thru 11. Note the Domain and Project name as we'll use this in Step 21
15) To change object ownership from "dbo" (Win Auth) to "td" (SQL Auth) issue the following queries the Site Admin databse, lab project database, and all project databases...

--Query A - Drop user and schema td

DROP SCHEMA [td]
GO
DROP USER [td]
GO

--Query B - Run this FIRST (establish td schema)

CREATE USER [td] FOR LOGIN [td] WITH DEFAULT_SCHEMA=[td]
GO
ALTER ROLE [db_ddladmin] ADD MEMBER [td]
GO

IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'td'))
BEGIN
    EXEC ('CREATE SCHEMA [td] AUTHORIZATION [dbo]')
END

--Query C - Run this SECOND (Tables)

DECLARE
    @old SYSNAME = N'dbo',
    @new SYSNAME = N'td',
    @sql NVARCHAR(MAX) = N'';
SELECT @sql += CHAR(13) + CHAR(10) + 'ALTER SCHEMA ' + @new 
    + ' TRANSFER ' + QUOTENAME(SCHEMA_NAME([schema_id]))
    + '.' + QUOTENAME(name) + ';'
    FROM sys.tables AS t
    WHERE SCHEMA_NAME([schema_id]) = @old
    AND NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = t.name
    AND SCHEMA_NAME([schema_id]) = @new);
EXEC sp_executesql @sql;
GO
SELECT SCHEMA_NAME([schema_id]),name FROM sys.tables;

--Query D - Run this THIRD (Views)

DECLARE
    @old SYSNAME = N'dbo',
    @new SYSNAME = N'td',
    @sql NVARCHAR(MAX) = N'';
SELECT @sql += CHAR(13) + CHAR(10) + 'ALTER SCHEMA ' + @new 
    + ' TRANSFER ' + QUOTENAME(SCHEMA_NAME([schema_id]))
    + '.' + QUOTENAME(name) + ';'
    FROM sys.views AS t
    WHERE SCHEMA_NAME([schema_id]) = @old
    AND NOT EXISTS (SELECT 1 FROM sys.views WHERE name = t.name
    AND SCHEMA_NAME([schema_id]) = @new);
EXEC sp_executesql @sql;
GO
SELECT SCHEMA_NAME([schema_id]),name FROM sys.views;

16) Open the "dbid.xml" file for the project create in Step 14
17) Open the "siteadmin.xml" on the ALM host for editing, typically located in the path: <drive>\ProgramData\Micro Focus\ALM\webapps\qcbin\WEB-INF
18) Locate the following element in the "siteadmin.xml" file...

<NativeAuthentication>Y</NativeAuthentication>

19) Change the element value 'Y' to 'N', for example...

<NativeAuthentication>N</NativeAuthentication>

20) Locate the following element in the "siteadmin.xml" file...

<DefaultUserPassword>QCC:g0FvB9ehM/rsASDFllj==</DefaultUserPassword>

Note the value within the tags will be different in your enviornment. In the above example the value "QCC:z0Z5aNvQ9ehN/rsRNPDlmg==" is what we will be replacing

21) In the project "dbid.xml" opened in Step 16, note the following element...

<DB_USER_PASS>QCC:z0Z5aNvQ9ehN/rsRNPDlmg==</DB_USER_PASS>

Note the value within the tags will be different in your environment. Note this value as it will be used in the next Step 22 and Step 26

22) Copy the hashed value within the tags in the "dbid.xml" file in Step 21 into the "siteadmin.xml" file in step 20.
Both values are hashed. What we're doing is taking the hashed password from the dbid.xml file and pasting it into the  siteadmin.xml file.
Once complete the element in the "siteadmin.xml" file will change...
From:

<DefaultUserPassword>QCC:g0FvB9ehM/rsASDFllj==</DefaultUserPassword>

To: 

<DefaultUserPassword>QCC:z0Z5aNvQ9ehN/rsRNPDlmg==</DefaultUserPassword>

Simply put. We're taking the password from the newly created project's dbid.xml and pasting it into the siteadmin.xml. We're simply changing the hashed password value
23) Save Changes
24) While still in the "siteadmin.xml" file note the value in the elment: <DbName>qcsiteadmin_db</DbName>
    Note In the example the site admin database name is: qcsiteadmin_db
25) Perform a full backup of the site admin database - noted in the previous step 18
26) Using the database name specifed in Step 9, issue the following query to the site admin database...

update td.projects set DB_NATIVE_AUTHENTICATION = 'N', DBSERVER_NAME = '<value_from_step_7>', DB_USER_PASS = '<value_from_step_21>

For example...

update td.projects set DB_NATIVE_AUTHENTICATION = 'N', DBSERVER_NAME = 'tm-alm16_sqlauth', DB_USER_PASS = 'QCC:z0Z5aNvQ9ehN/rsRNPDlmg=='

27) Start ALM - ALM should start and now be on the database using SQL Authentication

If ALM fails to start examine the 'wrapper.log' to understand what went wrong
It might be necessary to open a ticket with support to resolve encountered issue(s)

Labels:

How To-Best Practice
Comment List
Anonymous
Related Discussions
Recommended