June 21, 2008 at 12:54 am
Hi all...
I suppose to encrypt the password column in my login table.. For that i used the below Functions for implementing the Encryption and Decryption against a Input..
For Encryption
ALTER FUNCTION [dbo].[fnEncrypt]
(
-- Add the parameters for the function here
@key varchar(Max) = 'Testing',
@Input sql_variant,
@ID int = 1
)
RETURNS VarBinary(Max)
AS
BEGIN
--Variable Declaration
Declare @EncryptedOutput as VarBinary(max)
Declare @ExactInput as VarChar(max)
Declare @ExactId as VarBinary(Max)
--If Key has no value...
If Len(@Key) = 0
Set @key = '$Key has no Value$'
-- VarBinary Only allowed for encryption as 4th Argument
set @ExactId = CONVERT( varbinary(max), @ID)
-- VarChar Only allowed for encryption as 2nd Argument
Set @ExactInput = cast(@Input as varchar(max))
-- For Encryption
Select @EncryptedOutput = EncryptByPassPhrase(@Key, @ExactInput , 1, @ExactId )
-- Return the result of the function
RETURN @EncryptedOutput
END
For Decryption
ALTER FUNCTION [dbo].[fnDecrypt]
(
-- Add the parameters for the function here
@key varchar(Max) = 'Testing',
@Input sql_variant,
@ID int = 1
)
RETURNS varchar(Max)
AS
BEGIN
--Variable Declaration
Declare @DecryptedOutput as Varchar(max)
Declare @ExactInput as VarChar(max)
Declare @ExactId as VarBinary(Max)
--If Key has no value...
If Len(@Key) = 0
Set @key = '$Key has no Value$'
--VarBinary Only allowed for encryption as 4th Argument
set @ExactId = CONVERT( varbinary(max), @ID)
--VarChar Only allowed for encryption as 2nd Argument
Set @ExactInput = cast(@Input as varchar(max))
--For Decryption
Select @DecryptedOutput = CONVERT(varchar(max),
DecryptByPassphrase(@Key, Cast(@ExactInput as VarBinary(max)), 1, @ExactId)
)
-- Return the result of the function
RETURN @DecryptedOutput
END
In these functions i am just encrypting the data by using the Key which am passing randomly.
Is this is a better way for encrypting the data or any other algorithms ??
What about the Symmetric keys & Certificates usage??
Can anyone describe about this???
Ramkumar . K
Senior Developer
######################
No Surrender... No Give Up....
######################
June 21, 2008 at 10:09 am
This is a complicated subject and we can't necessarily give you a complete explanation on encryption. I'd suggest you read in BOL and articles on building encryption into an application. There are pros and cons of using each way.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 22, 2008 at 11:04 pm
Hi Steve..
Can you suggest any link regarding the BOL..
Because i have no idea Regarding that.. If i get a bit i ll proceed the next..
Thanks in Advance...
Ramkumar . K
Senior Developer
######################
No Surrender... No Give Up....
######################
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply