How to deploy an EventReceiver which writes to SQL Server correctly

Question

following scenario. I have a client machine on which I have all the permissions for:

1. SQL-databases (db_owner)
2. Full access to a Sharepoint Application.
3. Created a Sql-account (db_owner) and included credentials into connection string.

I also have my developing environment on a virtual machine (VM). Here I also have any permissions possible.

My task is to write an EventReceiver which reacts when Items are added to a specific list and then writes certain data to a database. I was told by an experienced developer (who is not around anymore) that I am supposed to simulate the real world situation in my developement environment when developing. So I did the following:

1. Create a 1:1 copy of the DB on the VM
2. Import a list templatre created out of the actual list I have to work with (including contents)
3. Write EventReceiver
4. Test EventReceiver
5. After I could verify that my solution is working in the VM I changed the DB-Connection strings.
6. Copy xxx.wsp to client machine.
7. Upload and activate the xxx.wsp

In the EventReceiver I am handling ItemAdded, ItemDeleting and ItemUpdated. The only method I was able to implement an ErrorMessage in was the ItemDeleting because the other two methods are asynchronous tasks, I guess. This is why this is the only action where I can see the error message. Well I also can tell that the ItemAdded-method does not work because no items are added to the DB.

The server has encountered the following error(s): Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

On the one hand I find this message quite selfexplonatory, but on the other hand I have db_owner permission level for the sql-database (with the very same creadentials I am logged in on the sharepoint site), which confuses me. Also I am not able to debug my solution as it was created on the VM which has Sharepoint installed contrary to my client machine.

After I got the exception I also tried following:

I tried to enclose the contents of my methods with SPSecurity.RunWithElevatedPrivileges. This gave me an error concerning the Assembly version. Could not load type 'CodeToRunElevated' from assembly. I am not sure but as far as I read it is supposed to be 14.0.0.0 but it was 14.900.0.0. Not sure what this is supposed to mean.

Then I tried to install the .wsp over the SharePoint Management-Shell directly on the server. I was able to add it and to install it so Get-SPSolution gave me a list which included my .wsp marked as deployed = true. Still, now adding a ListItem did not only not add it to the database but also this time I did not even get an error message when I tried to delete this very same item from the Sharepoint list.

Please help, I am quite desparate because the EventReceiver is working on the VM so this really bugs me.

Answer 1

What you are encountering is a code access security issue (CAS). Basically SharePoint only trusts certain assemblies to perform certain "risky" operations and your assembly is not on the list.

The way the list of assemblies that are allowed to do certain things is managed is through a CAS policy file which is located in the config folder of sharepoint. You can determine this location by looking at the web.config file in your root sharepoint application IIS directory and searching for trust level. It will tell you what level you're at (medium, full, etc.) and give you the path to the policy file (it too will be a config extension)

You can look at this article for writing your own addition to e policy for your assembly: http://alexanderst.wordpress.com/2011/03/03/adding-cas-permissions-to-wsp-in-sharepoint-2010/. Or this one:http://blog.mastykarz.nl/working-easier-custom-cas-policies/

You can also TEMPORARILY set the trust level to FULL in your dev sharepoint instance just to see if that's all that's causing it (but please understand that is only a temporary development solution - unless you take the steps to get a proper entry into the target environment's policy file you will face the same restriction when you go to those environments where the trus level should not be set to full)

Answer 2

Don't you need to give permission to access the database to the Service Account running the app pool in IIS? I think that's the user that will actually be attempting to access your database, not the current logged in user (either logged in to Windows, or logged in to SP site).