Setup Requirements

The following configuration prerequisites are required

The K2 Server must run under an account [Domain Name]/K2Service account
Within Active Directory there is an user group called ‘Domain/SqlUsers’ which must contain the names of the users who want access to the specific database(s)
The Active Directory user ‘Domain/SqlUser1’ which makes the delegated call

SQL Server Instance Configuration

These scripts need to be run once per SQL Server instance.

For each SQL Server Instance, the following mappings need to be created:

The above configuration enables the SQL Server to resolve the AD group or user by associating the Windows SID to a SQL SID.

Both AD Groups and AD Users can be added that you can set up a whole AD Group as a LOGIN, it needn’t just be an AD User:

Associate Windows SID to SQL SID	Copy Code
USE [master]; --Represent caller SID within SQL CREATE LOGIN [Domain\SqlUsers] FROM WINDOWS; --Represent service account SID within SQL CREATE LOGIN [Domain\K2Service] FROM WINDOWS; GO

The Grant IMPERSONATION rights on the K2 Host Server service account script enables the following actions:

This allows the service account to impersonate as the specific LOGIN
This is unnecessary if the service account LOGIN has high enough privileges (e.g. sysadmin)

Grant Impersonation Rights	Copy Code
USE [master]; --Allow one SID to impersonate as the other GRANT IMPERSONATE ON LOGIN::[Domain\SqlUsers] TO [Domain\K2Service]; GO

The Impersonation rights granted above must be mapped to a database user:

In the specific database (e.g. SalesDB), make sure the LOGIN above is mapped to a database USER
Since LOGINs operate at the SQL Server level, you need a USER principle in each database that is tied to the LOGIN
Either use an existing USER (such as dbo) or create a brand new database USER as per below

Map Server Login to the Local Database Principle	Copy Code
USE [SalesDB]; --Map server LOGIN to a local database principle, in this case using --the same name for convenience (can also use a different USER name) CREATE USER [Domain\SqlUsers] FOR LOGIN [Domain\SqlUsers]; GO

Running this script would not normally be required, but if it is required then only once per SQL Server Instance

Ensure that the USER specified above has rights to execute/read/write/delete all appropriate objects
If the LOGIN was mapped to dbo or assigned dbowner (or similar) permissions to the USER then nothing further is required.
If not, then the following script must be run for the specific database

Grant User Rights	Copy Code
USE [SalesDB]; GRANT EXECUTE ON [dbo].[MyProcOrTable] TO [Domain\SqlUsers]; GO