Connect to MySQL with .NET 2.0

The_Spook · January 3, 2006, 3:05am

I was recently moved to a server that supports ASP.NET 2.0, and I’m trying to connect to my mysql databases with ASP.NET 2.0. I was able to do it quite easily with .NET 1.1, but it’s no longer working since the switch.

Take this script for example:

[INDENT][FONT=“Courier New”][SIZE=“2”]<%@ Page Language=“C#” Debug=“true” %>
<%@ Import Namespace=“MySql.Data.MySqlClient” %>

Hello World! [/SIZE][/FONT][/INDENT]

When I run this script, it gives me the following error on the “MySqlConnection conn …” line:
“System.Security.SecurityException: That assembly does not allow partially trusted callers.”

I copied the latest MySQL Connector/Net to my /bin folder before running. Can anyone tell me what I’m doing wrong and/or offer a solution for connecting to MySQL from Jodo+ASP.NET 2.0?

antic · January 3, 2006, 5:19am

Might be worth searching MSDN or googling it. If you find anything, please post back here.

The_Spook · January 3, 2006, 2:42pm

For the record, this code works fine on my home machine (WinXP Pro/IIS/MySQL 4.0.26/.NET 2.0).

The_Spook · January 7, 2006, 7:05am

It seems to me it is impossible to connect to MySQL from ASP.NET through Jodo

I tried connecting via the MySQL connector, and received the following error: “System.Security.SecurityException: That assembly does not allow partially trusted callers.”

I also tried connecting via ODBC and received the following error: “System.Security.SecurityException: Request for the permission of type ‘System.Data.Odbc.OdbcPermission’ failed.”

It seems to me to be a permission problem. I sent a help request to Jodo, but their reply was:

“Your application needs ‘full trust’ that you dont get in shared hosting environment. Please modify your code for ‘medium trust’ and it will work fine. I am no programmer, so cant give you the exact implementation details.”

I am not sure this is possible with MySQL Connector/NET. I’m told full trust demands are imposed on most (or all?) of the classes in the System.Data.Odbc namespace. I will consider re-writing my pages for MS SQL, but it is unfortunate that a MySQL option cannot be provided.

antic · January 7, 2006, 7:22am

I’d be slow to say it can’t be provided… have a look around some developer sites, I’m certain it’s possible.
I searched on Google with the phrase “connecting to mysql with asp.net 2.0” and came up with the following articles which may help you.

15 Seconds : Using MySQL with ASP.NET

ASP .NET connect to MySQL using ADO .NET

Instead of using an ODBC connection, you should either use OleDbConnection or MySqlConnection. Refer to this site for connection strings:
http://www.connectionstrings.com/

You could also try the classic ADO wrapper for .NET in case that helps. Personally I always use it - I hate the ADO.NET data controls. You’ll need to put the file adodb.dll into your /bin folder.

Hope that helps. Try the classic ADO option, it just might work.

Stephen · January 7, 2006, 12:49pm

It seems that all but one connection method is disabled in all but full trust by default accorind go another article, I see no issue with alternative connection methods and will be enabling the other methods in the next 24 hours.

aggie · January 7, 2006, 8:58pm

TheSpook: What did you use to connect using 1.1?

I haven’t been able to connect to the db here. I’m using Odbc in my code and the connection string like this one:
“DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=myDatabase;USER=myUsername;PASSWORD=myPassword;OPTION=3;”

The values I use for database, user and password are setup correctly, so that’s not the problem.

Did you use localhost as the server or the ip of the server?

Thanks.

The_Spook · January 7, 2006, 9:52pm

aggie -

To connect to MySQL using ASP.NET 1.1, I used the MySQL Connector/NET:
http://dev.mysql.com/downloads/connector/odbc/3.51.html

This has always worked well for me, including when my site was hosted on Jodo. Just copy the MySql.Data.dll to your /bin folder and you’re ready to go. Too bad the same doesn’t work with .NET 2.0.

Here’s some example code, although you will probably have to change the connection string some:

[CODE]<%@ Page Language=“C#” %>
<%@ Import Namespace=“System.Data” %>
<%@ Import Namespace=“MySql.Data.MySqlClient” %>

[/CODE]

I’m not sure localhost would work, since I believe the databases on Jodo are hosted on different servers. You can find the IP for the database server from HSphere.

Using the ODBC driver should work, too. Perhaps it’s just the server that is incorrect (assuming the server you are on has the {MySQL ODBC 3.51 Driver} installed - have you asked them?) What error(s) do you get?

aggie · January 7, 2006, 9:56pm

I actually don’t get any error. But the database is not updated.

I guess I’ll have to check if there is an exception catched somewhere. I’m moving the site from another server. Just thought I would change the connection string and it would work, but it isn’t.

I’ll try what you tell me. Hope it works with that. Thanks for your help.

Anybody know ifs the ODBC driver is installed on the servers?

Stephen · January 7, 2006, 10:55pm

You need to use the MySQL IP, as localhost has no MySQL or SQL server.

As for the ODBc method or OLE method, they are currently disabled as per my previous reply, but will be enabled manually in the coming hours.

aggie · January 7, 2006, 11:12pm

Thanks for your help guys.

aggie · January 9, 2006, 7:38am

Hello,

has the odbc drivers been enabled?
I haven’t been able to connect to the db.

The_Spook · January 9, 2006, 2:33pm

Same here. This lack of support for other data providers is disappointing, especially since my site has been down since the move as a result. I could convert my data to MS SQL server, but for my particular needs, I prefer to use MySQL. Support tells me I need to update my code so it runs in medium trust, however, I haven’t figured out how to do that thus far (if it’s even possible with the current config?).

http://support.jodohost.com/showthread.php?t=6603

I haven’t tried the adodb.dll option antic suggested yet though. It would be nice if the MySQL Connector/NET would work, or even ODBC.

aggie · January 9, 2006, 5:41pm

This particular project is using ASP.NET 1.1.
Wonder if the trust settings are affecting this also. I’m now starting to panic, as it is crucial that this gets working by Thursday.

Has anybody been able to connect to MySQL with ASP.NET? If so, how?

The_Spook · January 9, 2006, 5:53pm

That’s strange. I have had no problems with connecting to MySQL with ASP.NET when I was on the 1.1 server (using the code I sent in an above post). When I was moved to the 2.0 server, though, everything broke (and is still broken - haven’t decided what I want to do from here).

No errors, huh? The code just runs but the database appears untouched? Can you select and display records?

aggie · January 9, 2006, 5:57pm

I’m going to give it more tries in a bit. I just hadn’t had much time for testing. But the code I used to use isn’t working.
Since Stephen said odbc would be enabled I thought I’d wait before changing any code.

I’ll change to use your code, hopefully that will work for me also.

Thanks.

aggie · January 10, 2006, 7:36am

I’ve tried using the Connector. It works on my development computer, but it doesn’t when I upload it to the server.
Is somebody able to connect to mysql using ASP.NET at all?

aggie · January 10, 2006, 10:24pm

Is the ticket system not working today?
I tried to log in to check the status of my ticket, and I get a Not Found.

I’m still trying to get the ASP.NET pages to connect to MySQL with no luck.

I appreciate any help anybody can give me on this.

scottsprize · January 11, 2006, 5:39pm

I was having the same issue. I would develop things on my localhost and it would work fine (even pull data from the Jodohost MySql server). When I uploaded it I got the dreaded Security Exception. I posted a ticket last night around midnight CST and have yet to receive a response.

Consequently, I am impatient and a problem solver so I did some homework. The issue appears to be that JodoHost has it’s clients set up as Medium trust or lower for ASP.NET 2.0. Unfortunately, that cuts off most database access except for SQL Server. To give us access to MySql (or any other ODBC compliant database) JodoHost’s trust policy needs to be modified. (see Technical documentation | Microsoft Learn )

In your default web.config file (located in C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG) you will have a line like this:

(When you develop on your localhost box the default level is “Full” which inherits security from Windows and therefore grants you all permissions. If you want to immitate JodoHost, change it from “Full” to “Medium” and save the web.config file. Refresh your data driven page and see what happens.)

Other built-in possibilities for the “level” attribute are Full, High, Low, and Minimal. These should be defined in the section of the web.config file. As part of the definition it also shows which policy file it will use (e.g. - for trustLevel Medium the policyFile=“web_mediumtrust.config”). This, and all other policy configuration files will be located in C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG.

To give us access to the Odbc classes and allow us to connect to MySql through ASP.NET 2.0 the following lines need to be added to the web_XXXXXtrust.config file…

Add the following to the node:

Add the following to the node:

I have a feeling this is important to many customers, including myself.

scottsprize · January 11, 2006, 6:32pm

Just received a response from JodoHost. They are analyzing the ASP.NET security structure. They said it may take some time because it is a matter of server security, which makes sense. I will keep you posted.