SQLServerCentral / Discuss Content Posted by Brian Knight / Article Discussions / Article Discussions by Author / Encrypting Data With the Encrypt Function / Latest Posts

RE: Encrypting Data With the Encrypt Function

ikjeft01 — Tue, 04 Aug 2009 17:34:25 GMT

encrypt is not a recognized function name in sql 2005. pwdencrypt() is, however

RE: Encrypting Data With the Encrypt Function

JunkMail Victim — Mon, 05 Jun 2006 06:20:00 GMT

I can confirm what you're saying for the encrypt function, but not when encrypting a stored procedure. A true test of encryption over encoding is whether or not the same result is produced each time you perform the function.

I created several stored procedures to see what happens, using this simple bit of SQL code:

CREATE PROCEDURE "sp_Test" WITH ENCRYPTION AS
--This is a test
GO

Then I would read out the entry from the syscomments table, drop the stored procedure and repeat it. Each time, the entry was different. This looks like encryption to me. What do you think?

Thanks.

RE: Encrypting Data With the Encrypt Function

JunkMail Victim — Thu, 01 Jun 2006 20:22:00 GMT

That's quite a misnomer calling it encryption. Thanks for the example. I had fun with that.

RE: Encrypting Data With the Encrypt Function

Oskar Austegard-253928 — Thu, 01 Jun 2006 14:45:00 GMT

For the record, PWDENCRYPT is also not secure:http://www.theregister.co.uk/2002/07/08/cracking_ms_sql_server_passwords/

RE: Encrypting Data With the Encrypt Function

Oskar Austegard-253928 — Thu, 01 Jun 2006 14:40:00 GMT

Crap. I had an eloquent reply to this, but Firefox/sqlservercentral swallowed it.Ok - I'm too lazy to type it all again, have a look at the sql and come to your own conclusions:--See the pattern?SELECT ENCRYPT (''), ENCRYPT ('a'), ENCRYPT ('ab'), ENCRYPT ('abc')--Simple hex encoding of unicode valuesSELECT ENCRYPT('a') / (0x0100 * 1), UNICODE('a')SELECT ENCRYPT('b') / (0x0100 * 1), UNICODE('b')--better, also undocumented alternative - for more use googleSELECT PWDENCRYPT (''), PWDENCRYPT ('a'), PWDENCRYPT ('ab'), PWDENCRYPT ('abc')

RE: Encrypting Data With the Encrypt Function

JunkMail Victim — Thu, 01 Jun 2006 12:10:00 GMT

Hi Oskar:

Thanks for the reply. In my organization we have some people who are looking into adding the encrypt clause to stored procedures prevent tampering. This may prove reasonable, but perhaps not. I guess what I'm looking to know are a few things about the encrypt clause:

A recent SQL Server Central article says that Microsoft does not provide support for it. I'm wondering where I can find Microsoft's statement to that effect.
If this is in fact encoding, and not encryption where could I go to corroborate this?
If you turn on the encrypt clause will applications running those stored procedures go belly-up or will SQL Server handle it seamlessly.
Lastly, can this be applied to triggers without issue?

Thanks for the expertise.

RE: Encrypting Data With the Encrypt Function

Oskar Austegard-253928 — Thu, 01 Jun 2006 07:24:00 GMT

JunkMailVictim: As nicerguy crudely points out, the ENCRYPT function does NOT encrypt anything, it merely hex ENCODES the string, which is quite useless from a security standpoint. If you need encryption it looks like the free version of xp_crypt would be the way to go.

RE: Encrypting Data With the Encrypt Function

JunkMail Victim — Wed, 31 May 2006 14:08:00 GMT

Hi:

Is there a reference link that explains Microsoft's support limitations with the encrypt function? Also, is it the same encryption used to encrypt stored procedures.

Thanks.

RE: Encrypting Data With the Encrypt Function

Oskar Austegard-253928 — Tue, 30 May 2006 10:41:00 GMT

Yikes. So much for that idea, then.

RE: Encrypting Data With the Encrypt Function

Oskar Austegard-253928 — Tue, 30 May 2006 10:15:00 GMT

Uhm - executing this in SQL 2000select ENCRYPT('abc123')yields0x610062006300310032003300So I'd have to say you're wrong.

RE: Encrypting Data With the Encrypt Function

Andy Warren — Wed, 16 Jul 2003 03:11:00 GMT

Please keep your comments professional. Disagreeing is fine. Being disrespectful is not. Thanks.Andyhttp://www.sqlservercentral.com/columnists/awarren/

RE: Encrypting Data With the Encrypt Function

nicerguy — Tue, 15 Jul 2003 16:59:00 GMT

What the hell are you thinking? You guys are just converting the string to a double-byte character string and type-casting it as a numerical. Hello?0x5400 = 84 = 'T'0x6500 = 101 = 'e'0x7300 = 115 = 's'0x7400 = 116 = 't'0x5000 = 80 = 'P'0x5700 = 119 = 'w'0x3100 = 49 = '1'

RE: Encrypting Data With the Encrypt Function

rpicchi — Thu, 15 May 2003 14:14:00 GMT

Probé en un sql 2000 SP2 y anda mal (la encripción es la que yo comenté anteriormente y no debe usarse).En cambio, en un sql 7.0 SP4, aparentemente anda ok. Lo que me parece que no es muy bueno en basarse en funciones no documentadas como ENCRYPT(), viendo que cambian con las versiones del motor.Saludos,Rafael Picchirafap@uol.com.ar

quote:
quote:
La encripción que hace esa función es muy fácilmente decifrable, como para usarla en cualquier ambiente. Solo guarda los caracteres en hexadecimal (2 bytes en hexa para cada uno, donde el segundo es 00) Lo que pasa al hacer select, es que solo ves el primer byte. Si te fijas, (en tu ejemplo) al hacer:select * from users where UserPW=0x5400650073007400500057003200 T e s T P W 2te devuelve:TestUser2 TMe parece muy malo que recomiendes esto como método de encripción.Rafael PicchiArgentinarafap@uol.com.ar
Try this one to confirm your idea:SET NOCOUNT ONSELECT ENCRYPT('TestPW1')SELECT ENCRYPT('TestPW2')SELECT ENCRYPT('TestPW3')SET NOCOUNT ONSELECT ENCRYPT('TestPW1')SELECT ENCRYPT('UestPW1')SELECT ENCRYPT('VestPW1')[url][/url]

RE: Encrypting Data With the Encrypt Function

diogenes1331 — Thu, 15 May 2003 11:23:00 GMT

quote:
La encripción que hace esa función es muy fácilmente decifrable, como para usarla en cualquier ambiente. Solo guarda los caracteres en hexadecimal (2 bytes en hexa para cada uno, donde el segundo es 00) Lo que pasa al hacer select, es que solo ves el primer byte. Si te fijas, (en tu ejemplo) al hacer:select * from users where UserPW=0x5400650073007400500057003200 T e s T P W 2te devuelve:TestUser2 TMe parece muy malo que recomiendes esto como método de encripción.Rafael PicchiArgentinarafap@uol.com.ar

Try this one to confirm your idea:SET NOCOUNT ONSELECT ENCRYPT('TestPW1')SELECT ENCRYPT('TestPW2')SELECT ENCRYPT('TestPW3')SET NOCOUNT ONSELECT ENCRYPT('TestPW1')SELECT ENCRYPT('UestPW1')SELECT ENCRYPT('VestPW1')[url][/url]

RE: Encrypting Data With the Encrypt Function

diogenes1331 — Thu, 15 May 2003 11:21:00 GMT

quote:
La encripción que hace esa función es muy fácilmente decifrable, como para usarla en cualquier ambiente. Solo guarda los caracteres en hexadecimal (2 bytes en hexa para cada uno, donde el segundo es 00) Lo que pasa al hacer select, es que solo ves el primer byte. Si te fijas, (en tu ejemplo) al hacer:select * from users where UserPW=0x5400650073007400500057003200 T e s T P W 2te devuelve:TestUser2 TMe parece muy malo que recomiendes esto como método de encripción.Rafael PicchiArgentinarafap@uol.com.ar

RE: Encrypting Data With the Encrypt Function

shauntj — Tue, 13 May 2003 12:44:00 GMT

This may be useful, feedback is certainly most welcome.1) How does one perform validation processes in SQL2K?Below is a script that demonstrates a column that has accepted encrypted values, it then returns a row based on input that undergoes an encryption. THIS IS NOT SUPPORTED BY MICROSOFT >>WAS IT EVER???<<<I hope it fits>SET NOCOUNT ONGO/*Author: Shaun Tinline-JonesCreate Date: 2003/05/13Purpose:Testing some logic around the Encrypt function*/USE NorthwindGOIF OBJECTPROPERTY(OBJECT_ID(N'dbo.Test_Encrypt'), N'IsTable') = 1 DROP TABLE dbo.Test_EncryptGO-- We need to store the values in a field that holds the result of the encryptionCREATE TABLE dbo.Test_Encrypt( Gambler sql_variant NOT NULL-- Gambler nvarchar(25) NOT NULL)GO/**************************** We have some gamblers *********************************/DECLARE @Name nvarchar(25) --sql_variantSELECT @Name = ENCRYPT(N'Shaun')INSERT INTO dbo.Test_Encrypt(Gambler) VALUES (ENCRYPT(@Name))INSERT INTO dbo.Test_Encrypt(Gambler) VALUES (ENCRYPT(N'Grant'))INSERT INTO dbo.Test_Encrypt(Gambler) VALUES (ENCRYPT(N'Jacye'))SELECT Gambler FROM dbo.Test_EncryptGO/******************************** Now let's get that winner ****************************/DECLARE @Winner nvarchar(25)SET @Winner = N'Shaun'SELECT N'and the lotto winner is.......' + @Winner FROM dbo.Test_Encrypt WHERE Gambler = ENCRYPT(@Winner) --@Encrypted_WinnerGOSET NOCOUNT OFFGO2) How does one deal with the upgrade?The encrypt function is now, correctly stated by Jacye, using the windows CryptoAPI. So yes it is different from other versions of SQL Server and also suffers the same exposure to cracking>>Whatever that may be<< as this API .The best is to use a query that takes a first time user, that is first time since upgrade, check it against a SQL Server 7.0 with the password table. If it succeeds, run customer created encryption algorithm, even if it is the straight Windows CryptoAPI, (as opposed to the SQL2K function) and store the result in the SQL Server instance and record that the user has upgraded.This will handle the upgrade in a controlled fashion, while remaining transparent to the user community as well as protect the customer from the possible deprecation of the ENCRYPT function.

RE: Encrypting Data With the Encrypt Function

rpicchi — Thu, 21 Feb 2002 17:08:00 GMT

La encripción que hace esa función es muy fácilmente decifrable, como para usarla en cualquier ambiente. Solo guarda los caracteres en hexadecimal (2 bytes en hexa para cada uno, donde el segundo es 00) Lo que pasa al hacer select, es que solo ves el primer byte. Si te fijas, (en tu ejemplo) al hacer:select * from users where UserPW=0x5400650073007400500057003200 T e s T P W 2te devuelve:TestUser2 TMe parece muy malo que recomiendes esto como método de encripción.Rafael PicchiArgentinarafap@uol.com.ar

RE: Encrypting Data With the Encrypt Function

Max Howell — Sat, 15 Sep 2001 07:49:00 GMT

Hi guys, Nice to meet you here, I'm author of xp_crypt(www.vtc.ru/~andrey/xp_crypt). You can easily encrypt with strong RSA encryption all what you want just with simple sql scripts.On my web page, i included all needed examples. And if you dont need encrypt strings longer then 21 chars and with key length more then 256 bits , so for you its freeBesides, it contains DES and SHA1 hashes with unlimited string length.Thank you for attention.Edited by - Andrey Kubyshev on 09/15/2001 07:51:13 AMEdited by - Andrey Kubyshev on 09/15/2001 07:53:25 AM

RE: Encrypting Data With the Encrypt Function

Dinesh Priyankara — Wed, 12 Sep 2001 03:47:00 GMT

I have little doubt about this. Though data is encrypted, that can be read easily.If I inserted data like 'app' with encryption, that is shown as 'a[Odd Char]p[Odd Char]p[Odd Char]'. I highly appreciate your reply.

RE: Encrypting Data With the Encrypt Function

ians — Thu, 09 Aug 2001 10:36:00 GMT

quote:
Are you connected with the author?

No, in fact I haven't even tried using it myself yet. I cant even remember when I found it, but have keep a bookmark to it, I'm sure it will come in usefull one day.

RE: Encrypting Data With the Encrypt Function

Sean Burke — Tue, 07 Aug 2001 20:18:00 GMT

Now that's a cool tool. Frebbie's not too bad, and $98.00 for the full version seems a fair price for someone who needs this functionality! Are you connected with the author?

RE: Encrypting Data With the Encrypt Function

ians — Tue, 07 Aug 2001 03:49:00 GMT

I'm suprised noone has mentioned xp_crypt :-[url]http://www.vtc.ru/~andrey/xp_crypt/[/url]

RE: Encrypting Data With the Encrypt Function

Brian Knight — Tue, 31 Jul 2001 10:40:00 GMT

quote:
Supposing im creating a personnel database and want to store national insurance numbers etc , i want this to be encrypted in the table., But throught the client application front end , a personnel details administrator should be able to see the unencrypted data .. how would i do this ?

Your best bet then would be to create some type of custom middle-ware that encrypts/decrypts your data. Les Smith (http://www.sqlservercentral.com/columnists/lsmith) has developed a few methods on doing this. You can use these to get ideas. You could possibly create a user defined function to do something like this as well. Possibly wrap an extended stored procedure into it to encrypt/decrypt it.Brian Knightbknight@sqlservercentral.comhttp://www.sqlservercentral.com/columnists/bknight

RE: Encrypting Data With the Encrypt Function

MudLuck — Mon, 30 Jul 2001 12:49:00 GMT

This function does not exsist in 2000.So inless your living in the Stone age move on.

RE: Encrypting Data With the Encrypt Function

Santosh Benjamin — Thu, 19 Jul 2001 11:32:00 GMT

Supposing im creating a personnel database and want to store national insurance numbers etc , i want this to be encrypted in the table., But throught the client application front end , a personnel details administrator should be able to see the unencrypted data .. how would i do this ?Santosh Benjamin

RE: Encrypting Data With the Encrypt Function

Brian Knight — Mon, 16 Jul 2001 07:23:00 GMT

quote:
Encrypt function in T-SQL is fine what about Decryption of encrytion columns.kindly tell us how to do it!

As I mentioned in the article, the only way to decrypt is with a comparion techinque with a WHERE clause. That's why if you would like to use a more advanced encryption/decryption system, you may have to resort to COM. Les Smith will be having some excellent articles coming out this week and next about how to use this.Brian Knightbknight@sqlservercentral.comhttp://www.sqlservercentral.com/columnists/bknight

RE: Encrypting Data With the Encrypt Function

gvphubli — Sun, 15 Jul 2001 23:01:00 GMT

Encrypt function in T-SQL is fine what about Decryption of encrytion columns.kindly tell us how to do it!

Encrypting Data With the Encrypt Function

Brian Knight — Sun, 15 Jul 2001 00:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bknight/encryptfunction.asp