VS.NET 2005 and CLR stored procedure permission sets.
VS.NET 2005 automatically deploys a CLR stored procedure when you start a project with debugging. However, if the CLR stored procedure attempts to access external resources you will get a message stating:
System
.Security.SecurityException: Requestfor the permissionoftype'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.I've isolated this to being because the CLR stored procedure does not have the EXTERNAL_ACCESS permission set.
Is there a security attribute that can be used to decorate the CLR stored procedure code so that VS.NET 2005 will register the CLR stored procedure with the correct permission set?
Steve Miller
[1209 byte] By [
swmiller] at [2007-12-23]
You can assignt the permission set for an assembly by viewing the project's properties via solution explorer. Then click on the Database tab. This will bring up the project's database settings including
- Connection String
- Permission Level
- Assembly Owner
Hope this helps,
Derek
Thanks. That was the information I was looking for.
sm
Hi All,
I have created managed trigger and deployed it in sql server and it all works fine until i try to open another connection.
My first connection uses
SqlConnection("context connection = true")
but when try to open another connection, it comes up with an error. The details of an error are below. Can someone please help me with this error ?
A .NET Framework error occurred during execution of user defined routine or aggregate 'TrgInsertContract':
System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.PermissionSet.Demand()
at System.Data.Common.DbConnectionOptions.DemandPermission()
at System.Data.SqlClient.SqlConnection.PermissionDemand()
at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at ExportFunctionality.clsContractFunctionality.GetTDCompanies(clsContract objContract)
at ExportFunctionality.clsContractFunctionality.InsertContract(clsContract objContract)
at Triggers.TrgInsertContract()
.
The statement has been terminated.
You have most likely run into the same issue as the original poster in this thread. You need to create your assembly with a permission set of EXTERNAL_ACCESS.
Niels
I also am having a permissions issue with a .net assembly. Here's my error:
System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.CodeAccessPermission.Demand()
at System.IO.Directory.GetLogicalDrives()
at System.IO.DriveInfo.GetDrives()
at SQLGetDriveInfo.StoredProcedures.SPGetInfo()
The action that failed was:
Demand
The type of the first permission that failed was:
System.Security.Permissions.SecurityPermission
The Zone of the assembly that failed was:
MyComputer
What I'm trying to do is return some basic information on the harddrive (size, space left etc.). I have "with permission_set = external_access" set but still receive the error. My code works just fine in a console app, and I've run this as SA and as my domain account on my laptop (which is an admin on my laptop).
What else could be wrong?
Do you try to run the code on the same machine as SQL Server is installed on. or some other machine? Anyway, when SQL Server executes code which accesses external resources (as the file system) it runs the code in the context of the account SQL Server runs as. Some times that is a very low-provoliged account which won't have acccess to whatever it tries to access.
To solve this (if you log in with integrated security), you can do impersonation in the code through SqlContext.WindowsIdentity.
Niels
System.IO.Directory.GetLogicalDrives is marked with SecurityPermissionFlag.UnmanagedCode (I'm not entirely sure why), so it won't work under the EXTERNAL_ACCESS permission set.
Steven
You should be able to use System.IO.Environment.GetLogicalDrives() instead to get the drive information.
Steven
Thanks guys for replying but i am struck with my problem. I try to deploy the assembly with external_access but it comes up with an error as below:
Error 1 CREATE ASSEMBLY for assembly 'DataExport' failed because assembly 'DataExport' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.
I have set my database to trustworthy but not able to give dbo external access.
Can someone please help me with this problem ?
I am running sql2005 and my program on the same machine
In addition to mark the database as trustworthy you have to give the login of the owner of the database the EXTERNAL ACCESS ASSEMBLY permission. So from master you execute:
GRANT EXTERNAL ACCESS ASSEMBLY TO [db_owner_login]
where you obviously replace [db_owner_login] with the login of the owner of the db. Notice as well that EXTERNAL ACCESS ASSEMBLY is without under-scores.
Niels
Thanks guy! I found that it was unmanaged late last night and tried it and it works now.
Hi Niels,
I have given access to my loginid which is 'XYZ' thru which i am connecting to the database as i am unable to give access to either 'dbo' or 'sa' and still encounter the same problem.
Thanks,
Nagul - are 'xyz' owning the database? Run this query:
select suser_sname(owner_sid)
from sys.databases
where name = 'your_db_name'
Then do a GRANT EXTERNAL ACCESS to 'the name above'
Niels
Hi,
My database owner is 'sa' and when itried to run the command to grant the access , i get the following error,
use
mastergrant
external access assembly to sago
error:
Cannot grant, deny, or revoke permissions to sa, dbo, information_schema, sys, or yourself
I am not a database guy, so i might be doing something silly, so please help me with this.
