Link to home
Start Free TrialLog in
Avatar of Aleks
AleksFlag for United States of America

asked on

Encrypting data SQL Server with a KEY

I am looking for a tutorial on how to encrypt data on MS SQL (2008) with a key.
I am using classic ASP and I want to connect to the database and save encrypted password and retrieve passwords and decrypt for login into the application.
I found one on Lynda.com but it uses a passphrase and I rather use a key, I understand this is more secure.

Would this be a good example?

http://benjii.me/2010/05/how-to-use-sql-server-encryption-with-symmetric-keys

Is this the most secure way to save encrypted data (passwords)
Avatar of ste5an
ste5an
Flag of Germany image

The most secure way is not storing passwords at all. Store the hash of the password after adding a different salt per login.
Avatar of Aleks

ASKER

Because I need to decrypt the password so that the admin can send it to the user I decided to go with using symmetric keys and a certificate.
I got that going. All passwords are now encrypted in the database on a new column.

I got the 'loginid' encrypted with the code below:

--create database master key

use BlueDotPwd
create master key encryption by
password ='mystrongpassword99';
go

--create certificate
create certificate bluedot
with subject = 'bluedot';
go

--create symetric key
create symmetric key bdotpassword
with algorithm=triple_des
encryption by certificate bluedot;
go

--encrypt and copy data to new column
open symmetric key bdotpassword
decryption by certificate bluedot

update UserLogin
set LoginIdenctypted = encryptbykey (key_guid('bdotpassword'), LoginId);

close symmetric key bdotpassword;

go

Open in new window


Now I created a Stored Procedure to open the keys when I need to

CREATE PROCEDURE OpenKeys
AS
BEGIN
    

    BEGIN TRY
        OPEN SYMMETRIC KEY bdotpassword
        DECRYPTION BY CERTIFICATE bluedot
    END TRY
    BEGIN CATCH
        -- Handle non-existant key here
    END CATCH
END

Open in new window


Then I created two functions, one to encrypt and one to decrypt

---encrypt function

CREATE FUNCTION Encrypt
(
    @LoginId varchar(max)
)
RETURNS varbinary(256)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result varbinary(256)

    SET @Result = EncryptByKey(Key_GUID('bdotpassword'), @LoginId)

    -- Return the result of the function
    RETURN @Result
END

--decrypt function

CREATE FUNCTION Decrypt
(
    @LoginIdenctypted varbinary(128)
)
RETURNS varchar(max)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result varchar(max)

    SET @Result = DecryptByKey(@LoginIdenctypted)

    -- Return the result of the function
    RETURN @Result
END

Open in new window


All seems to be working fine, now I test the functions and display the columns I already have to see if the functions indeed work:

-- Encrypting
EXEC OpenKeys
SELECT dbo.Encrypt(LoginId) as encryptfunc, LoginId, LoginIdenctypted
FROM UserLogin 

-- Decrypting

EXEC OpenKeys
SELECT dbo.Decrypt(LoginIdenctypted) as decryptedwfunc, LoginId, LoginIdenctypted
FROM UserLogin

Open in new window


The Decrypt and the Encrypt function works, I was able to compare and they both look the same, so that was a success.
I just want to share this with you and tomorrow Ill test if I can call the function from the application and make it work.

I am using ASP and I am not sure how to call the Stored Procedure first and then the function.

For the function ill simply display  <%=Decrypt(recordset("loginidencrypted"))%>

But not sure how to call the SP or where to place it.
Because I need to decrypt the password so that the admin can send it to the user I decided to go with using symmetric keys and a certificate.

This requirement is nonsense from the security viewpoint. Having an admin sent an email with a password to a user, means that security is broken by design, because there is already a second person knowing the password.

When a user needs a password, then it should be reset and only the user should know it.

Maybe you're mixing sending an e-mail with 2-factor authentication.

And last but not least: Why don't you use the ASP.NET built-in security features?
Avatar of Aleks

ASKER

Because I am using asp classic.
Also a user can send a passwd to a client. They don't know what the passwd is because it's created with a script. It's random and only in the email. The person creating it doesn't know it.
And the first time user logs in they have to change it.
ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial