Symptoms
Service instance Throwing Error on SmartObject
Diagnoses
We have issue with SQL service instance.
I tried update Service instance and SmartObject Update worked but when I tried to execute smartobject the smart Object throws an error. This smartobject is our primary smartobject for getting detail for domain users.
There is project in UAT and that project very critical for us. I tried to deploy SmartObject and Service Instance using Package and deployment but it didn't worked.
So I tried to add Service Instance as Brand New one and create new smartobject with different name. I still having an issue with it.
Resolution
The key "On Different SQL Server" = true and "Use Native SQL Execution" = true these settings usually only affect "list" type method.
When both keys are true (this is 'Option 1' of the documentation "http://help.k2.com/onlinehelp/k2blackpearl/UserGuide/4.6.8/webframe.html_SIC02.html"):
- the stored procedure sp_addlinkedserver has to be run to execute the query on the remote SQL Server.
When a user executes a SQL Service SmartObject method that uses a Linked Server to a remote LOB database server, if the Linked Server does not exist K2 will attempt 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 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
BUT the user could also temporarily give the SQL Login for the K2 service account the SysAdmin role, run the list method so the linked server gets created then remove the SysAdmin role.
For information on crating Linked Servers manually, please see the MSDN topic: https://msdn.microsoft.com/en-us/library/ff772782.aspx
As your company policy do not allow for the use of linked server objects, we set "Use Native SQL Execution" = false and "On Different SQL server" = true (this is 'Option 4', however, this will be ignored by the service and will function similar to 'Option 2') which will execute the query without the need for a linked server. Please see the article below regarding what the "Use Native SQL Execution" key is used for:
http://help.k2.com/displaycontent.aspx?id=5998
