Permission-problem on simple query inside CLR function

I have created an assembly with permission set safe, and a function inside the assembly.

The function reads data from the same SQL Server as it is running inside. For connection I use the "Context connection = true", and the function has the SystemDataAccessKind attribute set to Read.

However when I execute my CLR function I get an error saying something like:

"The request for permission of type System.Data.SqlClient.SqlClientPermission...... failed"

I do, as the login user, have been granted the necessary rights, so I don't believe this is the answer to the error.

And my .dll is also signed.

Has this something to do with writing something a config file?

I have had simular problems with reporting services but fixed them by entering a node in the rspolicy.config file. If this is the case here - which .config file should i modify...machine.config?

Where do you execute your CLR proc from, i.e. where do you get the exception?

Niels
|||

Inside Management Studio. Management Studio is running on a different machine than the SQL Server, but the user that I use when logging into Management Studio is dbowner, and the same user that I use to create the assembly and CLR function.

If I extract the SQL from the CLR function and runs it as a basic query in Management Studio, everything works fine.

|||

I have also noted than when I try to create an SqlPermission in the code and run the Asset(), I get another type of error:

System.Security.Permissions.SecurityPermissions, mscorlib....

|||What happens if you try to do a "normal" query from the SSMS you are testing from, against a table in a database on the SQL Server you have deployed your assembly to?

Niels
|||

pnp wrote:

Inside Management Studio. Management Studio is running on a different machine than the SQL Server, but the user that I use when logging into Management Studio is dbowner, and the same user that I use to create the assembly and CLR function.

If I extract the SQL from the CLR function and runs it as a basic query in Management Studio, everything works fine.

Oh, sorry - disregard my previous posting anout executing a "normal query", I didn't read your post properly. I assume, when you say that you run it as a basic query, that you are running it from the same SSMS that you fail when going against the CLR proc?

What if you try and run from the same box that SQL is on?

Niels
|||

Niels, thanks for your suggestions. I have also installed Management Studio on the same server that is running the SQL Server but the error is the same.

However, as I don't have endless time to deal with this problem I have fixed it by setting the permission of the assembly to unsafe. This fixes it. However, from the documentation I have read, safe-permissions should be enough, and I don't really understand why it isn't in my case.