Unmanaged code from SQLServer 2005 CLR
in [Programming SQL]
|
Prev: sa password rules
Next: database object hierarchy
From: Phil on 1 Feb 2006 17:06 Hi, I am trying to write a C# user defined function in SQL Server 2005. From there, I have to access a dll that was written in unmanaged C++. I do not think that I can use dllimport since I need to create objects from that dll. So I created a C# wrapper for the work that I need to do with the dll. I then added the wrapper dll to database?s assemblies. In order to do that, I had to first add the corresponding Interop dll. I was then able to reference my wrapper in my SQL project using ?Add Reference? in Visual Studio 2005. From my function I am able to create an instance of my wrapper. However, inside the wrapper I am unable to create an object from the unmanaged dll. I get the following error: "Creating an instance of the COM component with CLSID {?..} from the IClassFactory failed due to the following error: 80010105." I am able to use the wrapper dll from a standard C# windows application. Am I missing something? Is this even the right way of doing things? Many thanks, Phil
From: Steven Cheng[MSFT] on 2 Feb 2006 04:20 Hi Phil, Welcome to the MSDN newsgroup. As for using managed code in SQL CLR, generally speaking , the main restriction is the .NET's CAS restriction. And for SQL CLR code, its application level CAS policy is set for the custom object(UDT, UDF....)'s assembly , and the permission is granted at the deployment time of the assembly into sqlserver. And there are three levels: Safe, External Access and Unsafe.... to execute code accessing external resource or unmanaged code, I think we must set the level to unsafe. If this is the case you can try changing the security policy level of your custom assembly. #CLR Integration Code Access Security http://msdn2.microsoft.com/en-us/library/ms345101.aspx In addition, you can also try calling a buildin COM object, e.g the ADO or Scripting object instead of your custom COM object through COM interop to see whether it works. Regards, Steven Cheng Microsoft Online Support Get Secure! www.microsoft.com/security (This posting is provided "AS IS", with no warranties, and confers no rights.)
From: Phil on 2 Feb 2006 10:28 Hi Steven, It looks like the issue is specific to the object that I was trying to create. I was able to create an object (CatalogClass) from ADO. I then tried to create a different object in the dll that I am interested in and it worked. As for the permission level, I was already using the "unsafe". Since I am able to use my C# wrapper and create the object from a "standard" C# application I am thinking that there is something specific about the SQLServer CLR that is not allowing the object to be created. Do you know of additional restrictions that apply to the SQLServer CLR? Thanks for you help. Regards, Phil
From: Steven Cheng[MSFT] on 3 Feb 2006 05:36 Thanks for your response Phil, So it seems the problem is component specific. And currently I haven't found any other particular security setting against COM component, and the problem is likely due to something other than security. What's the detaild code logic in your component or anything different than other component object which can be creaetd correctly? Regards, Steven Cheng Microsoft Online Support Get Secure! www.microsoft.com/security (This posting is provided "AS IS", with no warranties, and confers no rights.)
From: Phil on 10 Feb 2006 08:13
I do not know the specifics of the components. I do not have access to the source code and cannot use a different one to perform the same task. Typical scenario. I ended up moving the use of the component outside of SQL2005. I send the data out using MSMQ. Ideally I would probably want to use the Service Broker component, but I did not have the time to investigate. Using MSMQ from SQL2005 works, but the amount of assemblies that need to be imported before System.Messaging can be used is impressive? Six to be exact, including things like System.Windows.Forms. But it works. Thank you for your help. Regards, Phil |