LEXolution Installation Guide: Switching SQL Server to Integrated Security

Overview

Integrated Security in SQL Server uses the Windows identity of a user or application for authentication.
This eliminates the need for SQL logins with passwords, which improves security and simplifies SQL Server administration. As a result, passwords no longer need to be passed in the connection string.

Switching the LEXolution Server and the Reporting Database to Integrated Security

To switch the server-side database connection to Integrated Security, the connection strings in the LEXolution Server configuration must be updated.
The "User ID" and "Password" attributes are replaced by the "Integrated Security" attribute.

The LEXolution Server configuration file is typically located in the LEXolution Server program directory at
C:\Program Files (x86)\STP AG\LEXolution.KMS Server.
The file is named STP.Kms.Server.exe.config.

Example:

Authentication via SQL user

<add name="KMS" connectionString="Server=localhost; Initial Catalog=Kms; User ID=KmsReaderWriter; Password=xxxx" />
<add name="KMS_Auswertung" connectionString="Server=localhost; Initial Catalog=Kms_Auswertung; User ID=KmsReaderWriter; Password=xxxx" />

Authentication using Integrated Security (Windows user)

<add name="KMS" connectionString="Server=localhost; Initial Catalog=Kms; Integrated Security=True;" />
<add name="KMS_Auswertung" connectionString="Server=localhost; Initial Catalog=Kms_Auswertung; Integrated Security=True;" />

In addition, the LEXolution and reporting databases must be updated:

• Transfer all schemas owned by KmsReaderWriter to dbo
  Optional: Transfer all schemas owned by a SQL user other than KmsReaderWriter that was previously mapped to dbo, to dbo
• Create a login for the Windows user to be used for Integrated Security (if not already present)
• Map the Windows user to be used for Integrated Security to dbo

The recommended approach is to use the LEXolution Database Updater (DB Updater).
It can handle these adjustments for the LEXolution database.
It can be run via the UI or silently using parameter input.

New parameters: |New Parameters|Value| |—|—| |postRestoreWindowsUserEnabled| Boolean| |postRestoreDeleteKmsReaderWriter| Boolean| |postRestoreWindowsUsername| String| |postRestoreSqlUsername| String| |postRestoreSqlUserPassword| String|

SqlUsername and password are not required when switching to Integrated Security.
These can be used to alternatively switch the database to a SQL user.
In that case, pass postRestoreWindowsUserEnabled as false or 0.

A KmsReaderWriter user that is no longer needed can be deleted for this database using the parameter postRestoreDeleteKmsReaderWriter.

🛈 Note

The PostRestore script provided up to version 9.3 does not support these parameters and must no longer be used.

Any non-standard users that were previously used as dbo must be deleted manually if needed.

Please note that to run the PostRestore script via a silent call to the DB Updater, "executePostRestoreScript" must still be passed as true or 1, as before.

For running with PostRestore functionality, or for any actions that go beyond a simple database update, using the LEXolution DB Updater in UI mode is recommended.

No automated process is available in the DB Updater for the reporting database, as it only applies to the LEXolution database.
The adjustments mentioned above (schema assignments, user adjustments and mappings) must be performed manually for the reporting database.
The new PostRestore script can be used as a reference for this — for example, run it in SQL Server Management Studio.
In this case, set the required input parameters directly in the script.