Saturday, 29 June 2013

For one of our customers, we had created some custom stored procedures in a separate database with CRM plugins to replace their existing SQL triggers. We required these SPs “as is” because they were having very complex logic inside them and would have taken ample amount of time to re-write the complete logic in CRM plugins. Hence, We had just reused those objects and amended it to make them compliance with Dynamics CRM standards by fetching the data from filtered views instead of direct table hits. Like,

Inside the Dynamics CRM plugins, we had written the code to connect to the custom database (using windows authentication connection string) and called the above stored procedures. However, surprisingly, the stored procedures did not return any data. Hence, one of our developers came to me with this problem. I knew that the problem is with Application Pool Identity. The CRM plugins are executing under the CRM Application Pool Identity user and that user does not have permission to access the CRM data.

The CRM filtered views run under the user security roles and do not return data if you don’t have configured the required privileges. Additionally, we must not configure the Dynamics CRM user as CRM application pool identity. Please refer below URL to know more about Security considerations for Microsoft Dynamics CRM 2011.

Then, I suggested her to add the above application pool identity user to PrivUserGroup. (One of the auto-generated Active Directory groups.) But, in our case the identity pool user was already added in this group. To give complete CRM database access, it can also be added to SQLAccessGroup, however, the situation was same, and the identity was already added here too.

Eventually, we switched to user impersonation. There were two options; either we impersonate the user inside the plugin or at SQL server level. I had asked her to implement the latter one and she tried with “Execute AS” inside the stored procedure and it worked as per the expectation. Below is our complete solution.

1. Add a new user in SQL server security logins, the security logins area is displayed in below screen shot.

OR

If you wish then you can use one of the existing users. However, this user must require sysadmin sever role to execute “Execute AS” command. Below link will help you to get more details on “Execute AS” (transact-SQL).

In plugin context you will always get the UserID via Context.UserID property, which is the GUID of the logged in CRM User. Use this value to retrieve the domain name of logged in CRM user from “systemuser” table. Keep this web service call in a common class/method inside your plugin solution, so that, the same can be reused in other plugin classes as well.

4. Pass the above domain name to your custom stored procedure as parameter which can be further used in “Execute AS” command, as shown in below stored procedure.

As per my knowledge, the stored procedure cannot be called from the QueryExpression. Also, if you're planning to use stored procedure for Dynamics CRM then make sure you create a separate database for it. Never, create SQL objects like SQL table, stored procedure, trigger, etc. on CRM SQL server.

About Me

Ankit Shah is highly motivated and experienced professional, Bachelor Science in Information Technology -B.Sc. (I.T.) securing more than 8 years of IT experience on software design and development.
Presently Ankit is working as a Dynamics CRM consultant at Inkey Solutions, Surat, India.