Microsoft Dynamics CRM Team Blog: Accessing a SQL Database from a Microsoft Dynamics CRM Plug-in

[Blog bot]

Источник: http://blogs.msdn.com/crm/archive/20...m-plug-in.aspx
==============

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))
      {
      conn.Open();
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(
         comm.ExecuteScalar().ToString());
      }
   }
}

For more information on the EXECUTE AS command, refer to http://msdn.microsoft.com/en-us/library/ms181362.aspx.
Cheers,
Ajith Gande and Peter Hecke


Источник: http://blogs.msdn.com/crm/archive/20...m-plug-in.aspx