How to enable Resource Central (RC) to use SQL Windows Authentication on the SQL server

Foreword

By default, RC uses SQL Authentication on SQL Server. From RC 4.2 and higher versions, Windows Authentication is supported, and you can follow the instructions in this article to implement the configuration in your system.

Note: Windows Authentication is supported in:

        - RC 4.2 and higher

        - SQL Server 2016 and higher

        - Windows Server 2016 and higher

Setup and configuration

Pre-requisites:

• Resource Central (RC), RealTime Service (RTS) and SQL server must be on the same domain or on “Trusted” domains.
• Before following the instruction in this article, it is required that RC 4.2 or higher version is installed.
• The database is assumed not to be created.
• This is only applied to On-Premise setup.

The guide is presenting a number of consecutive steps. They have to be done after each other.

The guide uses an account “AOC\rcadmin” as a throughout example.

Overall we have these sections:

• Setup a domain account (AOC\rcadmin)
• Use rcadmin account to configure RC to use Windows Authentication

Setup a domain account

Note: To use SQL Windows Authentication, you should log in to the RC server with the Windows account you are using to connect to SQL server. If you log in to the RC server with a different Windows account than the one used for SQL server, you must firstly upgrade database by using RC configuration tool.

Create and setup a domain account:

1. Create a new domain user (or use an existing account)

      E.g. create user “rcadmin” in the domain AOC

2. The newly created user must be in the Users group of the same domain with machine where RC is installed.

3. The domain of account rcadmin must be “Trusted” by the domain of the database server.

Note: This is only applied when web server and database server are on 2 different domains.

Grant permissions to the “rcadmin” account 

4. Grant the user a login privilege and other appropriate permissions (in SQL server)

• To create a login privilege for the rcadmin account in SQL Server and make it sysadmin, we can execute the statement below:

CREATE LOGIN [AOC\rcadmin] FROM WINDOWS  WITH DEFAULT_LANGUAGE = [us_english] GO EXEC sp_addsrvrolemember 'AOC\rcadmin', 'sysadmin';

• Statement to verify the newly created login:

SELECT * FROM sys.server_principals WHERE name LIKE 'AOC\rcadmin'

5. Grant the user permissions (in the machine where RC is installed)

a. Grant “GPO: Logon as a batch job” and “GPO: Logon as a service” permissions to the rcadmin account.

(This is needed because rcadmin account is used for services of RC, with this set of permissions, the proper operations of services are ensured.)

b. It is required that rcadmin has Local Administrator rights.

Use rcadmin account to configure RC to use Windows Authentication

Now upgrade to Resource Central 4.2 or higher version:

6. Run the Resource Central standard installer, which does following:

a. Files (including configuration files) are copied to the server.

b. Resource Central Synchronize Service is started.

c. RC app pool is configured.

d. Resource Central Digital Signage Export is started.

Note: Resource Central Digital Signage Export is an optional component, if it is not installed (when installing the base version) or needed, it is not required to be changed.

e. Resource Central Maintenance Service is started.

f. Resource Central Permission Service is started.

g. Resource Central Tracking Service is started.

h. Database is not created.

i. A shortcut of RC configuration tool is created in the Desktop. 

Now use RC configuration tool to create database and setup services to use Windows Authentication:

7. Use RC configuration tool to configure Resource Central Digital Signage Export, Resource Central Synchronize Service, Resource Central Maintenance Service, Resource Central Permission Service, and Resource Central Tracking Service to use Windows Authentication with the “rcadmin” user, and to configure app pool in IIS.

a. Run RC configuration tool as Local Administrator

b. Put the cursor in the [Enter a server name] textbox:

c. Press the combination Ctrl + Alt + Shift + W, the Authentication mode dropdown list shows up:

d. Select “Windows Authentication” from the list and fill in necessary details.

e. Click [Create Database] button.

f. When the database is successfully created, use the following statement to add permission to enable domain account “AOC\rcadmin” to read/write to Resource Central (RC) database “RC”

Note: This is only applied for the existing database.

USE [RC] GO CREATE USER [AOC\rcadmin] FOR LOGIN [AOC\rcadmin] GO ALTER USER [AOC\rcadmin] WITH DEFAULT_SCHEMA=[dbo] GO EXEC sp_addrolemember N'db_owner', N'AOC\rcadmin' GO

Check if the configuration is correctly done

1. Check that the app pool is correctly created:

2. Check that Resource Central Digital Signage Export is correctly configured:

3. Check that Resource Central Synchronize Service is correctly configured:

4. Check that Resource Central Maintenance Service is correctly configured:

5. Check that Resource Central Permission Service is correctly configured:

6. Check that Resource Central Tracking Service is correctly configured:

7. Check database connection by RC configuration tool:

8. Open the service list to confirm status of all services (status of Resource Central Synchronize Service, Resource Central Maintainence Service, Resource Central Permission Service, and Resource Central Tracking Service must be “Running”).

9. Open web backend in the browser to see that it works.

About future product upgrades

The following scenario is dealing with upgrading an existing installation which already use SQL Windows Authentication.

1. It is possible that the database cannot be upgraded in the upgrade process. The database must be upgraded manually by the RC configuration tool.

2. Windows Authentication must be re-configured in the RC configuration tool:

Properties

Applies to: RC 4.2+

Reference: TFS #36798; 53653

Knowledge base ID: 0178

Last updated: Oct 25, 2023