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)
LVL 1
AleksAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
The most secure way is not storing passwords at all. Store the hash of the password after adding a different salt per login.
AleksAuthor Commented:
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.
ste5anSenior DeveloperCommented:
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?
AleksAuthor Commented:
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.
ste5anSenior DeveloperCommented:
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.

hmm, when I create an e-mail, I'm able to read it. So I know what's in it.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.