How to get back the hash values in sql server

Question

How to get back the hash values in sql server

deepak.a

SSCertifiable

Points: 5330
More actions
February 16, 2011 at 4:53 am

#234548

Hi All,
how to get back the actual value from the has values generated using the Hashbytes function
Ex : for this
SELECT HashBytes('SHA1', 'SQLSERVER');
I am getting this hash value : 0x8FC8F6B21EDA2AF2FA0A6A4366ECF408B74281CD
if i pass the same hash value (0x8FC8F6B21EDA2AF2FA0A6A4366ECF408B74281CD) i want to return the "SQLSERVER"
is there any sql function or any other algorithm to perform this.
Thanks & Regards
Deepak.A

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

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

HowardW One Orange Chip Points: 29109 More actions · Answer 1

The whole point of hashing algorithms is that they are not reversable. Unless there was a specific vunerability in the hashing algorithm you could not reverse it.

I'd suggest reading up on cryptographic hashing - the point of it is not to be able to unencrypt data, but to compare two hashes to see if they are the same (e.g. to check if an entered password is correct)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Hashbytes is a one-way hash. It's not reversible and that's the whole point of a hash.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

deepak.a SSCertifiable Points: 5330 More actions · Answer 3

hi Gail show and HowardW thanks for your reply.

I am looking something like encrypt and decrypt is there any other way in sql server we can acheive ?

Thanks and regards

Deepak.A

Lowell SSC Guru Points: 323518 More actions · Answer 4

deepak.a (2/16/2011)
hi Gail show and HowardW thanks for your reply.
I am looking something like encrypt and decrypt is there any other way in sql server we can acheive ?
Thanks and regards
Deepak.A

I really like this article from database journal; very easy to understand examples on using a certificate to encrypt and decrypt:

http://www.databasejournal.com/features/mssql/article.php/3714031/SQL-Server-2005-Encryption-types.htm

http://www.databasejournal.com/features/mssql/article.php/3483931/SQL-Server-2005-Security---Part-3-Encryption.htm

--http://www.databasejournal.com/features/mssql/article.php/3714031/SQL-Server-2005-Encryption-types.htm

--http://www.databasejournal.com/features/mssql/article.php/3483931/SQL-Server-2005-Security---Part-3-Encryption.htm

select EncryptedData = EncryptByPassPhrase('PEPE', '123456789' )

declare @val varbinary(max)

SET @val = 0x010000004A298120E629F7F4DC4AD09EB0E380C9E60040BDAE1DB6D8DA98C2A4D249EB71

select convert(varchar(100),DecryptByPassPhrase('PEPE',@val))

--------------------------------

if not exists(SELECT * FROM sys.certificates WHERE SUBJECT = 'Elmer Certificate')

BEGIN

CREATE CERTIFICATE ElmerEncryptionCertificate

WITH

SUBJECT = 'Elmer Certificate',

EXPIRY_DATE = '01/01/2021 '

PRINT '"Elmer Certificate" Created.'

END

ELSE

BEGIN

PRINT '"Elmer Certificate" Already Exists.'

END

DECLARE @encryptedstuff NVARCHAR(100)

SELECT @encryptedstuff = EncryptByCert(Cert_ID('ElmerEncryptionCertificate'), N'www.google.com/Search/Test')

SELECT @encryptedstuff

SELECT CAST(DecryptByCert(Cert_ID('ElmerEncryptionCertificate'), @encryptedstuff) AS NVARCHAR)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

deepak.a SSCertifiable Points: 5330 More actions · Answer 5

Hi Lowell,

Iam Getting this Error

Msg 15581, Level 16, State 1, Line 3

Please create a master key in the database or open the master key in the session before performing this operation.

on execting the script

Lowell SSC Guru Points: 323518 More actions · Answer 6

you'll really want to read the article; the master key is a prerequisite; once you have that, my snippet examples will work.

i *think* this will work no matter what?: