Troubleshooting the SQL Server Service Type
sp_addlinkedserver
If the On Different SQL Server and Use Native SQL Execution service key values are True, the stored procedure sp_addlinkedserver must be run to execute the query on the remote SQL Server. When you execute a SQL Service SmartObject method that uses a Linked Server to a remote SQL database server, if the Linked Server does not exist, K2 attempts 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 the 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
Alternatively, you could also temporarily give the SQL Login associated with the K2 service account the SysAdmin role, run a list method so the linked server gets created, and then remove the SysAdmin role. Once this is done, the linked server setting stays in effect.
For information on creating Linked Servers manually, please see the MSDN topic: Create Linked Servers (SQL Server Database Engine)
Permission Levels
When creating a new SQL Server instance, if the database is on a different server from the SQL Server where the K2 Database resides, then a linked server object is created. This requires elevated permissions (i.e. sysadmin). If the account does not have these permissions, then it may only partially work and cause issues. We recommend that the linked server object is created in SQL prior to creating the new instance.
For a simple database, the permissions to create the instance are: data reader; data writer; and execute. However, depending on the nature of the stored procedures, DBO permissions may also be necessary. Granting DBO permissions can be a problem for very large databases, due to the broker and how it scans objects to create the instance. For very large databases you need to limit the permissions the account has to only the objects you want to create.
You can elevate permissions temporarily to create the instance and then change back to limited permissions for runtime.
Additional SQL connection string parameters
To add connection string parameters to the SQL connection string, use the Extra field in the service Authentication section. For example, when connecting to SQL Server using AlwaysOn Availability Groups, add MultiSubNetFailover=true, or to change the maxpoolsize, add maxPoolSize=2000.
Linked stored procedure from custom database on same server as K2 to another SQL server
In the following scenario, MS SQL does not allow nested transactions:
SQL server 1:
Has a SQL Server instance with a Database called Test and a Table called A.
SQL server 2:
Has the K2 database is installed in the server, but it also has a database called Custom. On this SQL server there is a linked server connection to SQL on Server 1. Custom database has a stored procedure B, which executes an update on Table A on SQL Server 1's Test DB.
As SQL does not allow nested transactions, 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 messages from the extra transaction.