SQL Server Service

The SQL Server Service Service Type provides the ability to expose entities in a Microsoft SQL server database as Service Objects, which can then be mapped to SmartObjects. This service type can represent Tables, Views and Stored Procedures in a SQL database as Service Objects, and supports SQL Server on-premises as well as Azure SQL Database (see Considerations.)

For a list of Microsoft SQL Server versions that can be connected to using this Service Type, please see the K2 Compatibility Matrix.

Service Authentication

The following Authentication Modes can be used with the SQL Server Service Type:

In a distributed environment where K2 PTA (Pass Through Authentication) is enabled, SQL Impersonation must be configured to allow the K2 Service Account to impersonate the connecting user. Without this an error will occur, for example Login failed for NTAuthority\NTAnonymous, when a user tries to execute the SmartObject. To enable SQL Impersonation, execute the following stored procedure in the target SQL system:
GRANT IMPERSONATE ON LOGIN::[Domain\UserName] TO [Domain\K2ServiceAccount]

TrustServerCertificate

The TrustServerCertificate can be added to the connection string and configured by using the Extra field in the Service Authentication section. When TrustServerCertificate is set to true, the transport layer will use SSL to encrypt the channel and bypass walking the certificate chain to validate trust. If TrustServerCertificate is set to true and encryption is turned on, the encryption level specified on the server will be used even if the Encrypt connection service key is set to false. The connection will fail otherwise. For more information see https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnectionstringbuilder.trustservercertificate(v=vs.110).aspx.

The recommended use of TrustServerCertificate and encryption is:

Service Keys (Service Instance Configuration Settings)

Key Can be modified Data Type Sample Value Notes
StoredProc Dataset Execution Yes
 True/False False

If you have stored procedures executing multiple select statements and you want an exception thrown from one of the others (not just first one) to come back to K2, set the “StoredProc Dataset Execution” to true. See K2 SQL Server Broker: Stored Proc Requirements for more information.
On Different SQL Server Yes
(required)		 True/False False

Specifies whether the target Database is located on a different SQL server/SQL Instance than the SQL server/SQL instance where the K2 Databases are located. .

This value must be set to True when configuring the Service Instance for a named instance.

When connecting to SQL Azure, ensure the value is set to True
Non-word character replacement for object system names Yes
(required)		 Text _ Character to be used for replacement of non-word characters (such as spaces) in Service Object system names , where required. The default is underscore (_)
Command Timeout Yes Number 60

The response wait time in seconds before a request timeout is thrown. If you get timeout errors when registering or refreshing the Service Instance or when executing SmartObject methods at runtime, you can increase this timeout to a longer interval.

This field is specific to the SQL SmartObject Service Instance’s SQL queries and commands. When a stored procedure of the SQL SmartObject is executed or described, and will only affect SQL SmartObjects.

The commandtimeout value in the K2HostServer configuration file configures SmartObject Runtime. It is used when the SmartObject Runtime does SQL queries and commands. It is when adding SmartObject definitions in the Database, when Describing or deleting or selecting Service Instance definitions, or when selecting data from the results of other non SQL SmartObjects. It can affect all SmartObjects.
Database Maximum Decimal Value Yes
(required)		 Number 1,000 The maximum number of decimal values in the database.
Database Yes
(required)		 Text The name of the SQL Database you wish to connect to. This value is required.
Server Yes
(required)		 Text

mysqlserver.mydomain
or
hostname/instancename

 The name of the SQL Server or SQL server Instance where the target Database resides.
Use parameters for stored procedures Yes
(required)		 True/False

If this setting is true, stored procedure parameters will be added as Service Object method parameters. All stored procedure parameters are added as optional or required parameters, and not as properties.
Suppress Transaction Yes True/False

As SQL does not allow nested transactions in certain scenarios, K2 (using the DAC) implements an extra transaction to keep the connection open for impersonation. Use the 'Suppress Transaction' setting on the K2 SQL Server service to suppress the extra transaction. See SQL Service Troubleshooting. See Suppress Transaction Considerations.
Use Native SQL Execution Yes
(required)		 True/False

This enables 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. This can help to improve performance of runtime methods, especially for large result sets, but has some additional requirements.

This setting only applies to LIST methods.

When connecting to SQL Azure, ensure the value is set to False.

If the Use Native SQL Execution setting is True and the On Different SQL Server setting is True, K2 needs to execute the sp_addlinkedserver stored procedure on the target database to set up the necessary resources. To do so, the K2 Service Account must be granted SysAdmin permissions on the target database. These permissions can be revoked after the linked server has been added. See Troubleshooting the SQL Server Service Type for more information.

If you get runtime errors when executing methods against the target SQL database, try to change this setting to False. Setting the value to False might have a performance impact. See the Troubleshooting section later in this topic for more information.
Encrypt connection Yes True/False

If this setting is true, the SQL connection to be encrypted. ( This can be useful for Cloud based SQL connections such as Azure.)

When encrypt connection is enabled, the Encrypt flag on the SQL connection string is set to True. If the SQL server has a certificate installed and configured, it will use that for the encryption. For more information see https://msdn.microsoft.com/en-us/library/ms254500(v=vs.110).aspx
Use Windows Authentication for SSO Yes
 True/False False When this is set to True it sets Single Sign-On (SSO) support for HTTP/S connections to Integrated Windows Authentication (IWA), which sends the user authentication to Active Directory.
Set the impersonation user on the DB ContextInfo Yes
 True/False False

When enabled, all calls to SQL will set the SQL CONTEXT_INFO variable that is in a VarBinary(128) format to the user's FQN authenticated in K2 that is currently executing the SmartObject. This can then be read and used in SQL (for example, in stored procedures) to determine the user that is making the SmartObject call, for example, to restrict user access. See KB003062 for more information.

All authentication modes are supported
See DB ContextInfo Considerations.

Service Instance Category and Object Structure

When Service Objects are created for the Service Instance, they are organized into categories for Stored Procedures, Tables and Views. The Service Object names will include the schema name of the entity in the database.

Service Objects for Tables that contain a Primary Key column will contain all the CRUD (Create, Read, Update, Delete) as well as List methods. If there is no primary key on the table, then only a List method will be created.

For integration with stored procedures, the resulting service objects will return one of two method types:

  1. Where stored procedures return data (e.g. SELECT), the resulting Service object Methods will be of type List
  2. Where stored procedures do not return data (e.g. INSERT INTO, DELETE and UPDATE), the resulting Service object Methods will be of type Execute

Parameters defined for Stored Procedures will be added as Service Object Method Parameters if the Use parameters for stored procedures configuration setting is true, otherwise the SP parameters will be added as Service Object Properties. Output parameters cannot be used.

When you register a SQL Server Service Instance, K2 executes the stored procedure to see its schema/metadata to create the input and return properties in the Service Object. However, executing a stored procedure can cause data to be deleted, updated, or inserted. K2 only needs the schema/Metadata to create the SmartObject, so the stored procedure is executed with default input properties and sets FMTONLY to ON. If you have an existing stored procedure and the following command does NOT return a schema, K2 can’t describe it and the properties will not be in the SmartObject:
SET FMTONLY ON
EXEC [Sproc]
SET FMTONLY OFF

The default values for property types are:

Property Type Default Values
GUIDs random GUID
Numbers 0
Time 00:00:00
DateTime and Date Current DateTime
Bit false
Text Blank/NULL
Files/Images Blank/NULL

Even though a stored procedure may be correct with a return property, if the above default values cause an execution error (such as a divide by zero exception), the return property cannot be identified and will not be available in the SmartObject.

FMTONLY returns only metadata, so it is used to test the format of the response without actually running the query. This cannot be done if the resulting dataset’s schema is dynamic and can change basedon the data. See https://docs.microsoft.com/en-us/sql/t-sql/statements/set-fmtonly-transact-sql?view=sql-server-2017 for more information on FMTONLY.

Dynamic Result Schemas for Stored Procedures

Dynamic result schemas for SQL Stored Procedure SmartObjects cannot be converted into Service Objects by K2. The schema being returned must contain a static list of properties. SmartObjects need the result dataset’s metadata (for example the number of fields and field name, type and size for each field) to create the Service Object and thus can only support a static result dataset and/or Stored Procedure output parameters. Stored Procedure output parameters are always static, however a result dataset can be dynamic when making use of temp tables, which do not work with the SQL Server service type.When using a temp table in a stored procedure, the result set becomes dynamic and can change depending on the execution of the stored procedure, and are, by definition, unable to be represented by the SmartObject.

K2 can use static output parameters even if the result dataset is dynamic. This will only work for defined output parameters, however. Example:
@Out_Dept_Codevarchar(12)output
This will only return values for a single record, and not for a table or a list, however. When registering or refreshing the SQL Server Service Instance, ensure that the Use parameters for stored procedures service key is set to False. The Output parameter defined will be added to the Service Object's properties.

Views use a SELECT statement and therefore will yield List-type Service Object methods.

SmartObjects

SmartObjects can be automatically created by selecting the Generate SmartObjects for this Service Instance check box when creating a new Service Instance. Designers can use the SmartObject design tools to build advanced SmartObjects that leverage the Service Objects in this service. It is recommended to use the SmartObject design tools to create SmartObjects rather than generating SmartObjects, since this allows better control over the naming, behavior and design of the SmartObject and its methods and properties.

Considerations