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:

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. It is recommended that the linked server object is pre-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 instance and then change back to limited permissions for runtime.