Using Gen Windows SQL Server CSE with Windows authentication

Products

• Gen

Issue/Introduction

The Gen Client Server Encyclopedia (CSE) configuration documentation for the creation of the Data Source Names for the SQL Server CSE database advises using SQL Server authentication:
Gen 8.6 > Encyclopedia > Client Server Encyclopedia > Use the Client Server Encyclopedia > Install and Configure CSE > Prerequisites for CSE Configuration > Configure Databases

Due to current-day security requirements is it possible to use Windows authentication?

Environment

Release : 8.6

Component : Gen Client Server Encyclopedia

Resolution

Support has run tests indicating that using Integrated Windows authentication for both the CSE configuration and CSE startup/runtime is possible as follows.

Using the steps documented for "Create Microsoft SQL Server CSE Database" in: Gen 8.6 > Encyclopedia > Client Server Encyclopedia > Use the Client Server Encyclopedia > Install and Configure CSE > Prerequisites for CSE Configuration > Configure Databases
It is assumed that step 9 Option 2 has been used i.e. both 32-bit and 64-bit Data Source Names have been created.
It is also assumed that the logged-on Windows user has access to the SQL Server CSE database.

1. In step 13 configure both the 32-bit and 64-bit Data Source Names (DSNs) to use Integrated Windows authentication instead of SQL Server authentication.

2. Run the cse_config.exe program and enter the configuration parameters:
Gen™ 8.6 > Encyclopedia > Client Server Encyclopedia > Use the Client Server Encyclopedia > Install and Configure CSE > Configure a CSE > Set Configuration Parameters
On both the Encyclopedia Database Connection and Coordination Database Connection windows:
a. For the Database Name enter the DSN name (as normal)
b. For the Database User ID enter any value e.g. testuser. Note: The cse_config.exe UI requires a value to be entered but it will not be used for the SQL Server DB connection because Windows authentication is configured in the DSN i.e. the SQL Server DB connection will be done via the DSN using the logged-on user.
c. For Database Password enter no value. Note: Again not required because the logged-on user will be used to connect to the SQL Server DB.
The configuration should be successful.
The file C:\ProgramData\CA\Gen 8.6\cfg\CSE\iefmd.ini created then contains this for each of the 3 DBNAME lines:
"DBNAME=CSEDB  DBUSER=testuser  DBPSWD="
However as per above because Windows authentication is configured in the DSN, the DBUSER/DBPSWD credentials in the iefmd.ini file will effectively not be used for the SQL Server DB connection at CSE start-up/runtime i.e. the SQL Server DB connection will be done either by the logged-on user (for manual CSE start-up) or by the service user for the CSE service CSESvcMD start-up.

3. Modify the CSE service CSESvcMD "Log on" tab details.
The CSE service CSESvcMD is by default configured to start with "Log on as" set to the Local System Account which needs to be changed to an account that has SQL Server access.
Under "Control Panel > Administrative Tools > Local Security Policy > Local Policies > User Rights Assignment", enable "Log on as a service" for the account that has SQL Server access and which will be used to start the service.
Then change CSESvcMD to have "Log on as" set to that user.
The service and CSE iefmd.exe will then start successfully and use the service user to connect to SQL Server.

Additional Information

NOTE: As of June 24, 2022 using Windows Authentication for the CSE SQL Server connection has now been officially certified by Gen Engineering.
These 2 documentation sections have been updated:
Gen™ 8.6 > Encyclopedia > Client Server Encyclopedia > Use the Client Server Encyclopedia > Install and Configure CSE > Prerequisites for CSE Configuration
Gen™ 8.6 > Encyclopedia > Client Server Encyclopedia > Use the Client Server Encyclopedia > Install and Configure CSE > Configure a CSE > Set Configuration Parameters