MySQL, .NET 4 Entity Framework and Medium Trust hosting environments

Recently I was trying to setup Entity Framework for a .NET 4 application on a MySQL database for a GoDaddy site that used shared hosting. In this environment applications have medium trust which can make things very frustrating as applications will work perfectly fine locally but will crash miserably when deployed with security exceptions. After pulling my hair out for several days I finally managed to get everything working smoothly. So smoothly in fact that after being able to get a simple test application working that connected directly to the database working I managed to get Entity Framework working within minutes. In this post I’ll explain some of the issues we came across and how we over came them. In addition I’ll include some sample source code and test apps for you to try.

Connecting to MySQL using .NET

In order to connect to a MySQL from .NET you will need to use the MySQL Connector for .NET. At the time of writing this article the latest version is 6.3.4.0. Before you go and get it the latest version doesn’t seem to work in the GoDaddy hosting environment, however I have found a solution. When I first started using this version of the .NET connector I jump right in and setup entity framework and created my web service that wanted, got everything working locally only to find that when I deployed it to my hosting environment that I was always getting the following error:

System.Security.SecurityException: That assembly does not allow partially trusted callers.

After some research I found several posts that explained the issue. Basically the .NET connector is not set to run in a medium trusted environment and in order to get around this you had to download the source code for the .NET connector and alter the Assembly.cs file in the MySQL.Data project by adding the following:

using System.Security;
[assembly: AllowPartiallyTrustedCallers]

After doing this you had to recompile the project and use the new dll assembly in your project. After doing this the above exception disappeared, unfortunately I ran into a new error:

MySql.Data.MySqlClient.MySqlException: Unable to connect to any of the specified MySQL hosts —> System.Security.SecurityException: Request for the permission of type ‘System.Net.SocketPermission, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’ failed

This error became a big road block. I spent a couple days trying different things and got nowhere. I reached out to the GoDaddy technical support and most of the responses where either copy and pasted from their help pages or made unhelpful suggestions such as using MS SQL instead or upgrading to a virtual dedicated or dedicated server. Refusing to except this as an answer I kept at it and created a simple test application that simply connected directly to my database and grabbed everything in the one table I had setup. Eventually I got passed this error only to be come across yet another one that stumped me for a while.

[SecurityException: Request for the permission of type ‘System.Security.Permissions.SecurityPermission, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’ failed.] MySql.Data.MySqlClient.MySqlTrace..cctor() +0

After looking around on the web and seeing lots of post of people who have done pretty much what I outlined above to get their connection working I started to wonder what it was that made my application so much different from those that managed to get theirs working. Most of the posts I came a cross where two or more years old and that got me thinking that perhaps it has something to do with the version of the .NET connector I’m working with. So after another quick search around I found a post that linked to an older version of the .NET connector (version 6.0.7.0 http://dev.mysql.com/downloads/connector/net/6.0.html). I grabbed the source code and put in the partial trust information into the Assembly.cs file, recompiled it and referenced it in my project. I ran a test locally and it worked which wasn’t that big of a deal as most of my other tests work locally. I then pushed my latest version of my code up onto my hosting environment and tested it. It worked!!! My test application was grabbing my data and displaying it on the screen. You can download a modified version of MySQL.Data.dll assemblies here: https://onedrive.live.com/redir?resid=D35222484A76A01%21381362

Now for the Entity Framework

I already had my Entity Data Model created so I simply tried adding in this recompiled assembly and redeployed. Right away their was an exception that basically said I was missing a reference to MySQL.Data.Entity. Not a problem, I opened up the source code I down loaded for the .NET connector, added in the partial trust parameters to the Assembly.cs file of the MySQL.Data.Entity project, recompiled and added it to my project. I then redeployed again to find a new error:

[ArgumentException: Unable to find the requested .Net Framework Data Provider.  
It may not be installed.]
   System.Data.Common.DbProviderFactories.GetFactory(String providerInvariantName) +1402071
   System.Data.EntityClient.EntityConnection.GetFactory(String providerString) +35

Right away I recognized the DbProviderFactories class from one of the forum posts I had read and remembered that I had to add something to the web.config. After a bit of searching I found it and added the following to my web.config file:

<system.data>
<DbProviderFactories> <remove invariant=”MySql.Data.MySqlClient” />
<add name=”MySQL Data Provider” invariant=”MySql.Data.MySqlClient” description=”.Net Framework Data Provider for MySQL” type=”MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data,Version=6.0.7,Culture=neutral” />

</DbProviderFactories>

</system.data>

I then updated the web.config on the server and tested my application to find it was working now. The recompiled MySQL.Data.Entity assembly is also included in the test application.

Summary

So after several frustrating days I managed to get Entity Framework in .NET 4 to work with a MySQL database in a medium trusted hosting environment. I’ve left out the details on how to create a database and how to create an project with Entity Framework as there are plenty of good articles already that cover those topics.

Advertisements

17 thoughts on “MySQL, .NET 4 Entity Framework and Medium Trust hosting environments

  1. Thank you for publishing your findings, it helped me a great deal. I did take a different approach in trying to stay with the current connector version (6.3.5), it already had the [assembly: AllowPartiallyTrustedCallers] in AssemblyInfo but that didn’t help, so after adding the project to my solution, I added so that my localhost acts similar to my medium trust production box. Once I finally got passed all the other issues you mentioned, I got to the SecurityException. I then simply commented out the logic in the MySqlTrace constructor and bingo, it worked. It seems like that ctor does some reflection work which I believe is limited in medium trust by default. Not sure if there are going to be any side affects to this but as I go clearing out all that trace code to lighten the load, I will run some more tests to find out.

    • Ricky: Thank you so much for this post. I have been struggling with this issue for quite some time now– so this was a life saver!

      Walter: Could you please post a link to your 6.3.5. DLL? I’d like to use that version if possible.

  2. Sorry, I didn’t realize that WordPress would strip out my web.config code example, where I said, “I added so that my localhost”, I actually said “I added ‘trust level=”Medium”‘ to my web.config so that my localhost acts similar to my medium trust production box.”

    • Did Walter post the dll’s? I’ve about flatten my head by beating it against the wall looking for an easy solution. Working with the source files is also a pain because it’s missing references to the installer… Then I’m on a wild goose chase looking for a Bazaar source control application to download the branch etc.. I’m gonna lose it.

      • Jess,

        Let me know if you find the 6.3.5 dlls. I’m still using the ones in this post, but would like to use a more current version.

        Thanks,
        Don

  3. I tried with latest version of MYSQL & it worked. Other than steps mentioned above, you need to put your latest MYSQL connector dll’s in bin directory.

  4. How did you get past the socketPermision error? I get this error when running on my local machine with medium trust and your SimpleDBConnection application

    Request for the permission of type ‘System.Net.SocketPermission, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’ failed.”}

  5. Thanks for your post..

    I have one problem with us. Now Mysql work fine with select query but when I am going for insert record in database then it show me following message :

    Request for the permission of type ‘System.Security.Permissions.ReflectionPermission, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’ failed.

    Please suggest me is anything I have missed here..

      • Thanks SimpleDBConnection.zip works amazingly but can you post SimpleDBConnection with EF, I mean I need Mysql.Data.Entity.dll also which can work at Godaddy

      • This is a really old post. I’ve moved all my databases from GoDaddy to Windows Azure a while ago. Azure is much faster and it’s a lot easier working with SQL Azure. Especially if you are using Entity Framework.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s