July 15, 2011 at 2:22 pm
Based on that link, yes. And I haven't tried a higher framework version..
I have a project on codeplex that I could use to try it out. Perhaps over the weekend..
CEWII
July 16, 2011 at 8:30 am
I was able to get something going. The issue changed for me when I reworked the DllImport for LoadLibrary (Reference).
Pre-reqs I had to satisfy to deploy UNSAFE assemblies:
1. Alter my test database setting TRUSTWORTHY ON. <edit>(Note: use Certificates in a PRODUCTION setting, not TRUSTWORTHY)</edit>
2. Make sure the database owner (DBO) has UNSAFE ASSEMBLY permission. My test DB is owned by sa.
Here is my UDF class. I stripped it down to only the essentials.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Runtime.InteropServices;
public partial class UserDefinedFunctions
{
[DllImport("kernel32.dll")]
static extern IntPtr LoadLibrary(string dllToLoad);
[DllImport("kernel32.dll", SetLastError = true)]
static extern bool FreeLibrary(IntPtr hModule);
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString TestLoadLibrary(SqlString LibraryPath)
{
IntPtr _moduleAddress = IntPtr.Zero;
try
{
_moduleAddress = LoadLibrary(LibraryPath.ToString());
// For debugging, throw an exception so that SSMS shows the results of LoadLibrary
if (_moduleAddress == IntPtr.Zero)
throw new Exception("Module NOT Loaded!");
else
throw new Exception("Module Loaded!");
}
catch (Exception e)
{
if (_moduleAddress != IntPtr.Zero)
{
try
{
FreeLibrary(_moduleAddress);
}
catch
{
// oh well, we tried...
}
}
throw e;
}
return string.Empty;
}
}
Once I deployed that as a funciton to SQL Server I was able to run this:
SELECT dbo.TestLoadLibrary('C:\Visual Studio 2008\Projects\ExoDLL\Debug\ExoDLL.dll') ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 18, 2011 at 8:57 am
Thanks to both of you for your assistance in this matter, but I believe I have found the cause of the failure with LoadLibrary.
I added a call to GetLastError and see that it is returning 193. A little more digging and I found that this is because SQL Server 2005 is 64-bit and the old libaray I'm trying to load is 32-bit. I'm going to have to leave the calcs in that old .dll for the workstation to process afterall.
This is my first foray into SQLCLR and it looks like I got in a little over my head. Thanks again, your time and effort are much appreciated.
July 18, 2011 at 3:16 pm
I think you are spot-on with your idea of employing the SQLCLR for this. You might want to try posting the question to the MSDN SQLCLR forum. You may get lucky and get an answer or workaround from Bob B.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 18, 2011 at 3:38 pm
OOOOOhhh... 64/32 strikes again.. Hate it when that happens..
And opc.three, I'd be really careful about turning trustworthy on, I have done that before but I much prefer to go the other route now.
CEWII
July 18, 2011 at 3:48 pm
Elliott Whitlow (7/18/2011)
And opc.three, I'd be really careful about turning trustworthy on, I have done that before but I much prefer to go the other route now.
I know...but I think it's a requirement for UNSAFE...and UNSAFE is a requirement for using LoadLibrary. Correct me if I am wrong please.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 18, 2011 at 3:54 pm
Not at all, trustworthy is not a requirement there are ways to get an UNSAFE assembly in without it. But unsafe and external access are a lot closer than people think, the only real difference is unsafe allows the use of PInvoke and unmanaged code.
CEWII
July 18, 2011 at 4:02 pm
UNSAFE was definitely required in this instance. LoadLibrary must have been the culprit.
I will check on TRUSTWORTHY down the line. I am not sure if it was...I think I was following a Bob B article on it but can't seem to find it right now (figures). I will post back later.
EDIT: was only sure about UNSAFE...will check TW later
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 18, 2011 at 4:14 pm
Elliott Whitlow (7/18/2011)
Not at all, trustworthy is not a requirement there are ways to get an UNSAFE assembly in without it. But unsafe and external access are a lot closer than people think, the only real difference is unsafe allows the use of PInvoke and unmanaged code.CEWII
"ways" = certificate signing?
Yeah, I remember that now, I bypassed that for the POC 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 19, 2011 at 8:15 am
Signing cert and I think something else as well, it has been many months since I last created an unsafe assembly, I try not to create unsafe assemblies if I possibly can.
CEWII
July 19, 2011 at 10:22 am
July 19, 2011 at 10:34 am
It mentions asym keys right in the error message:
CREATE ASSEMBLY for assembly 'ClrSql' failed because assembly 'ClrSql' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.
That is also "the long way around"...too much work for me for a POC 🙂
Here is the reference:
http://msdn.microsoft.com/en-us/library/ms345106.aspx
To create an EXTERNAL_ACCESS or UNSAFE assembly in SQL Server, one of the following two conditions must be met:
The assembly is strong name signed or Authenticode signed with a certificate. This strong name (or certificate) is created inside SQL Server as an asymmetric key (or certificate), and has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission (for external access assemblies) or UNSAFE ASSEMBLY permission (for unsafe assemblies).
The database owner (DBO) has EXTERNAL ACCESS ASSEMBLY (for EXTERNAL ACCESS assemblies) or UNSAFE ASSEMBLY (for UNSAFE assemblies) permission, and the database has the TRUSTWORTHY Database Property set to ON.
The two conditions listed above are also checked at assembly load time (which includes execution). At least one of the conditions must be met in order to load the assembly.
We recommend that the TRUSTWORTHY Database Property on a database not be set to ON only to run common language runtime (CLR) code in the server process. Instead, we recommend that an asymmetric key be created from the assembly file in the master database. A login mapped to this asymmetric key must then be created, and the login must be granted EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY permission.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 19, 2011 at 10:42 am
I agree, for a POC it is fine, for production no way.. and it is a little bit of work. I saved the script I used for next time.. no need to re-invent the wheel..
CEWII
July 19, 2011 at 10:47 am
Agreed on wheel inventing activities 😀
Re: the 32-bit issue...to the OP, can you install apps on your client's server as part of your deployment?
Thoughts on creating a 32-bit Windows Service that wraps the C DLL that can do IPC via named pipes and field requests from the 64-bit SQL Server process?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 19, 2011 at 11:12 am
As far as wrapping the 32-bit dll in a Windows Service, I'll have to pass on that for now. I have a deadline and have to cut the research off. I'll keep your ideas in mind for a future revision.
Thanks again.
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply