Avatar of GaryRasmussen
GaryRasmussen
Flag 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?
Microsoft Dynamics

Avatar of undefined
Last Comment
GaryRasmussen

8/22/2022 - Mon
Chinmay Patel

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.
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?
Chinmay Patel

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
GaryRasmussen

ASKER
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
Chinmay Patel

CRM and this Web Service are on the same server?
GaryRasmussen

ASKER
With CRM online?  Is that possible?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Chinmay Patel

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?
GaryRasmussen

ASKER
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
GaryRasmussen

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
GaryRasmussen

ASKER
I figured out what the problem was.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck