SQL CLR函数和OleDb权限

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.

我有一个.NET 3.5 C#库,它使用OleDb从Excel文件中提取数据并将其返回到DataRowCollection中,该数据由SQL Server 2005中的CLR表值函数使用。

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.

我在SQL Server中将ASSEMBLY部署为sa并使用了PERMISSION_SET = EXTERNAL_ACCESS。 sa登录有EXTERNAL ACCESS ASSEMBLY,数据库有TRUSTWORTHY。

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

程序集已签名,我使用了以下caspol.exe命令,表明它已成功:

-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.

SQL Server实例,我的库和Excel文档都在同一台机器上。

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).

SQL Server服务作为本地系统运行(但在尝试使其工作时,我也尝试将其作为我登录的AD用户运行,因为它也是本地管理员)。

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:

但是当我从SSMS运行该函数时,我在结果窗格中收到此错误:

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)

在执行用户定义的例程或聚合“GetExcelFile”期间发生.NET Framework错误:System.Security.SecurityException:请求类型为'System.Data.OleDb.OleDbPermission,System.Data,Version = 2.0.0.0的权限, Culture = neutral,PublicKeyToken = b77a5c561934e089'失败。 System.Security.SecurityException:位于System.Security.Andine.Deck(对象需求,StackCrawlMark和stackMark,Boolean isPermSet)的System.Security.PermissionSet.Demand()处于System.Data.Common.DbConnectionOptions.DemandPermission()处于System.Data的System.Security.SecurityException:位于System.Data.OleDb.OleDbConnection.Open的System.Data.Ole.b上的System.Data.OleDb.OleDbConnectionFactory.PermissionDemand(DbConnection outerConnection)中的.OleDb.OleDbConnection.PermissionDemand()处于System.Data.OleDb.OleDbConnection.Open(DbConnection outerConnection,DbConnectionFactory connectionFactory) System.Data.Common.DbDataAdapter.FillInternal(DataSet数据集,DataTable [] datatables,Int32 startRecord,Int32 maxRecords,String srcTable,IDbCommand命令,CommandBehavior)中的System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection连接,ConnectionState和originalState) System.Data.Common.DbDataAdapter.Fill(DataTable [] dataTables,Int32 startRecord,Int32 maxRecords,IDbCommand命令,CommandBehavi)或者行为)在GetExcelFunction.InitMethod(String logname)的GetExcelFunction.GetFile()处的System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)处

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

我已经在我的个人开发机器(XP PRO)和我们的开发沙箱(Server 2003)上部署了这个并且得到了同样的错误。

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.

有任何想法吗?我被挖出来了。

3 个解决方案

#1

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.

PERMISSION_SET = EXTERNAL_ACCESS是不够的。我不得不一直走到PERMISSION_SET = UNSAFE然后开始工作。我简直不敢相信我之前没有尝试过。

#2

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.

但是,您可以使用OPENROWSET函数来处理来自SQL Server的Excel文件。 CLR是没有必要的,除非有一些我不知道的东西。

#3

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).

尝试初始化System.Data.OleDb.OleDbPermission类并使用它的Add方法包含excel连接字符串以获得权限(在连接打开或查询执行之前)。

I hope that helps.

我希望有所帮助。