encrypt password column by keeping its data type varchar

[Syed Hadi]

encrypting coloumn ..hiding password charecter 


use CRM 
create table h1
(
uname varchar(20) null,
pwd varchar(20) null,
[Encryptedpwd] varbinary(200) null
)


insert into h1 
values ('teee' , 'cccc' , null)

CREATE MASTER KEY ENCRYPTION BY 
PASSWORD = 'mypassword'
GO



CREATE CERTIFICATE crm
   WITH SUBJECT = 'pwd Encryption';
GO
 
CREATE SYMMETRIC KEY  HRKey
    WITH ALGORITHM = DES
    ENCRYPTION BY CERTIFICATE crm;
GO

OPEN SYMMETRIC KEY HRKey
   DECRYPTION BY CERTIFICATE crm;
   
   
   UPDATE [h1]
   SET [Encryptedpwd] = EncryptByKey(Key_GUID('HRKey'), pwd );
GO

use CRM 
OPEN SYMMETRIC KEY HRKey
   DECRYPTION BY CERTIFICATE crm;
SELECT pwd, 
    CONVERT(VARCHAR, DecryptByKey([Encryptedpwd])) 
    AS 'Decrypted pwd'
    FROM [h1]
GO

pwd is the coloum containing the password i encrypted this to new coloumn but ..i want to encrypt the pwd coloumn only by keepin its data type varchar can anyone support ...please for that

[NeoPa]

My best guess is that the OP is looking for a way to encrypt string data before saving that away in his [PWD] column. I have no idea what "by keepin its data type varchar" means.

The best I can do at this stage is to link to a couple of articles on encryption. How they would be incorporated into T-SQL is something I can't help with (AES Encryption Algorithm for VBA and VBScript; RC4 Encryption Algorithm for VBA and VBScript; SHA2 Cryptographic Hash Algorithm for VBA and VBScript).

[Rabbit]

I don't see any reason whatsoever to store the encrypted data in a character format. It's going to be mostly human-unreadable anyways so what's the point?

[Syed Hadi]

@rabbit the point is that later from the front end the user name and password should be there for getting login

[Syed Hadi]

@NeoPa thanks for the links

[Rabbit]

You can get it for login anyways. There's no need to store the encrypted data in character format.

[NeoPa]

Syed:
the point is that later from the front end the user name and password should be there for getting login

Would it not make more sense to store the information as to who the user is, rather than what their security credentials are. I'm not sure I really follow your logic here.

I can imagine you may need to connect to tables from some other BE at some point, but even then having it in any format other than the one the BE requires is pointless surely.

Maybe I misunderstand you completely, but certainly your explanation makes no sense to me.