Symptoms

After creating a stored procedure that connects to AD to get employee email addresses using a linked server. The following error is seen when executing the smartobject:

10702 An error occurred in the Service Instance. An error occurred while preparing the query 'SELECT mail FROM 'LDAP:(Client Details) WHERE objectCategory='person' AND objectClass='user' and employeeNumber = '(Client Details)'' for execution against OLE DB provider 'ADSDSOObject' for linked server 'ADSI'. "

When connecting to the sql server using the K2 service account and directly executing the stored procedure in the sql server it works.

Config settings of the SQL service instance - Both "On different sql server" and "Use native sql execution" is set to TRUE.

Diagnoses

On SQL link server properties - ADSI
After updating the security page (Be made Using this security context) to use the remote login for the K2 service account.

Resolution

Proceeded to create a new test SmartObject from the stored procedure once we saw the original SmartObject failed.

The new SmartObject method to Execute was changed to list and successfully brought up the email once the employee number was used to execute.