Adding, Editing and Deleting SQL User Information

Creating a User

You must manually add users to the SQLUM tables. For security purposes, the product provides a stored procedure which encrypts the passwords.

Open Microsoft SQL Server Management Studio.
Connect to the server where the K2 database is installed.
Expand the K2 database (K2 is the default).
Browse to the following path: K2 > Programmability > Stored Procedure
Find CustomUM.CreateUser, right click and select Execute Stored Procedure.
Enter the user's details on the Execute Procedure dialog.

Adding a User to a Group

To add a user to a group, you need to first create the group in the database and then add the user to that group. Create the group manually by following these steps:

Open Microsoft SQL Server Management Studio.
Connect to the server where the K2 database is installed.
Expand the K2 database (K2 is the default).
Right click on CustomUM.Group and select Edit Top 200 Rows.
Enter the group details into the table (GroupID is automatically generated when you save your changes).
Next you add users to the group. Right click on CustomUM.UserGroup and select Edit Top 200 Rows.
Enter the GroupID (from CustomUM.Group entry you created) and the UserID (from CustomUM.Users, the stored procedure you ran when creating the user creates the ID) into the table and save your changes to the database.

Adding a user to a Role

To add a SQL user to a role, add the user to the role through K2 Management site > Users node > Roles. You must have administrator privileges to do this.

Edit a User's Details or Change a Password

To edit a user's UserName, UserDescription, UserEmail, ManagerID or DisplayName, edit the relevant row in the CustomUM.User table.

To change a user's password, execute the CustomUM.SetPassword stored procedure and enter the Username and the new Password for the user whose password you want to change. The stored procedure will encrypt the new password.

Configuring the user account lockout policy

To protect SQLUM users from brute force password guessing attacks, the lockout policy will lock a SQLUM user account after the configured amount of invalid password attempts, for a configured amount of time. Administrators can change the default SQLUM lockout policy.

Setting up lockout policies for other identity providers like Active Directory or Azure Active directory is done in the identity provider and is not configured in the product.

In clean installations of the product and on the first upgrade to Nintex Automation(5.7) from a previous version, a default policy is configured that will lock accounts for 1 minute after 30 failed login attempts. This default policy already provides a good level of security as it would restrict a brute force attack to 30 attempts a minute which would require more than a century to guess all combinations of a password that is 5 characters long.

Change these defaults in the [CustomUM].[Config] table of the K2 database. Changing the configuration takes immediate effect and there is no need to restart any service. The configuration values are:

AccountLockoutThreshold: This is the number of login attempts to allow before locking the account. The maximum is 255. When set to 0, the lockout policy is disabled.
AccountLockoutDuration: This is an integer value that sets for how many minutes an account should be locked when the AccountLockoutThreshold is reached. It can be set to a maximum of 2,147,483,647 minutes (which is over 4000 years).

How it works

When a SQLUM user tries to log in through any product interface with an invalid password, the user's record in [CustomUm].[User] is updated by incrementing the FailedLoginAttempts with 1 and the LastLoginAttempt is set to the date and time of the login attempt. When the user fails to login as many times as specified in the AccountLockoutThreshold setting, the user's account becomes locked.

Once locked out, another login attempt with a valid password will not succeed until the time set in the AccountLockoutDuration field has passed. For security reasons, the failure message on screen will also not indicate why the login attempt failed. A login attempt that fails due to the user account being locked will be logged in the SQLUM.log file if SQLUM logging is enabled.

While the user account is in a locked state, any login attempts will no longer update the LastLoginAttempt column for the user.

When the AccountLockoutDuration period for a locked user account is over, the user account automatically becomes unlocked.

An administrator can unlock a locked user account by setting the user's FailedLoginAttempts to 0 in the [CustomUm].[User] table.

How to exclude a user account from the lockout policy

By default, in a workgroup type installation, the Administrator user account (which is linked to the service account) is automatically excluded from the lockout policy. This is done so that a brute force attack against the admin user does not cause the service account to become locked, which would cause instability of the platform.

Excluding a user account from the lockout policy is useful for high valued accounts which must always be able to log in for administrative or maintenance needs.

To exclude a user account from the policy, do one of the following:

Set the account's ExcludeFromLockoutPolicy field to True in the [CustomUm].[User] table.
When creating a user through the [CustomUm].[CreateUser] stored procedure, pass in 1 to the ExcludeFromLockoutPolicy parameter.

It is the responsibility of the system administrator to configure the system admin user account (or any high valued account) with a sufficiently complex password of 12 characters or longer to prevent successful brute force attacks against this account.

How to enable SQLUM logging

Enabling SQLUM logging is useful to monitor for possible brute force attacks or to see if a user account has been locked when the user is unable to log in. System administrators can enable SQLUM logging by editing the SQLUM.config file in the [k2 instalaltion directory]\Host Server\Bin folder. Restart the service for the changes to the configuration to take effect.

Set TraceToFile to true to log into a SQLUM.log file in the [k2 instalaltion directory]\Host Server\Bin folder.

Setting KeepLogBackup to true creates a new log file every time the service is restarted. When it is set to false, the SQLUM.log file is overwritten on every service restart.

Change in SQLUM login behavior

Administrators can create SQLUM users in a domain\username format (referred to here as the full username) even though SQLUM has no concept of a domain. Before K2 Five (5.6), users created with the domain\username format could log in using just the username portion if the K2 server is running in the same domain as the domain part of the full username. This behavior has changed and now in clean installations of K2 Five (5.6) and on the first upgrade to K2 Five (5.6) from a previous version, SQLUM users that were created with the domain\username format need to log in with the full username.

If the SecurityLabel.K2SQL.Methods application setting was already configured (as described in the section below) when upgrading, the configuration will be kept as is and the login behavior will not change for upgrades.

Revert to pre-K2 Five (5.6) login behavior

For SQLUM users created in the domain\username format, administrators can revert the SQLUM login behavior to what it was before the lockout policy changes. To do this the administrator needs to set which login methods to try for each application. The login methods are described in comments in the web.config files. To change the login method, find and edit the SecurityLabel.K2SQL.Methods application setting to the following value, in the list of web.config files:

Web.config files in these locations:

[k2 installation directory]\K2 smartforms Designer
[k2 installation directory]\K2 smartforms Runtime
[k2 installation directory]\WebServices\Identity\Sts\Forms
[k2 installation directory]\WebServices\ViewFlow
[k2 installation directory]\WebServices\API\Workflow\Preview
[k2 installation directory]\WebServices\API\Workflow\V1
[k2 installation directory]\WebServices\API\SmartObjectService.OData

By setting multiple login methods to try, one failed login attempt will cause an increment equal to the number of methods tried against the user which will cause a user account to become locked sooner than the configured AccountLockoutThreshold. In the example above, methods 1, 3, 5, 6, 7, and 8 are tried, so each failed login would increment the FailedLoginAttempts field by 6.

To set the login logic back to default, which requires the full username to be supplied (in the domain\username format), set the value of SecurityLabel.K2SQL.Methods to "9".