By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,608 Members | 1,925 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,608 IT Pros & Developers. It's quick & easy.

encrypt password column by keeping its data type varchar

P: 56
Expand|Select|Wrap|Line Numbers
  1. encrypting coloumn ..hiding password charecter 
  2.  
  3.  
  4. use CRM 
  5. create table h1
  6. (
  7. uname varchar(20) null,
  8. pwd varchar(20) null,
  9. [Encryptedpwd] varbinary(200) null
  10. )
  11.  
  12.  
  13. insert into h1 
  14. values ('teee' , 'cccc' , null)
  15.  
  16. CREATE MASTER KEY ENCRYPTION BY 
  17. PASSWORD = 'mypassword'
  18. GO
  19.  
  20.  
  21.  
  22. CREATE CERTIFICATE crm
  23.    WITH SUBJECT = 'pwd Encryption';
  24. GO
  25.  
  26. CREATE SYMMETRIC KEY  HRKey
  27.     WITH ALGORITHM = DES
  28.     ENCRYPTION BY CERTIFICATE crm;
  29. GO
  30.  
  31. OPEN SYMMETRIC KEY HRKey
  32.    DECRYPTION BY CERTIFICATE crm;
  33.  
  34.  
  35.    UPDATE [h1]
  36.    SET [Encryptedpwd] = EncryptByKey(Key_GUID('HRKey'), pwd );
  37. GO
  38.  
  39. use CRM 
  40. OPEN SYMMETRIC KEY HRKey
  41.    DECRYPTION BY CERTIFICATE crm;
  42. SELECT pwd, 
  43.     CONVERT(VARCHAR, DecryptByKey([Encryptedpwd])) 
  44.     AS 'Decrypted pwd'
  45.     FROM [h1]
  46. 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
Jan 6 '13 #1
Share this Question
Share on Google+
6 Replies


NeoPa
Expert Mod 15k+
P: 31,769
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).
Jan 6 '13 #2

Rabbit
Expert Mod 10K+
P: 12,430
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?
Jan 7 '13 #3

P: 56
@rabbit the point is that later from the front end the user name and password should be there for getting login
Jan 9 '13 #4

P: 56
@NeoPa thanks for the links
Jan 9 '13 #5

Rabbit
Expert Mod 10K+
P: 12,430
You can get it for login anyways. There's no need to store the encrypted data in character format.
Jan 9 '13 #6

NeoPa
Expert Mod 15k+
P: 31,769
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.
Jan 9 '13 #7

Post your reply

Sign in to post your reply or Sign up for a free account.