Q: How to Encrypt and Decrypt

  • ahmadhassan

    SSC Enthusiast

    Points: 178

    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

  • Luis Cazares

    SSC Guru

    Points: 183499

    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 Enthusiast

    Points: 178

    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.

    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: 183499

    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 Enthusiast

    Points: 178

    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: 183499

    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 Enthusiast

    Points: 178

    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

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

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