InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 00:46:50 GMT20http://www.sqlservercentral.com/Forums/Topic199534-236-1.aspxI am facing the same issue Could not load the DLL d:\MSSQL\Binn\xp_base64decode.dll, or one of the DLLs it references. Reason: 193(%1 is not a valid Win32 application.).I believe its again to do with 64bit environment issue.Is the 64 bit xp_base64decode.dll & xp_base64encode.dll readily available?Wed, 16 May 2012 06:46:53 GMTyogesh.kananihttp://www.sqlservercentral.com/Forums/Topic199534-236-1.aspxWill there be a problem with the toolkit for 64-bit machines? Or is there a separete toolkit for 64-bit machines?Fri, 30 Sep 2011 06:49:15 GMTArun T Jayapalhttp://www.sqlservercentral.com/Forums/Topic199534-236-1.aspxHiWe are currently migrating from SQL 2000 to SQL2008R2 64x and are unable to install the dll's. Earlier in this thread I have seen the recompiled 64bit versions of the Blowfish implementation - however we use the AES encryption and base64 encoding functionality.Does anyone have the 64bit version of the AES/base64 related dlls that they can post a link for please?--Apologies I mis-posted the above into the wrong thread - we are using the 'SQL 2000 DBA Toolkit' as opposed to the 'SQL Encryption Toolkit' this thread is covering. I will post on that thread instead.ThanksSat, 16 Jul 2011 07:50:37 GMTQuornstarhttp://www.sqlservercentral.com/Forums/Topic199534-236-1.aspxGo to page 14 of this thread http://www.sqlservercentral.com/Forums/Topic199534-236-14.aspxand you will see a zip file attachment from user : cygnussolutionsThats what i use and they worked fine.Fri, 20 May 2011 05:14:33 GMTKhalid-334217http://www.sqlservercentral.com/Forums/Topic199534-236-1.aspxi have a client who is sending data encrypted in 2005 database. we loaded it into 2008 database appreciate if you could provide dll's for 2008 sql server for 64 bit.thank youThu, 19 May 2011 12:52:47 GMTgshivaram 89152http://www.sqlservercentral.com/Forums/Topic199534-236-1.aspxHiDoes anyone have an example on how to base64encode and base64decode in SQL 2008 R2?my code is as follows im using the 64 bit xp_blowfishdecrypt and xp_blowfishencrypt but 32 bit xp_base64encode is there a way around this.........DECLARE @text VARCHAR(255) SET @key = '123456789' EXEC master..xp_base64encode @key, @key OUTPUT EXEC master..xp_blowfishdecrypt @name, @key, @text OUTPUT SELECT @text as [Name]I would really appreciate it if some one can help me on this as im in the process of migrating from SQL 2000 to SQL 2008Thu, 28 Apr 2011 03:40:18 GMTKhalid-334217http://www.sqlservercentral.com/Forums/Topic199534-236-1.aspxA big thanks for posting this. A client's recent 2000/32 - 2008/64 migration missed an application call to the blowfish dll's and this saved our bacon this morning.Mon, 13 Dec 2010 08:21:33 GMTvhandahttp://www.sqlservercentral.com/Forums/Topic199534-236-1.aspxBetter yet, just use the attached script. It has 4 CLR functions that do what you asked.Enjoy,Mike CFri, 08 Oct 2010 16:45:45 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic199534-236-1.aspxThe first issue is probably trying to use the 32-bit dll's on a 64-bit system. Second issue is that binary to float conversion is not supported in SQL...Ping me offline, I have a solution for you.ThanksMike CFri, 08 Oct 2010 13:25:05 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic199534-236-1.aspxI am most interested in the base64 encode/decode functions. The data we are working with is base64 encoded arrays of floating point (32 bit) data. First challenge is to decode large (3-500 kbyte) base64 data strings. The second challenge is to convert the resulting varbinary array into arrays of floats.Has anyone recompiled the dlls for 64 bit systems (we get the "unable to load dll" error if I try to use the distribution on a current system)? Or are there now a builtin base64 functions? I have tried using the cast to XML and then calling the XML .value() functions.DECLARE @data varchar(max), @XmlData xml, @vb varbinary(max)SET @data = 'SGVsbG8gV29ybGQgIQ==' SET @XmlData = CAST('<data>' + @data + '</data>' as xml) SET @vb = @XmlData.value('(data)[1]', 'varbinary(max)')SELECT @vb, CAST(@vb as varchar(max))This works for small stirngs, but on large data strings, I get back NULL.Thanks,DavidFri, 08 Oct 2010 12:05:46 GMTdjspubhttp://www.sqlservercentral.com/Forums/Topic199534-236-1.aspxNone of the XPs here were distributed as 64 bit. SQL 2000 doesn't have built-in encryption.ThanksMike CThu, 07 Oct 2010 19:07:09 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic199534-236-1.aspxxp_blowfishencrypt and xp_blowfishdecrypt will not work in 64 bit environments.Please stay away from this encryption method as you may have trouble when migrating to a 64 bit box.Why not use SQL server encryption?Thu, 07 Oct 2010 15:15:32 GMTLeju Geevarghesehttp://www.sqlservercentral.com/Forums/Topic199534-236-1.aspxHi David,This was actually created for SQL 2000--before we had the max data types. There could be a chance the XP API (extended proc API) won't handle max data types properly, but I'm not 100% sure on that--I haven't looked into the XP API in quite a while now. I'm working on CLR versions of these utilities for 2005 and 2008 (and R2) but that project's not complete yet.ThanksMike CThu, 07 Oct 2010 14:36:14 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic199534-236-1.aspxNice post and useful toolkit.I notice that the function definitions in your ADD.SQL script use varchar(2000) rather than varchar(max). Are there limits on the size of the data that can be passed to the base64encode and base64decode procedures? From a brief look at the .cpp files, it would seem like there are not limits, but I did not do a thorough code review. Would be helpful to know that these routines can arbitrarily large strings.Thank you,DavidThu, 07 Oct 2010 14:07:02 GMTdjspubhttp://www.sqlservercentral.com/Forums/Topic199534-236-1.aspxHi all,A while back I compiled the 32 bit version of the blowfish encrypt and decrypt dlls for the 64 bit environment. No functionality changes to the code were made. I've been using them for several months now in our SQL2008 64 bit environment on data that was upgraded from a SQL2000 database and encrypted using the 32 bit version. Works like a charm. I've attached the 64 bit dlls.Cheers!Mon, 09 Aug 2010 10:47:33 GMTcygnussolutionshttp://www.sqlservercentral.com/Forums/Topic199534-236-1.aspxIIRC you were looking specifically for the blowfish or twofish encryption, correct? If so I have .net/clr versions that would be better to run on 2005/2008 (32 or 64 bit). I just finished testing them against the published test datasets. Send me an email offline if you'd be interested.ThanksMike CMon, 09 Aug 2010 10:27:29 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic199534-236-1.aspxI'll post the file here. good luck.Mon, 09 Aug 2010 08:16:31 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic199534-236-1.aspx[quote][b]philip.manual (7/2/2010)[/b][hr]Hi Mike,I'm trying to get the source for the SQL 2000 DBA Toolkit using http://www.sqlservercentral.com/products/mcoles/default.asp, so that I can recompile it for x64. I see that the link is dead. Has the code been moved to a different site or is it a URL mistake?Can anyone post the SQL 2000 DBAToolkit source (if you happen to have it)?Thanks,Philip[/quote]I have the source but no ability to recompile to x64.This is the reply I got from Michael in July 2010.[quote]Hi Richard,Thanks for the kind words. I’m actually working on a SQL 2005/2008 CLR-based version that includes a lot of the same functionality, plus some. Other than the occasional request for source code for those who want to compile it on x64 I haven’t added any functionality to the SQL 2000 version – and probably won’t. If you’d like to tweak it specifically for SQL 2000 platform, I can send you the source code.ThanksMichael[/quote]So. I have a 2MB zip file from Michael. I'm sure if you drop him an email, he'll be happy to send you the source.I have VC9 Express Edition. I don't think it can cross compile.If it can, I'd like someone to do a walkthrough with me. I use it to compile PHP. But I only follow a recipe (buildconf, config.nice, nmake - more or less). No GUI involved.Regards,Richard Quadling.Mon, 09 Aug 2010 08:02:27 GMTRQuadlinghttp://www.sqlservercentral.com/Forums/Topic199534-236-1.aspxHi Mike,I'm trying to get the source for the SQL 2000 DBA Toolkit using http://www.sqlservercentral.com/products/mcoles/default.asp, so that I can recompile it for x64. I see that the link is dead. Has the code been moved to a different site or is it a URL mistake?Can anyone post the SQL 2000 DBAToolkit source (if you happen to have it)?Thanks,PhilipFri, 02 Jul 2010 08:42:27 GMTphilip.manualhttp://www.sqlservercentral.com/Forums/Topic199534-236-1.aspx[quote][b]Mike C (4/5/2010)[/b][hr]On sql 2005 and later you should go with the built-in encryption functionality. Extended procs are deprecated since 2005 in favor of clr. The built-in functionality is also more efficient since it lives in t-sql. You're still restricted to 8000 bytes per encryption call by default though. There are a couple of ways around that limitation--you can encrypt lob data in 8000 byte chunks (requires you to write udf/sp) or do it in clr (I posted a blog about this a while ago, but you'll have to google it "encrypt lob data sql server" because I don't have the link handy. These xp's really were meant to provide encryption on sql 2000 only, and there are better alternatives on 2005 and 2008.Update - Here's the link to encrypting a BLOB in CLR: http://sqlblog.com/blogs/michael_coles/archive/2009/04/08/let-s-encrypt-a-blob.aspxThanksMike C[/quote]Hi Mike,Any chance that you could post the 64 bit versions of your 32 bit dlls? I know you've given the source code but I don't have a 64 bit development environment to compile it on. We're in the middle of moving to 64 bit SQL2008 and have used your blowfish udf on several columns of our data and the 32 bit dlls don't work.Thanks,ToddTue, 13 Apr 2010 08:35:46 GMTcygnussolutionshttp://www.sqlservercentral.com/Forums/Topic199534-236-1.aspxYou can write a t-sql udf that chops your data into ~7800 byte chunks (symmetric encryption adds some bytes of overhead). Then concatenate the encrypted string back together. To decrypt do the opposite.Tue, 06 Apr 2010 09:22:44 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic199534-236-1.aspxThanks for the link. Is there any way to create a function within SQL ? I don't know anything about C#.Tue, 06 Apr 2010 06:48:00 GMThomebrew01http://www.sqlservercentral.com/Forums/Topic199534-236-1.aspxOn sql 2005 and later you should go with the built-in encryption functionality. Extended procs are deprecated since 2005 in favor of clr. The built-in functionality is also more efficient since it lives in t-sql. You're still restricted to 8000 bytes per encryption call by default though. There are a couple of ways around that limitation--you can encrypt lob data in 8000 byte chunks (requires you to write udf/sp) or do it in clr (I posted a blog about this a while ago, but you'll have to google it "encrypt lob data sql server" because I don't have the link handy. These xp's really were meant to provide encryption on sql 2000 only, and there are better alternatives on 2005 and 2008.Update - Here's the link to encrypting a BLOB in CLR: http://sqlblog.com/blogs/michael_coles/archive/2009/04/08/let-s-encrypt-a-blob.aspxThanksMike CMon, 05 Apr 2010 15:48:45 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic199534-236-1.aspxWill BlowFish or TwoFish work with image data ?? I downloaded the latest SQL toolkit and Tried running TwoFish after modifying it to handle image data converted to varbinary(max). The data came back as NULL, instead of encrypted. Either I did something wrong (very likely), or there's some limitation in the DLL for length, or there's some other problem I don't understand.[code="sql"]--modified fn_encrypt_twofish in the hopes it will work with bigger dataCREATE FUNCTION [dbo].[fn_encrypt_twofish_max] (@plaintext VARBINARY(max), @localkeyname VARCHAR(128), @password VARCHAR(128), @keybits INT)RETURNS VARBINARY(max)ASBEGIN DECLARE @masterkey VARBINARY(64) DECLARE @localkey VARBINARY(80) SELECT @masterkey = m.[Key], @localkey = l.[Key] FROM dbo.Local_Key_Vault l, dbo.Master_Key_Vault m WHERE l.[name] = @localkeyname AND l.[master_key_name] = m.[name] DECLARE @enctext VARBINARY(max) EXEC dbo.xp_encrypt_twofish @plaintext, @enctext OUTPUT, @password, @masterkey, @localkey, @keybits RETURN @enctextEND[/code]and then ran[code="sql"]-- Encrypt dataUPDATE MyTable SET VoiceData = cast(master.dbo.fn_encrypt_twofish_max(VoiceData , 'Local Key 1', NULL, 32) AS VARBINARY(max))[/code]Thoughts ?TIAMon, 05 Apr 2010 15:02:58 GMThomebrew01http://www.sqlservercentral.com/Forums/Topic199534-236-1.aspxHI Mike,Thanks for your reply,Last time is working fine, we are using blatmail external application to send a mail. but only this few days is having an error. everytime they sending mail with attach htm file is creating a dump file. can anyone help me for this error.ThanksTue, 27 Oct 2009 01:34:10 GMTAyiehttp://www.sqlservercentral.com/Forums/Topic199534-236-1.aspxAyie, that's a completely different set of stored procedures. I haven't seen the xp_makewebtask proc in a long time to be honest. Check your server settings and make sure it's installed. It looks like this thing is trying to send an email, you might want to check into the databasemail functionality in SQL 2005 instead.Mon, 26 Oct 2009 21:29:58 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic199534-236-1.aspxHI All,I have same problem found in SQL Error Log in SQL 2005, last time is running daily successfully but only this few days I received error. Anyone can help me to find out what is the issue? Below is the error I found in error logs.2009-10-26 13:43:06.34 spid59 Error: 18002, Severity: 20, State: 1.2009-10-26 13:43:06.34 spid59 Exception happened when running extended stored procedure 'xp_makewebtask' in the library 'xpweb90.dll'. SQL Server is terminating process 59. Exception type: Win32 exception; Exception code: 0xc0000005.2009-10-26 13:44:00.22 spid141 Using 'dbghelp.dll' version '4.0.5'2009-10-26 13:44:00.23 spid141 ***Stack Dump being sent to V:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0340.txt2009-10-26 13:44:00.23 spid141 SqlDumpExceptionHandler: Process 141 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.2009-10-26 13:44:00.23 spid141 * *******************************************************************************2009-10-26 13:44:00.23 spid141 *2009-10-26 13:44:00.23 spid141 * BEGIN STACK DUMP:2009-10-26 13:44:00.23 spid141 * 10/26/09 13:44:00 spid 1412009-10-26 13:44:00.23 spid141 *2009-10-26 13:44:00.23 spid141 *2009-10-26 13:44:00.23 spid141 * Exception Address = 000007FF7FC51D71 Module(msvcrt+0000000000051D71)2009-10-26 13:44:00.23 spid141 * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION2009-10-26 13:44:00.23 spid141 * Access Violation occurred reading address 00000000000000002009-10-26 13:44:00.23 spid141 * Input Buffer 86 bytes -2009-10-26 13:44:00.23 spid141 * EXEC dbo.prc_sendMailKillQueriesMon, 26 Oct 2009 20:57:45 GMTAyiehttp://www.sqlservercentral.com/Forums/Topic199534-236-1.aspx[quote][b]Ed Mays (10/6/2008)[/b][hr][quote]Hi Ed,The data encrypted in PHP/mcrypt could have been encrypted with any number of options, and was probably encrypted with a different key. The options that affect the encryption/decryption process include:* Algorithm used* Key length (bits), content* Block mode* Initialization Vector (IV)/Salt length, content* Padding optionsMike C[/quote]Am I correct that there's no way some of these options (i.e. initialization vector) are not configurable in this tool?[/quote]With these sample XP's the only way to change most of these options is by modifying the C++ source code and recompiling. SQL 2005/2008 encryption won't be much help to you either since they choose random IVs and use preset block mode and padding options. Again, this sample code wasn't designed to decrypt data that was previously encrypted using other tools. You could probably force the issue by modifying the C++ source code to match the configuration you want, but it might be more trouble than it's worth.Mike CMon, 06 Oct 2008 17:50:23 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic199534-236-1.aspx[quote]Hi Ed,The data encrypted in PHP/mcrypt could have been encrypted with any number of options, and was probably encrypted with a different key. The options that affect the encryption/decryption process include:* Algorithm used* Key length (bits), content* Block mode* Initialization Vector (IV)/Salt length, content* Padding optionsMike C[/quote]Am I correct that there's no way some of these options (i.e. initialization vector) are not configurable in this tool?Mon, 06 Oct 2008 15:25:42 GMTEd Mayshttp://www.sqlservercentral.com/Forums/Topic199534-236-1.aspx[quote][b]Ed Mays (10/6/2008)[/b][hr]I am trying to decrypt data that was encrypted via PHP/mcrypt and stored in a MySQL database originally. We have a SQL Server that needs to process some of the data, and I am unable to decrypt any of the data using the blowfish decrypt xp.I can encrypt and decrypt fine on SQL Server, but am having difficulty with the data originating on the other system.Thanks[/quote]Hi Ed,The data encrypted in PHP/mcrypt could have been encrypted with any number of options, and was probably encrypted with a different key. The options that affect the encryption/decryption process include:* Algorithm used* Key length (bits), content* Block mode* Initialization Vector (IV)/Salt length, content* Padding optionsIf any one of these factors is different you can't decrypt the data. This particular tool wasn't really designed to decrypt data from external sources, but rather to demonstrate SQL 2000-specific encryption. You'll find the same issues on other platforms, like SQL 2005 or 2008 with built-in encryption. I would decrypt the data using PHP/mcrypt, and reencrypt on SQL Server rather than trying to directly decrypt the already PHP/mcrypt-encrypted data on SQL Server.ThanksMike CMon, 06 Oct 2008 15:13:53 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic199534-236-1.aspxI am trying to decrypt data that was encrypted via PHP/mcrypt and stored in a MySQL database originally. We have a SQL Server that needs to process some of the data, and I am unable to decrypt any of the data using the blowfish decrypt xp.I can encrypt and decrypt fine on SQL Server, but am having difficulty with the data originating on the other system.ThanksMon, 06 Oct 2008 14:26:58 GMTEd Mayshttp://www.sqlservercentral.com/Forums/Topic199534-236-1.aspx[quote][b]jsleichty (7/25/2008)[/b][hr]Has anyone used the xp_blowfish dll on SQL 2005? We just moved to a new server and we're getting error messages to the effect.Could not load the DLL C:\Program Files\Microsoft SQL Server\90\Tools\binn\Encryption DLL\xp_blowfishdecrypt.dll, or one of the DLLs it references. Reason: 193(C:\Program Files\Microsoft SQL Server\90\Tools\binn\Encryption DLL\xp_blowfishdecrypt.dll is not a valid Win32 application.).[/quote]Hi js,You don't happen to be running it on a 64-bit server do you? The DLL was only compiled for 32 bit. The source code is available somewhere on SSC (sorry I don't have a current link), but you could d/l and compile for 64 bit if necessary. I usually recommend avoiding XPs on SQL 2005, since they are deprecated. In this instance SQL 2005 provides native T-SQL encryption functionality.ThanksMike CFri, 25 Jul 2008 09:44:51 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic199534-236-1.aspxHas anyone used the xp_blowfish dll on SQL 2005? We just moved to a new server and we're getting error messages to the effect.Could not load the DLL C:\Program Files\Microsoft SQL Server\90\Tools\binn\Encryption DLL\xp_blowfishdecrypt.dll, or one of the DLLs it references. Reason: 193(C:\Program Files\Microsoft SQL Server\90\Tools\binn\Encryption DLL\xp_blowfishdecrypt.dll is not a valid Win32 application.).Fri, 25 Jul 2008 09:28:12 GMTjsleichtyhttp://www.sqlservercentral.com/Forums/Topic199534-236-1.aspxHi Kamalesh,Well, the problem is that searching/ordering data and encrypting it are two problems with diametrically opposed goals. When you search and order data transparency is your best friend, and raw speed is generally a driving goal. Sorting and searching rely strongly on patterns inherent in the data (for instance "A" comes before "B", "B" comes before "C", etc.).Encryption removes transparency. The goal of encryption is to obfuscate your data and remove patterns. In terms of encryption the more patterns you remove, the more secure your data from attack. Encryption removes the patterns that allow you to sort data and search on ranges of data without going through the decryption process first.By not using a SALT/IV you can perform exact equality searches on encrypted data. You can also create a hash of your data for search purposes, although this could expose statistical patterns in your data to hackers.ThanksMichaelThu, 22 May 2008 21:34:30 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic199534-236-1.aspxHi Mike,Thanks a lot for the encryption solution, it works excellent.We are working on a solution where we have to provide sorting on the column which is encrypted.This seems to be impacting a lot on the performance.Could you please suggest something on this?Thanks,KamaleshWed, 21 May 2008 10:09:27 GMTkamaleshshirpurkarhttp://www.sqlservercentral.com/Forums/Topic199534-236-1.aspxI think it was supposed to say "toolkit" :)Mon, 28 Apr 2008 13:24:20 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic199534-236-1.aspxI must be further behind on my encryption terminology than I thought. The blurb for the article says:[quote]Free SQL Server 2000 Encryption for your data!!! Author Michael Coles has put together a tolljit and some XPs that you can use to encrypt your data with the Blowfish algorithm.[/quote]Google only brings me back to here when I try to figure out what a "tolljit". JIT, I figure, means "Just In Time", but that is only a guess.Mon, 28 Apr 2008 13:07:31 GMTJames Skipperhttp://www.sqlservercentral.com/Forums/Topic199534-236-1.aspx[quote][b]Peso (4/25/2008)[/b][hr]No need to invoke CLR (only if you want greater speed).Free RC4 encryption here[url="http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76258&SearchTerms=RC4"]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76258[/url]Free MIME64/BASE64 encoding here[url="http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67831&SearchTerms=MIME64"]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67831[/url]Free ROT algorithm here[url="http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67752"]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67752[/url][/quote]Thanks for the links! Actually the XP's here don't use CLR - they are native extended procedures. The BASE64 encoding was included as a convenience feature, and the ROT algorithm was added as a test proc to ensure the installation went smoothly. I decided to implement AES in this release instead of RC4.Sun, 27 Apr 2008 22:08:32 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic199534-236-1.aspxHi Wayne,There are some interesting issues surrounding the bigint data type, but I don't know why it would be giving you results so out of whack. I'll look into it when I get a chance. Thanks!Sun, 27 Apr 2008 22:04:27 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic199534-236-1.aspxHey Charles,I haven't had an opportunity to test on compact editions (mostly because I don't have access to any right now). It does sound like an interesting idea though, and I'd love to hear the results if someone does try it out.BTW, Steve was kind enough to post the C++ source code for this project somewhere here on the SSC site. I imagine if it doesn't work as-is on compact editions it could probably be modified to use functionality supported on the compact platform.Sun, 27 Apr 2008 22:02:04 GMTMike C