Link to home
Start Free TrialLog in
Avatar of GaryRasmussen
GaryRasmussenFlag for United States of America

asked on

CRM 2011 Plugin Sandbox SqlClientPermission

I have a requirement to be able to communicate with a remote SQL Server database from inside a plugin.  When I do this, I get an error:

The action that failed was: Demand
The type of the first permission that failed was: System.Data.SqlClient.SqlClientPermission
The Zone of the assembly that failed was: MyComputer

Evidently because my plugin has to run in isolation mode, CRM is preventing my code from communicating with my own database.  MS white papers say ytou can't communicate with a remote SQL Server from a plugin running in SabdBox mode.  I do not have the option to change the isolation mode.  What are my options?
Avatar of Chinmay Patel
Chinmay Patel
Flag of India image

Hi GaryRasmussen,

You could build a web service and communicate to your DB via that web service.
You could make a simple web page which accepts a querystring and writes result as output


Regards,
Chinmay.
Avatar of GaryRasmussen

ASKER

I would go the web service route if this was only 1 instance but I need this to be a solution where each customer has their own CRM instance and their own SQL Server and I have the plugin code talk to the remote SQL Server.

With a web service or a web page, each customer wouold have to have a web presence or web server somewhere.

Why does SQL Server care that my code is running in isolation mode if I am specifying the SQL user credentials?  I could see that happening with Windows Credentials but don't the specified user credentials indicate that it is ok?
it is not SQL Server... 'cause Isolation mode doesn't allow execution of code beyond a set of privileges you are facing this issue. It is not SQL but .Net framework and the way it was designed to provide security features.

Also if your SQL DB is on the same server and you try to use 127.0.0.1 then Isolation mode won't allow it.
Isolation mode won't let it no matter how I specify the server.  This is pretty frustrating, been at this all day.

I wrote a web service and now my WS is doing all the SQL work but now when I run the WS from the plugin, I get a new permission error.  Do I have to do something in CRM to make this possible?  Thanks!

The action that failed was: Demand
The type of the first permission that failed was: System.Net.WebPermission
The Zone of the assembly that failed was: MyComputer
CRM and this Web Service are on the same server?
With CRM online?  Is that possible?
No. Now just to validate the scenario :
You are executing your plugin in CRM online and you have exposed a web service on your own server right?
I am only in the development stage at the moment.  So for dev, I have a single Windows 2008 Server with SQL Server 2008 and CRM 2011 on premise all installed on the same box.

I created a plugin (registered in isolation mode) in a custom CRM solution that looks up some field values on a CRM entity and passes that information as parameters to a stored procedure in a custom database on the SQL Server.  It gives me an error that I cannot do that because of the Isolation mode.

So I created a new website in IIS on the same box.  Then I created a web service and a web method that receives a string of XML as an argument, parses the XML, and then passes that information as parameters to the same stored procedure.   It gives me an error that I cannot do that because of the Isolation mode.

I need to get information from CRM to SQL so that I can take advantage of SQL Analysis Server and I need to do that when a CRM entity changes so a plugin is the perfect place to do this however this has to work for on premise as well as online so I need a single solution that works for both.

I don't want to require the customer to have a website where they can store the web service.  If I hosted the web service for each customer, then each customer would basically have to give me the username and pasword of a user on their SQL Server and there is no way they are going to go for that nor would I even consider asking.

So I am faced with a requirement that if I can't find a solution is a deal breaker with this project.  Is there any way to do this that you can think of?  I read one post where someone suggested adding a proxy service to the SQL Server that would allow code execution from code in isolation mode but that was for SharePoint and there are SharePoint proxy service APIs out there but I did not find anything for CRM.
ASKER CERTIFIED SOLUTION
Avatar of GaryRasmussen
GaryRasmussen
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I figured out what the problem was.