Q: How to Encrypt and Decrypt

Question

Q: How to Encrypt and Decrypt

ahmadhassan

SSC Veteran

Points: 218
More actions
December 13, 2016 at 9:06 am

#319744

Hello all,
I have a Database name 'ahmed' and more tables
And have table name "Users"
Users Table contain
UserNo. numeric(18, 0) Not Nulls
UserName. varchar(100) Allow Nulls
Password. varchar(50) Allow Nulls
Q: How can Encrypt password and showing hash when any one open users table and how can I Decrypt to showing original pass?
Thanks for your support and I am waiting your answer

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

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

Here's a basic process I wrote for encrypting other values. However, you shouldn't decrypt your passwords, you should just compare the hashes. Otherwise, it's a security vulnerability.

Process:
1)Create the Master Key
•CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Insert1StrongPasswordHere!';
2)Create Certificate
•CREATE CERTIFICATE CertificateName WITH SUBJECT = 'field in the metadata of the certificate as defined in the X.509 standard’;
3)Create Symmetric Key
•CREATE SYMMETRIC KEY SimmetricKeyName WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE CertificateName;
4)For each use, the key should be opened before use and close after it. This process takes 1.5 milliseconds to complete on a workstation and will depend on the server’s capabilities.
•OPEN SYMMETRIC KEY SimmetricKeyName DECRYPTION BY CERTIFICATE CertificateName;
•CLOSE SYMMETRIC KEY SimmetricKeyName
5)For encryption, use EncryptByKey preferably with an authenticator
•EncryptByKey(Key_GUID('SimmetricKeyName'), SSNColumn, 1, AuthenticatorColumn)
6)For decryption, use DecryptByKey which would need the authenticator used to encrypt
•CONVERT(nvarchar(30), DecryptByKey(SSNEncryptedColumn, 1 , AuthenticatorColumn))

Additional information: https://www.simple-talk.com/sql/t-sql-programming/encryption-without-the-confusion/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ahmadhassan SSC Veteran Points: 218 More actions · Answer 2

Luis Cazares (12/13/2016)

Here's a basic process I wrote for encrypting other values. However, you shouldn't decrypt your passwords, you should just compare the hashes. Otherwise, it's a security vulnerability.

Additional information: https://www.simple-talk.com/sql/t-sql-programming/encryption-without-the-confusion/

I login to https://www.simple-talk.com/sql/t-sql-programming/encryption-without-the-confusion/

and at the first step showing error, I am sorry but you can explain step by step. 🙂

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

Are you sure that you're using SQL Server 2005?

What happens if you run the following?

SELECT @@VERSION

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ahmadhassan SSC Veteran Points: 218 More actions · Answer 4

Luis Cazares (12/13/2016)
Are you sure that you're using SQL Server 2005?
What happens if you run the following?
SELECT @@VERSION

Luis Cazares SSC Guru Points: 183698 More actions · Answer 5

Those are not the expected results. Actually, there are no results. Be sure to run the code.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ahmadhassan SSC Veteran Points: 218 More actions · Answer 6

Luis Cazares (12/13/2016)
Those are not the expected results. Actually, there are no results. Be sure to run the code.

Sorry I upload wrong pic