5

I'm having trouble accessing an external database from a CRM plugin. The error I receive is:

"Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=xxx' failed."

The code runs great locally within a "unit test". I made sure to set the plugin isolation mode to "none". I tried looking to this article for help, and tried everything it suggested with no luck.

Here is the current code I'm using:

var conn = new SqlConnection(@"Server=MyServer\Instance;DataBase=MyDB;User Id=MyUser;Password=MyPassword;Integrated Security=false;");
conn.Open();

I also tried this connection string and giving the NT AUTHORITY\NETWORK SERVICE user access to the database.

var conn = new SqlConnection(@"Data Source=MyDS\Instance;Initial Catalog=MyDB;Integrated Security=SSPI;");
conn.Open();

I'm on Dynamics CRM 2015 On-Premise.

Update: I found out it was working when I didn't debug, but I got the error when I try to debug it through the plugin registration tool. Any idea on why that would happen?

Community
  • 1
  • 1
Brandon Tull
  • 337
  • 4
  • 17
  • Please explain how you got it working. – Henk van Boeijen Feb 20 '16 at 22:21
  • 1
    The plugin registration tool has only limited debugging capabilities. It was designed for CRM Online, where you cannot use the debugging options of Visual Studio. In OnPremise deployments use either remote debugging or install Visual Studio on the CRM Server. The last one is the recommended approach. – Henk van Boeijen Feb 20 '16 at 22:26
  • @HenkvanBoeijen It was working all along. I successfully tested it by reading a record from external SQL and writing it to an entity field without debugging. Can you put your second comment as an answer so I can mark it as the answer? – Brandon Tull Feb 21 '16 at 02:35

3 Answers3

3

A SQL connection will require "full trust" to establish which the CRM plugin sandbox does not run within.

We run CRM 2013 On-Premise and I frequently make calls to external databases within custom plugins and workflows, but to overcome the security issues - I created a web service which handles these requests.

For example, a call to update a record in DB2 when an account is updated would work like this:

  1. Account record updated in CRM
  2. Account plugin fired
    1. Establish connection to MyCompanyWebService
    2. Call UpdateDB2 (method within MyCompanyWebService)

Of course you have the overhead of having to develop a separate web service, but (on the bright side) it allows you to separate the logic and you can fully control the trust level within your web service.

Jason Faulkner
  • 6,378
  • 2
  • 28
  • 33
  • Thank you for the reply. I was hoping by running the plugin outside of the sandbox (isolation mode="none") it would prevent me from having to do that. Creating a web service is my last option, but I will if that's the only way to call an external database. – Brandon Tull Feb 18 '16 at 19:43
  • @BrandonTull - I went through the _exact_ same steps you did in your original question before ultimately coming to the conclusion that a WS was the best (really only viable) solution. It has actually proven to be a good thing because we leverage that same WS to expose the functionality to other integration points as well. – Jason Faulkner Feb 18 '16 at 20:34
  • I understand.. The downside is I will have to manage another web service, and I won't have the raw speed of doing a simple SQL call. – Brandon Tull Feb 18 '16 at 21:43
  • @BrandonTull - Unfortunately it is just a cost of doing business. For what it is worth, if all the traffic between CRM and your WS is local then overhead should be pretty minimal; at least that is my experience. – Jason Faulkner Feb 18 '16 at 22:03
  • 1
    @JasonFaulkner Your answer and comments suggest this isn't possible. However, as long as the plugin is running outside sandbox which the OP says it is, there is no reason a direct connection to the SQL database would not work. The reason this isn't working for the OP will be an environment issue but this isn't a limitation of CRM. – Darren Lewis Feb 18 '16 at 23:01
  • @DarrenLewis - I never could get it to work within the sandbox due to the trust level required. Deploying through the GAC (/disk) is an option, but that potentially requires IT to become involved because you need admin level rights to the server to deploy (not to mention it is a pain to deploy updates to). – Jason Faulkner Feb 19 '16 at 14:11
  • @DarrenLewis It was actually working... See my update in the original post. For some reason it doesn't work when I debug it through the plugin registration tool. – Brandon Tull Feb 19 '16 at 22:18
  • @JasonFaulkner You can deploy Full Trust (IsolationMode=None) plugin assemblies to the database. They don't need to be deployed to disk although you do then need to be a Deployment Admin rather than just CRM System Administrator for Sandbox. Full Trust gives you access to all network protocols (winsock tcp in this questions case for SQL) plus verious other features, unlike a sandbox plugin that only has access to http, https and certain Azure service endpoints. – Darren Lewis Feb 19 '16 at 22:44
2

The plugin registration tool has only limited debugging capabilities. It was designed for CRM Online, where you cannot use the debugging options of Visual Studio. In OnPremise deployments use either remote debugging or install Visual Studio on the CRM Server. The last one is the recommended approach.

Henk van Boeijen
  • 7,357
  • 6
  • 32
  • 42
0

That issue looks like your code is running in partial trust (Sandbox), it's failing even before trying to connect to the SQL Server instance as it doesn't have permissions to instantiate a SqlClient.

In Dynamics CRM 2015 On Premise you don't have to run plugins in Sandbox if you don't want to. Sandbox is a requirement for Dynamics CRM online only.

Did you try running outside the Sandbox? Did you do an iisreset after changing the plugin isolation maybe?

Here is an article with more details.

Jordi
  • 1,460
  • 9
  • 9
  • I have it set to run out of the sandbox (isolation mode to none). I didn't try an iisreset. I'll do that and report back. – Brandon Tull Feb 19 '16 at 14:19
  • I am back with an update. After much effort I found out that the Sql connection is working, but it does not work if I debug it through the plugin registration tool.. I get the "...SqlClientPermission...Failed" error. Any ideas on that one? – Brandon Tull Feb 19 '16 at 22:15