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.

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.

Permission Denied executing SP that reads foreign table!

I thought I had a reasonable understanding of how Stored Procedure security
worked, but I've been caught out by something and now I'm a little puzzled.
When a stored procedure is executed the user calling the SP may not have
access to the base tables, but that doesn't matter as the SP runs under the
creators rights and in this case its the dbo who owns the SP. So far so
good.
However we have split our tables across two database (still on the same
server however). We have company data DB's where all requests originate and
to save time and duplication we have a central database holding information
that is common to all company DB's.
When an SP is executed on a company DB that needs to select data from the
central DB, we get a permission denied message.
Why is this? All objects across these databases on the same server are owned
by the same person... the DBO. so why doesn't this work.
In order to resolve this it seems I'm going to have to give select
permissions on certain tables within this central DB and that is what I was
trying to avoid.
Any help on the mechanics of this would be gratefully received
Steve Le Monnier> When a stored procedure is executed the user calling the SP may not have
> access to the base tables, but that doesn't matter as the SP runs under
> the creators rights and in this case its the dbo who owns the SP. So far
> so good.
This statement is not entirely correct; the proc doesn't run under the
creator's (owner) rights. Permissions on objects referenced by the proc are
not checked as long as the objects involved have the same owner. This is
called an unbroken ownership chain.

> Why is this? All objects across these databases on the same server are
> owned by the same person... the DBO. so why doesn't this work.
Cross-database ownership chaining is a little confusing because of
login/user mapping. It is not the name of the object owner that controls
the ownership chain, it is the login associated with the object owner.
Since the login mapping for the 'dbo' user is determined by database
ownership, both databases need to have the same owner in order to provide an
unbroken ownership chain for dbo-owned objects.
If you are using SQL 2000 SP3 or above, you need to also enable the 'db
chaining' database option for the databases involved. This should be done
only when you fully trust those users that have permissions to create
dbo-owned objects. See the Books Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Le Monnier" <steve_lemon@.hotmail.com.nospam.com> wrote in message
news:OBrDEOh3FHA.2432@.TK2MSFTNGP10.phx.gbl...
> I thought I had a reasonable understanding of how Stored Procedure
> security worked, but I've been caught out by something and now I'm a
> little puzzled.
> When a stored procedure is executed the user calling the SP may not have
> access to the base tables, but that doesn't matter as the SP runs under
> the creators rights and in this case its the dbo who owns the SP. So far
> so good.
> However we have split our tables across two database (still on the same
> server however). We have company data DB's where all requests originate
> and to save time and duplication we have a central database holding
> information that is common to all company DB's.
> When an SP is executed on a company DB that needs to select data from the
> central DB, we get a permission denied message.
> Why is this? All objects across these databases on the same server are
> owned by the same person... the DBO. so why doesn't this work.
> In order to resolve this it seems I'm going to have to give select
> permissions on certain tables within this central DB and that is what I
> was trying to avoid.
> Any help on the mechanics of this would be gratefully received
> Steve Le Monnier
>
>|||Thanks for the input Dan, I'm going to hit the Books On Line to read up on
DB Chaining.
Cheers
Steve Le Monnier
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eJrlkyh3FHA.3976@.TK2MSFTNGP15.phx.gbl...
> This statement is not entirely correct; the proc doesn't run under the
> creator's (owner) rights. Permissions on objects referenced by the proc
> are not checked as long as the objects involved have the same owner. This
> is called an unbroken ownership chain.
>
> Cross-database ownership chaining is a little confusing because of
> login/user mapping. It is not the name of the object owner that controls
> the ownership chain, it is the login associated with the object owner.
> Since the login mapping for the 'dbo' user is determined by database
> ownership, both databases need to have the same owner in order to provide
> an unbroken ownership chain for dbo-owned objects.
> If you are using SQL 2000 SP3 or above, you need to also enable the 'db
> chaining' database option for the databases involved. This should be done
> only when you fully trust those users that have permissions to create
> dbo-owned objects. See the Books Online for more information.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Steve Le Monnier" <steve_lemon@.hotmail.com.nospam.com> wrote in message
> news:OBrDEOh3FHA.2432@.TK2MSFTNGP10.phx.gbl...
>