SQL CLR function and OleDb permissions

Question

I have a .NET 3.5 C# library that uses OleDb to pull data from an Excel file and return it in a DataRowCollection which is used by a CLR Table-Valued Function in SQL Server 2005.

I deployed the ASSEMBLY in SQL Server as sa and used PERMISSION_SET = EXTERNAL_ACCESS. The sa login has EXTERNAL ACCESS ASSEMBLY and the database has TRUSTWORTHY on.

The assembly is signed and I used the following caspol.exe command which indicated that it was successful:

-m -ag All_Code -url "C:\Testing\sqlFunction.dll" FullTrust -n "sqlFunction"

The SQL Server instance, my library and the Excel document are all on the same machine.

The SQL Server service is running as Local System (but while trying to get this to work I also tried running it as the AD user that I was logged in as which is also a local administrator).

I created a command line application to test run the library and everything runs fine and data is returned as expected.

But when I run the function from SSMS, I get this error in the result pane:

A .NET Framework error occurred during execution of user-defined routine or aggregate "GetExcelFile": 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. 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.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 System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) at GetExcelFunction.GetFile() at GetExcelFunction.InitMethod(String logname)

I've deployed this on my personal dev machine (XP PRO) and our dev sandbox (Server 2003) and got the same error.

Most code access security issues revolve around the assembly running from a network share - but that's not the case here.

Any ideas? I'm tapped out.

Answer 1

I am sorry that I am not providing a solution to the exact problem here.

But, you can use OPENROWSET function to work with Excel files from SQL Server. CLR is not necessary, unless there is something that I don't know of.

Answer 2

I am looking the code using reflector. And I am purely speculating looking at the code.

Try initializing System.Data.OleDb.OleDbPermission class & use it's Add method to include the excel connection string to have permission (either before connection opens or query execution).

I hope that helps.

Answer 3

Solved it!

PERMISSION_SET = EXTERNAL_ACCESS was not enough. I had to go all the way down to PERMISSION_SET = UNSAFE and then it started working. I can't believe I didn't try that before.