November 9, 2010 at 9:34 am
We move a site from sql server 2000 to sql server 2005 64bit the other day. Our frontend logs their error messages to the db. When we activated the site it was logging several messages stating it could not find sp master.dbo.xp_md5. So I went to the old sql server instance and scripted this proc and created it on the new 2005 version. I then double checked the sql server bin directory to ensure the xp_md5.dll was there. This resolved the front end errors however now we’re getting sql server errors in the log that state the following
Could not load the DLL xp_md5.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
Error: 17750, Severity: 16, State: 0.
Does anyone have any info that may help my situation?
November 9, 2010 at 11:04 am
Have you attempted to reregister the .dll?
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
November 9, 2010 at 11:07 am
here is an article similar to ur issue.
November 10, 2010 at 8:32 am
i believe there is tsql way to perform what this proc does. Does anyone have any insight to how you could rewrite the md5 to perhaps a sql function? Can u use hash match in sql 2005?
November 10, 2010 at 11:48 am
Thanks for the replies. Here is what I had to do in order to make this work. Re-registering was not an option as the the original dll was created on a 32 bit environment and this is on 64 bit. I know this could have been recompiled but no one had the source code. The .net developer told me what to use in order to do the same functionality.
1.Set the Master database option “TrustWorthy” to ON:
ALTER DATABASE Master SET TRUSTWORTHY ON
2.Enable CLR on the server:
sp_configure 'clr enabled',1
GO
reconfigure
3.Create the Assembly:
USE Master
GO
CREATE ASSEMBLY [MD5StringFunction]
FROM 'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Web.dll'
WITH PERMISSION_SET = UNSAFE
4.Create the Function:
USE Master
GO
CREATE FUNCTION [dbo].[CLRMD5] (@str nvarchar(max), @ECode nvarchar(50))
RETURNS nvarchar(max)
AS EXTERNAL NAME [MD5StringFunction].[System.Web.Security.FormsAuthentication].[HashPasswordForStoringInConfigFile]
5.Modify the existing SQL function to call the new code:
USE Master
GO
ALTER FUNCTION [dbo].[MD5]
(
@ValueTEXT
)
RETURNS VARCHAR(50) AS
BEGIN
DECLARE @vMD5VARCHAR(50)
--EXEC master.dbo.xp_md5 @Value, -1, @vMD5 OUTPUT
SELECT @vMD5 = master.dbo.CLRMD5 (@Value,'MD5')
RETURN @vMD5
END
6.Test the function:
SELECT master.dbo.MD5 ('Test')
I am not sure about the "Unsafe" setting but this was the first time I have ever created an Assembly
January 11, 2011 at 3:28 pm
Thank you so much for posting this alternative. I've been fighting this whole issue all day and basically bumped into a dead end with trying to find a working 64bit DLL. Your alternative works like a charm!!! 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply