Вернуться   AXForum > Microsoft Dynamics CRM > Dynamics CRM: Blogs
Забыли пароль?
Зарегистрироваться Правила Справка Пользователи Сообщения за день Поиск Все разделы прочитаны

Опции темы Поиск в этой теме Опции просмотра
Старый 14.07.2008, 13:05   #1  
Blog bot is offline
Blog bot
23,918 / 805 (74) +++++++
Регистрация: 28.10.2006
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;
using System.Collections.Generic;
using System.Text;
using Microsoft.Crm.Sdk;
using Microsoft.Crm.SdkTypeProxy;
using System.Xml;
using System.Data.SqlClient;
using Microsoft.Crm.Sdk.Query;
public class AccessDatabase : IPlugin
   string m_secureConfig;
   string m_connectionString;
public string SecureConfig
      get { return m_secureConfig; }
      set { m_secureConfig = value; }
// Pass the connection string to the plug-in’s constructor.
   // The string is defined during plug-in registration.
   public AccessDatabase(string config, string secureConfig)
      m_connectionString = config;
      m_secureConfig = secureConfig;
   public void Execute(IPluginExecutionContext context)
// Step 1. Get the domain name of the calling user.
      ICrmService crmService = context.CreateCrmService(false);
      systemuser callingUser = (systemuser)crmService.Retrieve(
EntityName.systemuser.ToString(), context.UserId,
new ColumnSet(new string[] { "domainname" }));
      // Step 2. Connect using a SQL connection string specified in the
      // configuration of step
using (SqlConnection conn =
new SqlConnection(m_connectionString))
SqlCommand comm = conn.CreateCommand();
// Step3. Start SQL impersonation.
      comm.CommandText = @"Execute as Login='" +
         callingUser.domainname +"'; ";
      // Step 4. Run the SQL commands that need to be executed.
      comm.CommandText += "SELECT SUSER_NAME(); ";
// Step 5. Revert the context back to Network Service
      comm.CommandText += "revert;";
      comm.CommandType = System.Data.CommandType.Text;
// For demonstration purposes, display the username displayed
      // from the SELECT statement.
throw new InvalidPluginExecutionException(
For more information on the EXECUTE AS command, refer to
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
Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск
Опции просмотра

Ваши права в разделе
Вы не можете создавать новые темы
Вы не можете отвечать в темах
Вы не можете прикреплять вложения
Вы не можете редактировать свои сообщения

BB коды Вкл.
Смайлы Вкл.
[IMG] код Вкл.
HTML код Выкл.
Быстрый переход

Часовой пояс GMT +3, время: 11:51.
Powered by vBulletin® v3.8.5. Перевод: zCarot
Контактная информация, Реклама.