Explanation
Integrated Security is used in SQL Server to authenticate using the Windows identity of a user or application.
This eliminates the need for SQL logins with passwords, which increases security and simplifies SQL Server administration. As a result, passwords no longer need to be included in the connection string.
Switching the Lexolution Server and ADB to Integrated Security
To switch the server-side database connection to Integrated Security, you need to update the connection strings in the Lexolution Server configuration.
The “User ID” and “Password” attributes are replaced with the “Integrated Security” attribute.
You can usually find the Lexolution Server configuration in the program directory of the Lexolution Server at
C:\Program Files (x86)\STP AG\LEXolution.KMS Server.
The file is named
STP.Kms.Server.exe.config.
Example:
Authentication using 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;" />Additionally, the Lexolution and reporting databases need to be updated:
- Transfer all schemas owned by KmsReaderWriter to dbo
Optional: Transfer all schemas owned by any SQL user other than KmsReaderWriter, who was previously assigned to dbo, to dbo - Create the login for the Windows user who will be used for Integrated Security (if not already present)
- Assign the Windows user who will be used for Integrated Security to dbo
It is recommended to use the Lexolution Database Updater (DB-Updater) for this.
This tool can make these changes for the Lexolution database.
You can run it via the UI or silently by passing parameters.
New parameters: |New Parameters|Value| |—|—| |postRestoreWindowsUserEnabled| Boolean| |postRestoreDeleteKmsReaderWriter| Boolean| |postRestoreWindowsUsername| String| |postRestoreSqlUsername| String| |postRestoreSqlUserPassword| String|
SqlUsername and password are not needed when switching to Integrated Security.
They can be used if you want to switch the database to a SQL user instead.
In this case, set postRestoreWindowsUserEnabled to false or 0.
If the KmsReaderWriter user is no longer needed, you can delete this user from the database (parameter
postRestoreDeleteKmsReaderWriter).
🛈 Note
The PostRestore script provided up to version 9.3 does not take these parameters into account and should no longer be used.
Any other users who were previously used as dbo must be deleted manually if needed.
Please note that to run the PostRestore script via a silent call of the DB-Updater, as before, you must pass “executePostRestoreScript” as true or 1.
For running with PostRestore functionality or for any actions beyond a simple database update, it is recommended to use the Lexolution DB Updater in UI mode.
There is no automation in the DB-Updater for the reporting database, as it only applies to the Lexolution database.
Here, the above-mentioned changes (schema assignments, user changes and assignments) must be done manually.
The new PostRestore script can be used to help with this. For example, run it in SQL Server Management Studio.
In this case, set the required input parameters directly in the script.
Related to