Sunday, March 11, 2012

CLR Stored procedure to access OleDb datasource, how?

I tried to write a CLR stored procedure using C# in SQL 2005 to access an
Access
database.
When I use the OleDbConnection class in System.Data, the procedure throws
SecurityException at runtime.
Output as following:
System.Security.SecurityException: Request for the permission of type
'System.Data.OleDb.OleDbPermission, System.Data, Version=2.0.0.0,
Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
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.OleDb.OleDbConnection.PermissionDemand()
at System.Data.OleDb.OleDbConnectionFactory.PermissionDemand(DbConnection
outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at Dbbest.Data.BulkStuff.bulkcopy(String source_oledb_connection_string,
String source_table, String destination_table, Int32 batchSize, Int32
notifyAfter)
The action that failed was:
Demand
The type of the first permission that failed was:
System.Data.OleDb.OleDbPermission
The Zone of the assembly that failed was:
MyComputerWhen you created the assembly did you set the permission set? If not,
then it is SAFE by default and you will not be able ot get to your
access database. You MAY have to go all the way to UNSAFE to get
there, I have not checked.
CREATE ASSEMBLY SQLCLRTest
FROM 'C:\MyDBApp\SQLCLRTest.dll'
WITH PERMISSION_SET = SAFE
SAFE, EXTERNAL_ACCESS, UNSAFE are the options that you have for this.
Steve

No comments:

Post a Comment