In the summer of 2008, I volunteered to help out a local political organization Get Out The Vote this election year. There is an old saying that goes something along the lines of, “No good deed goes unpunished”
I wish I’d remembered that before I started all this!
Looking for System.Security.SecurityException: That assembly does not allow partially trusted callers? click here.
But, if you’d like to read a good story, carry on!
The project involved creating a .NET based website that was to leverage some data stored in a MS SQL Database.
“Easy enough,” I thought at first. After all, I’ve been creating websites using these tools for several years now.
.NET provides several tools for interacting with a MS SQL Server database. I opted for the ADO.NET since they work well, and I have lots of experience using them. I managed to create my web application and database and imported all the raw data that we would need for the project. So far, so good.
We then purchased our shared Windows hosting account with GoDaddy.com (the same
company that is hosting this site). I have another site site using this exact same setup. This setup is working well and I’ve experienced no problems whatsoever. I uploaded the application and prepared to create and populate the database.
This is where things started to unravel.
My database was large. The table that held the bulk of the data has over 500 thousand records. MS SQL Databases on the GoDaddy system can import records via CSV files (CSV files are “comma separated value” files; plain text files that can be used to represent a lost of data).
I tried to upload the large CSV file, but kept getting time-out errors using the standard web-based file upload process.
I then made the first of many unsuccessful calls to GoDaddy Tech Support. The techie on the other end of the line instructed me to upload a database backup into a special folder in my account’s file system. I uploaded the backup (over 500MB in size!) and then tried to initiate a restore of the database per the instructions in GoDaddy’s help system.
Shortly, after I had started the restore, I went back to check the progress, only to find that the restore had failed. The reason given was that a MS SQL Restore can only be performed using backup files that are created by the GoDaddy system. It seems that there is a MS Security bulletin out on this. But I guess that the Tech Support staff did not know this.
The second call to Tech Support taught me about the Database Publishing Wizard produced by Microsoft. I downloaded this wizard and began to publish my data.
This, too, failed.
It seems that there is a limit on the physical size of database available to Windows shared hosting accounts. The limit, it seems is 200MB. My main data table is over 200MB by itself. The problem, is that I was not made aware of this limitation at any point during the account creating process. And this limit was not visible on any of the sign up screens that I came across when setting up the account.
Another call to tech support informed me of the 200 MB limit. The Techie’s only solution was to transfer to a dedicated hosting account. Exept that because of the database size, we would need a full MS SQL license fo our site.
Did I mention that this work is for a non-profit organization?
So the dedicated hosting with a full MS SQL Server license was out of the question.
The tech support person informed me that there is no limit on the size of a MySQL database (actually there is, but it is the size of the actual account space; mutiple GBs).
Conversion to MySQL
After much cursing and swearing, I got down to the business of downloading and installing MySQL on my development laptop (as if I needed anything else slowing things down on my old laptop). I ran the Database import/conversion wizard and copied all my data into my brand spankin’ new MySQL datbabase. But, it seems, none of the stored procedures were duplicated.
I then exported my MS SQL stored procedures into a text based SQL file. I opened the text/sql file and tried to execute the SQL in MySQL only to find that there are significant differences in how MySQL interprets SQL, when compared to MS SQL.
So I had to re-write approximately 50 stored procedures, using MySQL syntax. Did I mention that I had not used MySQL before?
After I finished re-writing the stored procs, I then had to convert my web app to make use of the new data source. Fortuntately, I had all my database functions in a single file. Even better, there were only four functions that needed to be modified.
So I downloaded the MySQL ASP.NET database connector/DLL and rewrote my application. Initial testing identified about 6 places where I needed to “tweak” some code. And then voila it worked!!
So, I then created a database backup and uploaded the backup file (~250MB) to GoDaddy and then initiated a Restore operation. It eventually ran successfully. I ran some test using the MyPHPAdmin application. (MyPHPAdmin runs slower than molasses in winter on the GoDaddy servers, of course).
ASP.NET Login Controls
Because this application has restrictions on who has access to what, I wrote the application using the .NET login controls. They use a database to track users,
permissions, etc. But…. you will recall the we moved our database over to
Fortunately, GoDaddy allows you to have both a MS SQL Database and multiple
MySQL databases. So I then set about learning how to use a SQL database as my
Authentication provider’s backing store. Another two days later, and I was able to get the accounts logging in, resetting passwords, etc.
I then uploaded my application and tried to perform some of the tasks that this application was designed to do.
MySQL .NET Connector
The .NET connector that MySQL makes available requires “full trust” in order to run. But the shared hosting environment is a “medium trust” environment so the connection would not work. I downloaded a patch and recompiled the .NET connector so that it allows a medium trust application to use the connector.
So I uploaded the new connector’s DLL and executed some AdHoc queries. Success! Or so I thought. I then tried to use the application’s pages which called on some of my stored procedures (which I discussed above).
No go. Again.
The .NET connector now worked using ad hoc queries, but required admin rights in order to execute stored procedures. Obviously, GoDaddy was not about to give my account admin rights. So, all seemed lost. Again.
But, I then realized that I had been able to use AdHoc queries to call my stored procedures. So, I re-wrote my database access class to create SQL statements that get executed as AdHoc queries, and FINALLY, I was able to get the application to work as designed.
Well, not as designed, but as originally intended.
I had originally designed my application to use Crystal Reports to generate PDF outputs. But (surprise!) GoDaddy does not make Crystal Reports available on their servers. So Crystal Reports is out.
I then found the iTextSharp library which can generate PDF files from C# code. But, the reports has to be created in code, not in a designer, as are the Crystal Reports outputs.
So five days of coding later, and I was able to generate my reports using the iTextSharp library. Almost…
When I first tried to create some reports I got an error telling me that I did not have permissions to write to my report output folder. I then tried deleting the folder and having the application create the folder. Still no-go.
Finally, I found the “Permission” page on the GoDaddy site and gave my output folder “Write” permissions. Good to go? Well, not quite.
When I next tried to run a report, I got error messages stating:
System.Security.SecurityException: That assembly does not allow partially trusted callers
in that ugly yellow colored window that we’ve all come to hate. After some deft Googling, I determined that the iTextSharp library would need to be re-compiled so that Partially-Trusted callers would be able to use the iTextSharp library.
Would I do it again? No. There are other hosting providers that would allow me to host my large database and use Crystal Reports to produce my output. But they cost considerably more that what we are paying for GoDaddy hosting.
If you found this useful, consider helping me maintain this site.