kbt179642

Issue

An error appears on the K2 Site Application Pool Configuration step when attempting a re-configuration with the K2 Setup Manager.

"Format of the initialization string does not conform to specification starting at index 0"

Symptoms

• You might encounter this error after changing some configuration settings that required you to restart the K2 Service.
• You have checked connection strings in Configuration.config, K2HostServer.exe.config, K2Server.setup and the values are all correct.
• The K2 Configuration Service, which the K2 Server service has a dependency on from K2 Five 5.3 onwards, is running as the K2 Service account.
• The InstallerTrace log shows the same 'Format of the initialization string...' error after every attempting to run the query SELECT [Uri] FROM [Identity].[ClaimIssuer].

Troubleshooting Steps

Incorrectly editing the K2 database can result in system instability or failure. Before making any database changes, it is strongly recommended to make a backup of your database. Database changes should only be performed by database administrators or users with sufficient knowledge and experience with SQL server to apply these changes correctly. If you need assistance with implementing these changes, please log a Support Ticket with K2 Product Support.

1. Run these 4 scripts on the K2 database sequentially to recreate the encryption if it is broken:

a. Open Master Key:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

OPEN SYMMETRIC KEY [SCSSOKey] DECRYPTION BY CERTIFICATE [SCHostServerCert];

SELECT [VariableToken],
[VariableValue] as [Encrypted],
CONVERT(NVARCHAR(MAX), DecryptByKey([VariableValue])) as [Decrypted]
FROM [HostServer].[Configuration] WHERE [Encrypted] = '1';

CLOSE SYMMETRIC KEY [SCSSOKey];
GO

INSERT INTO [HostServer].[HostServerSetting] ([Name], [Value])
VALUES ('UpdateEncryptionKey', 'Manual')
GO

b. Change Password:

OPEN SYMMETRIC KEY [SCSSOKey] DECRYPTION BY CERTIFICATE [SCHostServerCert];

SELECT [VariableToken],
[VariableValue] as [Encrypted],
CONVERT(NVARCHAR(MAX), DecryptByKey([VariableValue])) as [Decrypted]
FROM [HostServer].[Configuration] WHERE [Encrypted] = '1';

CLOSE SYMMETRIC KEY [SCSSOKey];

EXEC [Utility].[RecreateEncryptionKey]
@OldKeyName = 'SCSSOKey',
@NewKeyName ='Temp_SCSSOKey',
@OldCertName ='SCHostServerCert',
@NewCertName ='Temp_SCHostServerCert',
@KeyValue ='K2_temp',
@KeySource ='G8keeper'
GO

EXEC [Utility].[RecreateEncryptionKey]
@OldKeyName = 'SCSSOKey',
@NewKeyName ='Temp_SCSSOKey',
@OldCertName ='SCHostServerCert',
@NewCertName ='Temp_SCHostServerCert',
@KeyValue ='K2',
@KeySource ='G8keeper'
GO

OPEN SYMMETRIC KEY [SCSSOKey] DECRYPTION BY CERTIFICATE [SCHostServerCert];

SELECT [VariableToken],
[VariableValue] as [Encrypted],
CONVERT(NVARCHAR(MAX), DecryptByKey([VariableValue])) as [Decrypted]
FROM [HostServer].[Configuration] WHERE [Encrypted] = '1';

CLOSE SYMMETRIC KEY [SCSSOKey];

c. Drop Keys:

DROP SYMMETRIC KEY SCSSOKey
GO
DROP CERTIFICATE SCHostServerCert
GO
DROP MASTER KEY
GO

d. Remake Keys:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX'
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX'
GO

CREATE CERTIFICATE SCHostServerCert WITH SUBJECT = 'Host Server Certificate'
GO

CREATE SYMMETRIC KEY SCSSOKey
WITH ALGORITHM = AES_256,
KEY_SOURCE = 'G8keeper',
IDENTITY_VALUE = 'K2'
ENCRYPTION BY CERTIFICATE SCHostServerCert;
GO

OPEN SYMMETRIC KEY [SCSSOKey] DECRYPTION BY CERTIFICATE [SCHostServerCert];

SELECT [VariableToken],
[VariableValue] as [Encrypted],
CONVERT(NVARCHAR(MAX), DecryptByKey([VariableValue])) as [Decrypted]
FROM [HostServer].[Configuration] WHERE [Encrypted] = '1';

CLOSE SYMMETRIC KEY [SCSSOKey];
GO

Please replace XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX with the Master Key Password after obtaining it from K2 Support.

2. If you are still encountering the error, it might mean that your encryption is working, but the values in the database are corrupted.

Then, you will have to run the following script:

OPEN SYMMETRIC KEY [SCSSOKey] DECRYPTION BY CERTIFICATE [SCHostServerCert];
SELECT [VariableToken], [VariableValue] as [Encrypted], CONVERT(NVARCHAR(MAX), DecryptByKey([VariableValue])) as [Decrypted] FROM [HostServer].[Configuration] WHERE [Encrypted] = '1' ORDER BY 1;
CLOSE SYMMETRIC KEY [SCSSOKey];

3. If the error is still encountered when running the Setup Manager, you will then need to purge the table with the following script: 

DELETE FROM [HostServer].[Configuration] WHERE [Encrypted] = '1'

After that is done, re-run the Setup Manager, select 'Configure', and it should put the encryption back correctly.