Saturday, February 13, 2010

Authentication and Impersonation between CRM database and External databases

Sometime we may need to create stored procedures and views to access CRM data for faster retrieval and processing. To do so, we have to use an external database to store all stored procs and views since Microsoft does not recommend to keep our stored procs, functions and views in CRM database.

For me, I am using an external database for THREE obvious reasons:
  1. Processing very complex business logic through stored procs
  2. Recording data changes for future reference (for example; audit data)
  3. To retrieve data using impersonation (through plug-in and/or C# code)

Below is the steps you may need to configure if you want authentication and impersonation between CRM database and external databases.(In this example, I am using "crm admin" account as a service account to do authentication and impersonation.)

In SQL Server Management Studio:

1. SQL Server >> Security >> Login >>
    Create domain\crmadmin in account MS SQL Server

2. SQL Server >> Security >> Login >> Select User properties >> Server Role >>
    Give domain\crmadmin to sysadmin server role

3. CRM/Extenal Database >> Security >> Login >>
    Add domain\crmadmin account and domain\ReportingGroup {GUID} account into orgname_CRM database and external database

4. SQL Server >> Security >> Login >> Select User properties >> User Mapping >>
    Map domain\crmadmin account to THREE databases (MSCRM_CONFIG, orgname_CRM, External database) and give database role to db_owner and public

5. SQL Server >> Security >> Login >> Select User properties >> User Mapping >>
    Map domain\ReportingGroup {GUID} account to TWO databases (orgname_CRM, External database) and give database role to public
6. Extenal Database >> Database Properties >> Permissions >>
    Choose domain\ReportingGroup {GUID} accountand give explicit permission to GRANT CONNECT, EXECUTE and SELECT

7. Run grant impersonation of crmadmin to reporting group

USE [master]

grant impersonate on login :: [domain\crmadmin] to [domain\ReportingGroup {GUID}]

8. Use EXEC AS LOGIN = 'domain\crmadin' for crmadmin impersonation if necessary

9. Use REVERT after every impersonation

Tips for development:
  1. To make your live easier in writing and deploying SQL scripts, please use my MSSQL Synonym Generator to create synonym names for you.
  2. If your CRM plug-ins need to access data from SQL server through impersonation, please use stored procedure and defined you impersonation code there. (You cannot pass through EXEC AS LOGIN, Stored Proc. and REVERT commands together in sqlcommand.commandText)
  3. If your environment does not have enough CALs to access remote desktop to use SQL server, you can refer EXEC AS LOGIN command to access SQL data to through your laptop/PC.

No comments:

Post a Comment