CODEWITHSUNDEEP
sql-serversqlclr
John
John

Reputation: 1913

How to unload native DLL (loaded using [DllImport()] attribute) from SQL Server process?

I have created a SQLCLR User-Defined Function which calls a native C++ DLL.

The native C++ DLL is mine and if I want to make changes in it I need stop the sqlservr process to copy the new one. In production that is not acceptable.

Even if I drop the assembly which makes use of that DLL, the file is still in use.

What can I do to overwrite the native DLL?

EDIT

DLL method declaration:

[DllImport("Library64.dll", CallingConvention = CallingConvention.Cdecl, CharSet = CharSet.Ansi)]
public static extern IntPtr GetTiming();

MS seems to load library on first call to it, and seems to unload when process terminates .... :)

Upvotes: 2

Views: 1035

Answers (2)

Solomon Rutzky
Solomon Rutzky

Reputation: 48874

It sounds like the "reference" is being held by the App Domain. You can try unloading the App Domain which should clear that out (best guess as I have no way to test this). You can do that by making any security changes to the database. The following works:

ALTER DATABASE {db_name} SET TRUSTWORTHY ON (or OFF if already ON);
GO
ALTER DATABASE {db_name} SET TRUSTWORTHY OFF (or ON if already OFF);
GO

Keep in mind that this will unload all AppDomains in that particular Database. This is usually not an issue since people rarely have multiple App Domains in a single Database (that would require Assemblies being owned by different Users, and most people just use dbo).

To see what App Domains exist and which assemblies are loaded into them, run the following:

SELECT DB_NAME(dca.[db_id]) AS [DatabaseName], dca.*, '---' AS [---], dcla.*
FROM sys.dm_clr_appdomains dca
INNER JOIN sys.dm_clr_loaded_assemblies dcla
        ON  dca.appdomain_address = dcla.appdomain_address
WHERE dca.[db_id] <> 32767;

If nothing is returned by that query and you still can't replace that external DLL, try the following (which seems like a bit much, but we need to know if it works before trying anything else):

sp_configure 'clr enabled', 0;
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
RECONFIGURE;

Two other options to try are:

  • Create a wrapper DLL that you would call via DLLImport. And it would call your Library64.dll

  • The last resort would be to forcefully remove the unmanaged DLL with yet another unmanaged call. You did not create the DLL using LoadLibrary(), but you should be able to get a reference to it using GetModuleHandleExA() and then using that handle in a call to FreeLibrary(). This is described in the following blog post: PInvoke Library Load/Unload Behavior – Freeing Unmanaged Libraries. It seems this was the only method to be successful. Please see @John's answer for the specific code.

Upvotes: 1

John
John

Reputation: 1913

Here is what I have done and what seems to work. Please tell me if it is wrong to do this..

I have added two additional CLR storedprocedure like these:

[SqlProcedure]
public static void asdUnloadLibrary()
{
    try
    {
        var hMod = IntPtr.Zero;
        if (GetModuleHandleExA(0, "Engine64.dll", ref hMod))
        {
            while (FreeLibrary(hMod))
            { }
        }
        else
        {
            throw new Exception("Library not found");
        }
    }
    catch (Exception e)
    {
        throw e;
    }
    return;
}

[SqlProcedure]
public static void asdLoadLibrary()
{
    try
    {
        var hMod = IntPtr.Zero;
        LoadLibrary("Engine64.dll"); 
    }
    catch (Exception e)
    {
        throw e;
    }
    return;
}

Now ... In case I want to copy to server a new native DLL file I will:

1) Execute asdUnloadLibrary stored procedure. This will unload the dll.
2) Then, I can copy another version of dll to system folder
3) Then I can (but I think it is not necessary) do it:

ALTER DATABASE TEST SET TRUSTWORTHY OFF
GO
ALTER DATABASE TEST SET TRUSTWORTHY ON
GO

4) Execute asdLoadLibrary

And Now the original UDF function works again as expected ...

Upvotes: 1

Related Questions