Saturday, 29 June 2013

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.

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.



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

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]
ALTER PROCEDURE [dbo].[TestProcedure] 
    @UserName NVARCHAR(100)
        EXECUTE AS LOGIN =  @UserName 
        SELECT * FROM CRM_DB_Name.dbo.FilteredAccount

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.

Saturday, 1 June 2013

CRM 2011 On Premises - External Connector License


UPDATE: The External Connector License is obsoleted. The connector was removed from the pricelist in January 2014. From the quick reference guide:
"Access by external (third party) users is included with the Server License; you do not need CALs unless using Microsoft Dynamics CRM client applications. External users are users that are not either your or your affiliates’ employees, or your or your affiliates’ onsite contractors or onsite agents."


One of our customers, working on CRM 2011 on-premises, would like to provide the access of CRM data to external parties, like their service providers, customers and other contacts. At present, these people have been created as contacts in CRM. I was asked to prepare high level design, a work model that allows this group of people to access the Dynamics CRM data. I have prepared a design wherein we decided to give access to external parties via the existing web site of my customer. I have also explained that we require External Connector License for this implementation. However, as I never used it before, I would like to collect more details on External Connector License.

I thought once we purchase such uncommon license from Microsoft, they would allow us to assign it to one of the existing Dynamics CRM users and the credentials of that user can be consumed at web portal then to fetch/update CRM data. To validate my understanding, I put the same as question on different Dynamics CRM communities, forums and groups.

I found that my conception was wrong. The External Connector License is just a paper license, a trust license. It has nothing to do with the implementation. To validate the cost for this license, you can refer below link.

You could also refer the below links that help me to confirm it.

Note: The External Connector License is not applicable to Dynamics CRM 2011 online

Please feel free to update this post with your experience about External Connector License.