Dynamics CRM 2011 On-premises - Use “Execute AS” to call custom stored procedures

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,

Select * from DBO. Account

was replaced with,

Select {required columns } from OrganisationName_MSCRM.dbo.FilteredAccount

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.

http://technet.microsoft.com/en-us/library/hh699825.aspx

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).

http://msdn.microsoft.com/en-us/library/ms181362.aspx

2. Use the above SQL user in your connection string to connect with custom database inside CRM plugins, as described below.

Data Source=myServer;Initial Catalog=myDataBase;User Id=myUsername; Password=myPassword;Integrated Security=false

3. Retrieve the domain name of logged in CRM user.

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.

USE [DBName]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[TestProcedure] 

    @UserName NVARCHAR(100)

AS

BEGIN

    BEGIN TRY

        EXECUTE AS LOGIN =  @UserName 

        SELECT * FROM CRM_DB_Name.dbo.FilteredAccount

        REVERT;

    END TRY

    BEGIN CATCH

        REVERT;

    END CATCH

END

Note: Make sure you write the “REVERT” command to stop the user impersonation inside your stored procedure.

In this way we have impersonated the application pool identity user with the logged in CRM user. Thus, we can fetch the data from filtered views based on logged in user security roles.

I hope this would be helpful.