Symptoms
Can't start k2 server after configuring for SQLUM. Here is the error from the Host Server logs:
"2001 Error Starting Host Server: SQL User manager Error:Login failed. The login is from an untrusted domain and cannot be used with Windows authentication."
Diagnoses
SQL server is located on another domain, and cannot be accessed using windows authentication.
Resolution
Use the following script to create the security label. Replace the values accordingly:
DECLARE @SecurityLabelName NVARCHAR(20) = 'K2SQL' -- Update as needed
DECLARE @SecurityLabelID UNIQUEIDENTIFIER = '8e8d5221-ee89-4cd7-99da-fcfcdf64abdb'
DECLARE @AuthSecurityProviderID UNIQUEIDENTIFIER = 'fc1848e6-23f5-49d8-8c48-9f7b197c80b7'
DECLARE @AuthInit XML =
'andltAuthInitandgt
andltinitandgt[SQL Server],[K2 Database],[SQL Username],[Password]andlt/initandgt
andltlogin/andgt
andltimplementation assembly="SQLUM, Version=4.0.0.0, Culture=neutral, PublicKeyToken=16a2c5aaaa1b130d" type="SQLUM.K2UserManager"/andgt
andlt/AuthInitandgt' -- XML configuration for the SQL provider, see K2 Help for more information on configuration values
DECLARE @RoleSecurityProviderID UNIQUEIDENTIFIER = @AuthSecurityProviderID
DECLARE @RoleInit XML =
'andltroleproviderandgt
andltinitandgt[SQL Server],[K2 Database],[SQL Username],[Password]andlt/initandgt
andltlogin/andgt
andltimplementation assembly ="SQLUM, Version=4.0.0.0, Culture=neutral, PublicKeyToken=16a2c5aaaa1b130d" type ="SQLUM.K2UserManager"/andgt
andltpropertiesandgt
andltuserandgt
andltproperty name="Name" type="System.String" /andgt
andltproperty name="Description" type="System.String" /andgt
andltproperty name="Email" type="System.String" /andgt
andltproperty name="Manager" type="System.String" /andgt
andltproperty name="DisplayName" type="System.String" /andgt
andlt/userandgt
andltgroupandgt
andltproperty name="Name" type="System.String" /andgt
andltproperty name="Description" type="System.String" /andgt
andltproperty name="Email" type="System.String" /andgt
andlt/groupandgt
andlt/propertiesandgt
andlt/roleproviderandgt' -- XML configuration for the SQL provider, see K2 Help for more information on configuration values
DECLARE @DefaultLabel BIT = NULL --1 = true, NULL and 0 = false
-- UPDATE TABLES
DELETE FROM [HostServer].[SecurityLabel] WHERE SecurityLabelName = @SecurityLabelName
INSERT INTO [HostServer].[SecurityLabel] VALUES (@SecurityLabelID, @SecurityLabelName, @AuthSecurityProviderID, @AuthInit, @RoleSecurityProviderID, @RoleInit, @DefaultLabel)