Aleks
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)
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)
The most secure way is not storing passwords at all. Store the hash of the password after adding a different salt per login.
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:
Now I created a Stored Procedure to open the keys when I need to
Then I created two functions, one to encrypt and one to decrypt
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:
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("logi nidencrypt ed"))%>
But not sure how to call the SP or where to place it.
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
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
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
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
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("logi
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?
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.