SQL Server Service - Service Instance Configuration

The Service Instance Configuration wizard screen adds new SQL Server Service Instances. Two screens are used to configure the Service Instance.

If the flag On Different SQL Server is set to true, the stored procedure sp_addlinkedserver has to be run to execute the query to link to the remote SQL Server. This should be run on the SQL server containing the K2 database.

When a user executes a SQL Service SmartObject method that uses a Linked Server to a remote LOB database server, if the Linked Server does not exist K2 will attempt to execute the sp_addlinkedserver system stored procedure as the K2 service account to create it. Quite often the K2 service account does not have the permissions to execute this stored procedure and list method fails with following error:
"User does not have permission to perform this action"

The minimum permissions required by the K2 service account to execute the sp_addlinkedserver system stored procedure are:

ALTER ANY LINKED SERVER
ALTER ANY LOGIN

BUT the user could also temporarily give the SQL Login for the K2 service account the SysAdmin role, run the list method so the linked server gets created then remove the SysAdmin role.

For information on creating Linked Servers manually, please see the MSDN topic: Create Linked Servers (SQL Server Database Engine)

Fig. 1. SQL Server Service - Service Instance Configuration Screen 1

Option	What it is
Database	Specifies the relevant SQL Database that should be used for the SQL Server Service Instance
Server	Specifies the relevant Server that should be used for the SQL Server Service Instance
Use Native SQL Execution	This will enable the ability to execute and filter queries directly against the SQL server where the complex queries have been specified in ADO.NET and reduce complex data structures being passed between K2 and the databases. When this option is set to true a SQL query is returned instead of a data table. Note: This only applies to LIST methods. All other method types will be executed directly against the database. Refer to the options section below for more information.
On Different SQL Server	Specifies whether the specified Database is located on a different server than the server where the K2 specific Databases are located. Refer to the options section below for more information.
Non-word character replacement for object system names	Character to be used for replacement of spaces within object system names if required. The default is '_'
Use parameter for stored procedures	Allows stored procedure parameters to be added as Method Parameters.
Encrypt Connection	This allows the SQL connection to be encrypted when setting this property to True. This can be used for Cloud based SQL connections such as Azure.
Command Timeout	Setting the Command Timeout is usefull when creating, refreshing or editing a SQL Server Service Instance on a SQL database to retrieve metadata, where a table, view or stored procedure contains a large number of properties.
Security Provider	Specifies the Security Provider. Not applicable to this service.
User Name	Specifies the User Name that should be used to access the specified Server.
Password	Specifies the Password that should be used to access the specified Server.
Extra	Provides additional information, for example dll paths needed. Not applicable to this service.
Impersonate	The impersonate option allows the K2HostServer service account to impersonate the user.
Enforce Impersonation	Enforces impersonation using K2 Pass-through Authentication. See K2 blackpearl Getting Started Guide for more information.

When registering, editing or refreshing a SQL Server Service Instance and the database contains a large amount of tables where not all of these tables are required to work with, Deny permissions can be set on the non required tables specifically for the K2 Service Account. Setting the Deny permissions as well as setting the Command Timeout to a large value such as 900, might prevent timeout errors to occur.

Use Parameter for Stored Procedures setting

A feature was added in the SmartObject framework to allow for optional parameters. All stored procedure parameters are now added as optional and required parameters and not as properties as was previously the case. This new configuration setting was added to allow for stored procedure parameters to be added as method parameters.

The following applies when using the Use parameter for stored procedures setting on the Service Instance Configuration screen and having used stored procedure parameters prior to K2 blackpearl 4.6.5:

The default value of the Use parameter for stored procedures setting is 'true'
When creating a new instance, the default value will be applied which results in 'true'
When refreshing an instance, the existing value configured for the instance will be used. If configured prior to K2 blackpearl 4.6.5, the value will be blank/null which will result in ‘false’
When editing an instance, the value can be changed if required or the existing value configured for the instance can be used. The setting will depend on the edited value.

Configure Rights

When configuring the rights the following applies:

If the User Name and password is configured and the Impersonate checkbox is selected (set to True) the service will ignore the User Name and password and use Impersonate.
If the User Name and password is configured and the Impersonate checkbox is not selected (set to False) the service will use SQL Authentication.
If the User Name and password is not configured and the Impersonate checkbox is not selected (set to False) the service will use the K2 Service Account.
If only the User Name and password is configured the service will use SQL Authentication.

The second Service Instance Configuration screen:

Fig. 2. SQL Server Service - Service Instance Configuration Screen 2

Option	What it is
System Name	Automatically generated System Name for the new Service Instance
Display Name	Provides an automatically generated display name for the new Service Instance. The display name can be edited to be more user friendly. This allows users to distinguish between different SQL Service Instances as multiple SQL Service Instances can be created
Description	Provides a description for the new Service Instance

Options

When configuring the settings in the Use Native SQL Execution and On Different SQL Server fields various combinations can be used with different outcomes. These options are only considered when method of type List are executed. The matrix below describes the options available:

Option	Use Native SQL Execution	On Different SQL Server
Option 1	True	True
Option 2	False	False
Option 3	True	False
Option 4	False	True (This option can be selected however, this will be ignored by the service and will function similar to Option 2)

'On Different SQL Server' will ONLY ever be used (true) when 'Use Native SQL Execution' is also set to true.

SQL Impersonate setting

If the Impersonate checkbox is selected to indicate that impersonation must be used then it is necessary to run the following SQL commands on the database that the interaction will take place with. Replace the [Domain \User Name] placeholder with the correct details for your environment.

Copy High Level SPAM Security Example
EXEC SP_GRANTLOGIN [Domain\User Name] go EXEC SP_GRANTLOGIN [Domain\K2Service] go EXEC SP_ADDUSER [Domain\User Name] go EXEC SP_ADDUSER [Domain\K2Service] go GRANT IMPERSONATE ON USER::[Domain\User Name] TO [Domain\K2Service]; go --HERE YOU NEED TO DETERMINE WHAT OPERATIONS THE USER MAY/MAY NOT EXEC AGAINST THE DATABASE / TABLES GRANT SELECT TO [Domain\User Name] go

Copy High Level SPAM Security Example

EXEC SP_GRANTLOGIN [Domain\User Name]
go

EXEC SP_GRANTLOGIN [Domain\K2Service]
go

EXEC SP_ADDUSER [Domain\User Name]
go

EXEC SP_ADDUSER [Domain\K2Service]
go

GRANT IMPERSONATE ON USER::[Domain\User Name] TO [Domain\K2Service];
go

--HERE YOU NEED TO DETERMINE WHAT OPERATIONS THE USER MAY/MAY NOT EXEC AGAINST THE DATABASE / TABLES
GRANT SELECT TO [Domain\User Name]
go