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.)

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 the product at the time of calling the SmartObject method is impersonated when it connects to the SQL server
  • Service Account: The product uses the Service Account as the security context when connecting to the SQL server
When connecting to on-premises systems from your environment, you must use static credentials. See KB002939: Connecting to On-Premises Data from K2 Cloud for more information about how to do this for supported systems.

In a distributed environment where PTA (Pass Through Authentication) is enabled, SQL Impersonation must be configured to allow the 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 be returned, 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. For Cloud, this value is usually True.

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 the product 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 from K2 Cloud, ensure the value is set to False. See the considerations for more information.

If the Use Native SQL Execution setting is True and the On Different SQL Server setting is True, the product 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 the product 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.

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:

  • List: Where stored procedures return data as a result set (e.g. SELECT), the resulting Service object Methods will be of type List.
  • Execute: 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 stored procedure parameters will be added as Service Object Properties. See Return Data from a Stored Procedure for more information.

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

When you register a SQL Server Service Instance, the product 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. The product 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, the product 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 SET FMTONLY (Transact-SQL) 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 the product. 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 so 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.

The product can use static output parameters even if the resulting 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. 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 then be added to the Service Object's properties. See K2 SQL Server Broker: Stored Procedures Requirements for more background information.

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.

See the following resources for more information:

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 the product only discovers the entities that the account has access to. This approach can also be used to restrict the number of entities the product 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.
  • The product 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), the product 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 the product.
  • 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, the product will interact with the target database in the context of the 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 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 server if it is geographically separate from the Azure SQL environment. For example, when connecting an on-premises server to an Azure SQL environment, test that the performance is acceptable.
  • Azure SQL does not allow access to the master database, so it is not possible to create a SQL Server service in K2 Cloud from an Azure installation linked to another database with the Use Native SQL Execution set to True. Use Native SQL Execution must be set to False. See Resolving Transact-SQL differences during migration to SQL Database for background information.