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:

• Static: SQL Authentication will be used, you need to provide a SQL user username and password.
  Static is the only valid authentication mode for connecting to an Azure SQL database
• Impersonation: The user authenticated in K2 at the time of calling the SmartObject method is impersonated when K2 connects to the SQL server
• Service Account: K2 uses the K2 Service Account as the security context when connecting to the SQL server

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 SqlConnectionStringBuilder.TrustServerCertificate Property.

The recommended use of TrustServerCertificate and encryption is:

• Set TrustServerCertificate=False in the Extra field.
• Set Encrypt Connection = True in the Service Keys section.

Service Keys (Service Instance Configuration Settings)

Key	Can be modified	Data Type	Sample Value	Notes
Filter null as empty	No	N/A	N/A	For system use
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 Stored Procedures 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	23,9	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 set to True, stored procedure parameters are added as Service Object method parameters. All stored procedure parameters are added as optional or required parameters, and not as SmartObject properties. If set to False, the stored procedure parameter is returned as a property (typically as System.String[]). When you set this service key to false, you generate SmartObject properties for all stored procedure parameters. This may, however, result in having SmartObject properties that do not return data. When this service key is set to true, stored procedure parameters are added as Service Object method parameters and are tied to the method. This allows for the parameter to be properly passed to the stored procedure call without the added property on the SmartObject for return results.
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. 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 sp_addlinkedserver 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 Connection String Syntax.
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, 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.
Stored Procedure returning as XML (for xml statement)	Yes	Text	[dbo].[MyServiceObject]	A comma-separated list of service objects (stored procedures) that return xml using the FOR XML SQL clause. When using this method and returning large amounts of xml, SQL Server returns the XML as multiple rows. When you specify the service object in this field, the XML returned is concatenated into one row.

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.

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

• For tables containing a period/full stop character (.) in the table name, only List Service Object Methods will be generated (not CRUD methods). You may have to remove this symbol from the table name to generate CRUD Service Object Methods.
• Any changes to the SQL Tables, Stored Procedures, or Views, require the Service Instance to be refreshed.
• Depending on the size of the SQL database being referenced, registering or refreshing a Service Instance may take several minutes to complete.
• You may want to use SQL permissions to limit the access of the account used to discover the SQL entities, so that K2 only discovers the entities that the account has access to. This approach can also be used to restrict the number of entities K2 discovers and generates Service Objects for, when registering or refreshing the service.
• To discover schemas of Stored Procedures, Views, and Tables in the targeted SQL database, the account used when registering or refreshing the service instance requires at least View Definition permissions on those entities in the targeted SQL database. Depending on your database, other permissions such as Execute may also be required to correctly discover schemas in the targeted database.
• K2 uses FMTONLY when creating Service Objects for Stored Procedures. See FMTONLY for more information.
• Stored procedures that return data will yield List-type Service Object Methods. Stored procedures that do not return data will yield Execute-type Service Object Methods.
• Stored Procedures that use Temp Tables (i.e. SELECT...INTO) or CURSORs cannot be represented as Service Objects since the resulting table structure is unpredictable.
• Dynamic Stored Procedures (i.e. Stored Procedures that build up a SQL query string and then execute that string) cannot be represented as Service Objects, since the resulting table structure is unpredictable.
• The following SQL Data Types are known not to work natively with the SQL Server Service
  • TimeStamp
  • Image
  • SQL_Variant
  • Binary
  • VarBinary
  • Numeric
  • Hierarchyid
• When using "Auto-incrementing" data types (such as AutoNumber and AutoGuid), K2 will represent the data as the underlying data type (e.g. Number or Text for a GUID) but will not implement any automatic numbering since the auto-incrementation is implemented by SQL server, not K2.
• Service Objects for Tables that contain a Primary Key column will contain all the CRUD (Create, Read, Update, Delete) methods, as well as LIST. If there is no primary key on the table then only a List method will be created.
• If Impersonate is used as the Service Authentication mode, and if the targeted database is on the same SQL Server Instance as the K2 database and if the Use SQL Native Execution setting is set to True on the SQL Service Instance, K2 will interact with the target database in the context of the K2 Service account, as opposed to the impersonated user.
• When the Set the impersonation user on the DB ContextInfo field is set to True:
  • With this setting you can connect and execute as a non-impersonated user (for examples, the service account) to SQL, but still have the username that initiated the call available by querying the CONTEXT_INFO value in SQL.
  • This call is made irrespective of any other settings on the Service Instance.
  • There are scenarios that the user executing the SmartObject (based on the SQL Server Service) might not be passed to the database. For example, when a customer SmartObject setup uses the Service Account for authorization to execute the SQL SmartObject. The specific username will then be lost because the previous SmartObject’s user context would have been reverted to the service account.
  • Other settings can influence this setting, for example, when Native SQL Execution is true, list methods are not executed by the SQL service, and thus the username may not be set.
  • When there is no user context, such as in a workflow server event, the user context info is set to the K2 service account's identity. When there is user context for the call, such as a SmartObject method executed in a SmartForm, the user context info is set to the identity of the user who opened the form.
  • The SQL CONTEXT_INFO value is set as a VarBinary(128) type.
  • CONTEXT_INFO will contain the user's K2 FQN id including the security label, for example K2:Denallix\Bob.
  • The CONTEXT_INFO value is essentially just a string that represents the K2 user's FQN. It is not a security token or security credentials.
• When the Suppress Transaction field is set to True:
  • The nested transaction issue is due SQL not allowing nested transactions in certain scenarios. And the K2 DAC requires two transactions (one for SmartObject Server Broker and one for SQL Service instance) so that all transactions in the SQL instance get executed in the same context. This setting is to suppress the second transaction for scenarios where this might cause issues.
  • There are two known issues that can be caused by nested transactions, that have undesired effects.
    • Executing a stored procedure on a linked SQL server can cause SQL to throw an error (ST 118800).
    • Working with Composite SmartObjects that access the same data, can cause locks in SQL, resulting in a timeout (ST 138011).
• For additional considerations and information please see Troubleshooting the SQL Server Service Type.

Considerations when using Azure SQL:

• Only Static authentication mode can be used, and you must use an Azure SQL database user account.
• When migrating from SQL Server to Azure SQL you may need to edit your stored procedures, for example, changing the output parameter from:
  @OutputPIDDelimList as VARCHAR(MAX) = '' OUTPUT
  to:
  @OutputPIDDelimList as VARCHAR(MAX) = NULL OUTPUT
• If the username on the server has an '@' in the name, the name of the server must be appended to the username, for example, if the server username is 'bob' on 'bobserver' then the username for the connection string can either be 'bob' or 'bob@bobserver'. But if the server username is 'bob@company' on 'bobserver' then the username can only be 'bob@company@bobserver'.
• Consider the performance impact of your K2 server if it is geographically separate from the Azure SQL environment. For example, when connecting an on-premises K2 server to an Azure SQL environment, test that the performance is acceptable.
• When creating a SQL Server service instance in Nintex K2 Five configured to an Azure SQL database with both Use native execution set to True and On a different server set to True, K2 will create a linked server in the local K2 database, but without a default catalog. A default catalog is required as there is no access to the master database.
  • Workaround
    
    Copy

    Delete the linked server and create it manually:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    EXEC sp_addlinkedserver @server = 'sqladventurejan.database.windows.net,1433', 
    @srvproduct = '', 
    @provider = 'SQLOLEDB', 
    @datasrc = 'sqladventurejan.database.windows.net,1433', 
    @location = '', 
    @provstr = '', 
    @catalog = 'SQL_Azure_-_AdventureWorks_DB' EXEC sp_addlinkedsrvlogin 'sqladventurejan.database.windows.net,1433', 
    'false', 
    NULL, 
    'K2CloudDemoSQLReader', 
    'password123';