Wikis - Page

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

PhilTrostel

0 Likes

over 2 years ago

Summary

It might be desirable to change MSSQL from SQL Authentication to Windows Authentication Note: MSSQL Windows Authentication is only possible when ALM is installed on a Windows platform OS. It is not possible if the ALM server is installed on Linux

1) Add a Windows AD user to MSSQL
2) Assign the following roles...
   Option A:
       Assign roles "sysadmin" and "public"
   Option B - use if "sysadmin" role can't be granted:
       Assign roles "dbcreator", "public", and "securityadmin"
3) If not already defined change the service account for the "Micro Focus Application Lifecycle Management" service to the same user specified in Step 1
4) Restart the ALM service
5) Login to the Site Administration
6) Click the DB Servers tab
7) Click "New" to create a new database server
8) In the "Database Type" specify "MS-SQL (Win Auth.)"
9) In the "Database Name" specify a unique name. Remember this name as we use it in the query used in Step 20
8) Specify the exact same database server host and port as is used for SQL Authentication
9) Click "Ping" the ping should complete successfully
10) Click OK to complete the add
11) Create a new blank project and specify the database create in Steps 8 thru 10. Note the Domain and Project name as we'll use this later
12) Stop ALM
13) To change object ownership from "td" (SQL Auth) to "dbo" (Win Auth) issue the following queries on the site admin database, the lab project database, and all project databases...

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

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

--Query B - Run this SECOND (Tables)

DECLARE
    @old SYSNAME = N'td',
    @new SYSNAME = N'dbo',
    @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 C - Run this THIRD (Views)

DECLARE
    @old SYSNAME = N'td',
    @new SYSNAME = N'dbo',
    @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;

14) Open the "siteadmin.xml" on the ALM host for editing, typically located in the path: <drive>\ProgramData\Micro Focus\ALM\webapps\qcbin\WEB-INF
15) Locate the following element...

<NativeAuthentication>N</NativeAuthentication>

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

<NativeAuthentication>Y</NativeAuthentication>

17) Save Changes
18) 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
19) Perform a full backup of the site admin database - noted in the previous step 18
20) Using the database name specifed in Step 9, issue the following query to the site admin database...

update td.projects set DB_NATIVE_AUTHENTICATION = 'Y', DBSERVER_NAME = '<value_from_step_9>'

For example...

update td.projects set DB_NATIVE_AUTHENTICATION = 'Y', DBSERVER_NAME = 'tm-alm16_winauth'

21) Start ALM

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

Recommended

Menu

ALM/QC Tips & Information

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

Labels:

Resources