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 220.127.116.11. 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:
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=18.104.22.168, 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=22.214.171.124, 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 126.96.36.199 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:
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.
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.