Yes. The functions are PWENCRYPT and PWCOMPARE. This basically allows the ability to encrypt a value on an insert/update and offer a comparison of the value on a select. There isn't a decryption function available.
Here are a few T-SQL statements to illustrate how pwencrypt/pwcompare work:
create table #MetroTest
(
UserLogIn varchar(10),
UserPass nvarchar(256)
)
insert #MetroTest
(UserLogIn,UserPass) values ( 'MaryMary', PWDENCRYPT('QuiteContrary'))
select UserLogIn, password = 'QuiteContrary', PWDCOMPARE('QuiteContrary', UserPass, 0) from #MetroTest
select UserLogIn, password = 'QuietContrary', PWDCOMPARE('QuietContrary', UserPass, 0) from #MetroTest
It is possible to write user defined functions which will encrypt/decrypt and compare. An excellent example of this is Peter Larsson's RC4 hash:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76258