Encrypt a column

How do I encrypt a column in an SQL table - say I want to store passwords in
that column?
Thanks.
Assuming you are on SQL2005
How To: Encrypt a Column of Data
http://msdn2.microsoft.com/en-us/library/ms179331(en-US,SQL.90).aspx
Cryptographic Functions (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms173744.aspx
Encryption Hierarchy
http://msdn2.microsoft.com/en-us/library/ms189586(en-US,SQL.90).aspx
A couple of blogs as well
http://blogs.msdn.com/lcris/default.aspx
http://blogs.msdn.com/yukondoit/default.aspx
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"M$" <M$@.xyz.com> wrote in message
news:%23kne7taAGHA.1028@.TK2MSFTNGP11.phx.gbl...
> How do I encrypt a column in an SQL table - say I want to store passwords
> in that column?
> Thanks.
>
|||M$ wrote:

> How do I encrypt a column in an SQL table - say I want to store passwords in
> that column?
> Thanks.
M$ wrote:

> How do I encrypt a column in an SQL table - say I want to store passwords in
> that column?
> Thanks.
If you are using SQL Server 2005 you can use the EncryptByKey or
EncryptByPassPhrase functions to encrypt data in a column.
Password recovery mechanisms are an inherent security flaw however and
I recommend you avoid them. Don't store the password (encrypted or
otherwise) in the database. Instead, make a secure hash of the password
and store that. For example:
/* Store a password */
DECLARE @.pw NVARCHAR(256), @.salt NVARCHAR(36)
SET @.pw = 'foobar'
SET @.salt = CAST(NEWID() AS NVARCHAR(36))
UPDATE user_passwords
SET pw_salt = @.salt,
pw_hash = HashBytes('MD5', @.salt + @.pw)
WHERE userid = 123 ;
/* Retrieve and compare a password */
DECLARE @.pw NVARCHAR(256), @.userid INT
SET @.pw = 'foobar'
SET @.userid = 123
SELECT CASE pw_hash WHEN HashBytes('MD5', pw_salt + @.pw)
THEN 'Valid'
ELSE 'Invalid' END
FROM user_passwords
WHERE userid = @.userid ;
Conventional wisdom has it that the "salt" value limits the
effectiveness of possible password dictionary attacks. The reality in
today's environment though is that it's far more important to have
mechanisms to prevent users picking easy passwords and to monitor
intrusion attempts. Better yet, use asymetric methods of authentication
that don't require a central password store.
David Portas
SQL Server MVP