Escription Function

Question

Escription Function

Mauricio Morales S

Ten Centuries

Points: 1300
More actions
September 6, 2005 at 4:01 pm

#89754

There is any function or Store Procedure in SQL that let Encript the data into a Field?
Using a little key in order to avoid that the data could be readed directly using Enterprise Manager Console or Using the SQL Query Analizer. And in this way be mandatory use the function or Store Procedure for read, insert or update the data, into a specific table.

Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

There is no build in function that will encrypt data values.

This will be a large benefit for using managed code in sql 2005, but not available in sql out of the box. Search the script section of this site, I think there is a workaround there.

Edit:

I stand Corrected. There is an Encrypt function. But using will cause searching and working w/ data a little more difficult.

http://www.sqlservercentral.com/columnists/bknight/encryptfunction.asp

Decrypt issue

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=31&messageid=578

Possible Lead

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=199743

nigel. SSChampion Points: 11636 More actions · Answer 2

There are some useful encryption/decryption functions here:

http://www.sqlservercentral.com/scripts/contributions/610.asp

Nigel
How to post questions

Mauricio Morales S Ten Centuries Points: 1300 More actions · Answer 3

Mauricio Morales S

Ten Centuries

Points: 1300

September 7, 2005 at 7:25 am

#588077

Thank you all for the Help pthat you give me.

Ste_P SSCarpal Tunnel Points: 4430 More actions · Answer 4

Carlos,

There are some fairly undocumented and more importantly - unsupported - routines. pwdencrypt and pwdcompare.

So use at your own risk.

DECLARE @PINtoEncrypt varchar(255)

DECLARE @EncryptedPIN varbinary(255)

DECLARE @PasswordTest varchar(255)

SET @PINtoEncrypt = 'TEST'

SET @PasswordTest = 'tests'

SELECT @EncryptedPIN = CONVERT(varbinary(255), pwdencrypt(@PINtoEncrypt))

SELECT @PINtoEncrypt

SELECT @EncryptedPIN

SELECT pwdcompare(@PasswordTest, @EncryptedPIN, 0)

pwdcompare returns 1 for a match, 0 if they dont.

One point to note - these are not case sensitive......

Have fun

Steve

We need men who can dream of things that never were.

Jeff Moden SSC Guru Points: 1003893 More actions · Answer 5

Very, very cool... I like it.

Just so everyone is aware, though, this is a fairly weak form of encryption that contains it's own decoding key that changes based on the time of day (one each second, in fact). Any form of encryption that contains it's own key, especially based on time of day, is easily hacked by anyone dedicated to doing so. I'd be surprised if you didn't find the hack code somewhere in this very forum.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ste_P SSCarpal Tunnel Points: 4430 More actions · Answer 6

You are 100% right Jeff, it is quite a weak routine. Probably why it's undocumented and unsupported...

I liked it, thats why I kept it. I know there are better encryption functions written by members in here. It's just sharing the knowledge that these things are available - without knowing whats available, it's impossible to make a real decision on the way forward for your personal situation. It is simple and easy to use.

I will be having a root round for the code to break this. Been trying myself for a while to no avail.

If I find it, I will post it on here.

Have fun

Steve

We need men who can dream of things that never were.

Jeff Moden SSC Guru Points: 1003893 More actions · Answer 7

Steve, I thought your post was great! I don't know about anyone else, but I'm always interested in undocumented features that have been discovered.

I'm not in the business of hacking passwords and breaking encryption but it only took me a minute or two to realize that the first 8 significant characters where the key to breaking this encryption because they count up once per second at which time the whole encrypted password changes. Although I don't know what particular algorithym might come into play, I'm figuring that's a pretty easy hack for someone with that skill set.

I think it's awsome that you found this feature because it will certainly keep casual users from peeking at stuff they don't need to see on a casual basis... I'll likely use it for some of the minor security stuff I may have to do.

Thanks for sharing the code. I love this stuff!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)