Microsoft Dynamics CRM Team Blog: Accessing a SQL Database from a Microsoft Dynamics CRM Plug-in
Have you ever had the need to access data in a non-CRM SQL database from within a plug-in? Let’s say that you register a plug-in with Microsoft Dynamics CRM that will pull additional data from another SQL database in order to pre-populate a newly created entity’s attributes or perform some calculation using the data from both databases.
The problem that you will run into is that the system account that the plug-in executes under needs to have login and data access to the SQL server and database, which is not enabled by default. In Microsoft Dynamics CRM, all plug-ins execute under the system account named “NT AUTHORITY\NETWORK SERVICE”. If you take a look at any Microsoft Dynamics CRM database, you will see that a login exists for the NETWORK SERVICE account.
Your SQL server administrator will need to create a SQL server login and assign database access permissions and roles for the NETWORK SERVICE account in order for your plug-in to be able to access the SQL database. Once this is configured you can connect to the database using a trusted connection string.
Data Source=myServer;Initial Catalog=myDataBase;Integrated Security=SSPI;
An alternate approach to creating a SQL server login account is to have your plug-in establish a connection to the SQL server using a connection string which includes login information. For example:
Data Source=myServer;Initial Catalog=myDataBase;User Id=myUsername; Password=myPassword;Integrated Security=false
Note that you must use Integrated Security=false and not Integrated Security=SSPI. This method has the disadvantage of sending login information in clear text over the network, which is less secure. You are also going to have to either hardcode the login information in the plug-in or pass the information to the plug-in’s constructor at run-time. For more information on how to pass data to a plug-in at run-time, refer to the Microsoft Dynamics CRM 4.0 SDK documentation under the topic Writing the Plug-in Constructor.
How to Execute SQL Commands from a Plug-in using Impersonation
Sometimes you may need to execute SQL stored procedures or SQL commands in the context of the user who caused a plug-in to execute instead of the Network Service system user. You can achieve this using the Execute AS command in SQL.
The NT AUTHORITY\NETWORK SERVICE login (see previous figure), or the user ID used to connect from the plug-in without using Integrated authentication, in the SQL database should be granted the sysadmin role in order for impersonation to work.
The following steps describe the process that a plug-in should implement.
1. Retrieve the domain name of the caller from Microsoft Dynamics CRM through the CrmService Web service. The systemuser entity contains domain information. You can execute a Retrieve on that entity to obtain the information.
2. Create the SQL connection to the target SQL database using the connection string specified in the secure or unsecure configuration attribute of the step. You can use integrated authentication or a hard coded SQL connection string as explained in the previous section of this blog.
3. Start the impersonation as the caller.
4. Execute any SQL commands or stored procedure that you want.
5. Revert the SQL execution context back to the Network Service system user.
The following plug-in sample code implements the previously described steps.
using System;For more information on the EXECUTE AS command, refer to http://msdn.microsoft.com/en-us/library/ms181362.aspx.
Ajith Gande and Peter Hecke
Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору.
|Microsoft Dynamics CRM Team Blog: List Web Part for Microsoft Dynamics CRM 4.0 Deployment Scenarios||Blog bot||Dynamics CRM: Blogs||0||30.01.2009 22:05|
|Microsoft Dynamics CRM Team Blog: Microsoft Dynamics CRM 4.0 Bookshelf||Blog bot||Dynamics CRM: Blogs||1||22.01.2009 04:46|
|Microsoft Dynamics CRM Team Blog: Auditing Report Execution using the ReportServer Database||Blog bot||Dynamics CRM: Blogs||0||19.11.2008 20:05|
|Microsoft Dynamics CRM Team Blog: Microsoft Dynamics CRM Plug-in Template for Visual Studio||Blog bot||Dynamics CRM: Blogs||0||27.10.2008 23:05|
|Microsoft Dynamics CRM Team Blog: Top 14 Microsoft Dynamics CRM Sites and Downloads||Blog bot||Dynamics CRM: Blogs||0||14.07.2008 13:05|
|Опции темы||Поиск в этой теме|