Hi,
we are trying to create C# CLR Stored Procedure on a SQL Server 2005 machine. The C# code connects to an external datasource via an OLEDB driver to get a datatable. It has been tested on a separate console application, and it runs without problems. (The OLEDEB Driver is a proprietary driver connecting to a proprietary DB...)
On SQL Server, we have set the EXTERNAL permission level on the assembly, it is StrongNamed and an ASSYMETRIC KEY has been created for it in the SQL Server.
When we try to execute the procedure we get the following two problems:
Credentials used to contact the OLEDB are the ones that the SQL Service are running under, and not credentials of the logged user in the sql contect
a .NET Framework is raised :
System.Security.SecurityException: Request for the permission of type System.Data.OleDb.OleDbPermission
It seems that we cannot manage to give the necessary rights to the process for it to run the oledb driver. Either that or our configuration is lacking, or we are just doing something wrong. Setting permission level to UNSAFE is also not an option...
Does anybody have any experience with this type of problems. Any input would be welcome!