Encryption in sql server 2000

Hi experts

Can I encrypt some data ( like password ) using sql server. Is there any stored procedure to doing this?

Thank you

Dishan FernandoSoftware Engineer / DBAAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

illConnect With a Mentor Commented:
create table users ( id int, name nvarchar(200), pwd nvarchar(200) )
create trigger enc on users for insert, update as
if @@nestlevel=1 begin
declare @id int, @pwd nvarchar(200), @fname varchar(40)
declare c cursor  for select id, pwd from inserted

open c
fetch next from c into @id, @pwd
while @@fetch_status=0 begin
      set @fname= 'fenc'+ cast( @id as varchar(20))
      if exists (select * from dbo.sysobjects where id = object_id('dbo.'+ @fname) and xtype in (N'FN', N'IF', N'TF'))
            exec( 'drop function ' + @fname)
      exec( 'create function dbo.'+ @fname+'(@id int) returns nvarchar(200) with encryption as begin return('''+ @pwd+''') end')
      update users set pwd= cast( id as nvarchar(20)) where id= @id
      fetch next from c into @id, @pwd
close c deallocate c
insert into users values ( 1, 'admin', 'encrypted')
insert into users values ( 2, 'admin2', 'moreencrypted')
insert into users values ( 3, 'admin3', 'mostencrypted')
select * from users

select id, name, "pwd"= dbo.fenc1(id) from users where id=1
select id, name, "pwd"= dbo.fenc2(id) from users where id=2
select id, name, "pwd"= dbo.fenc3(id) from users where id=3
I think you want to encrypt Password of the users of your front end application, if so then i would  recommend that you should have your Encrypt and Decrypt functions to do so. If you are using .NET on front end then there is builtin class for encryption.

There is no encrypt option for  column is sql server.
i personally handle  password encryption with dynamically created SPs created "with "with encryption" option.
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Dishan FernandoSoftware Engineer / DBAAuthor Commented:
I think you all guys didnt understand my probleam. I want to Encrypt some data (Say "ABC" ) using some SQL. I can Encrypt those things in .NET .
Dishan FernandoSoftware Engineer / DBAAuthor Commented:
Hi all

I found the way to doing something like this.


But its undocumented !!

ShogunWadeConnect With a Mentor Commented:
PWDENCRYPT is the encryption procedure that sql uses to store passwords in syslogins.   I use this to store passwords myself but be aware that there is no PWDDECRYPT sp   you can compare an unencrypted string with a PWDENCRYPT'ed string using PWDCOMPARE which will give a 1 or 0 result.
Of course a further word of warning is that as PWDENCRYPT is a built in sp, there are hacker tools to decrypt it.   So for improved security you could adopt ill's suggestion of writing your own.    Then only you know the algorythm.
Lori99Connect With a Mentor Commented:
You can check out this product.  I have played with it a bit and it works well.  If all you need to encrypt is a password, I believe you could use the free version.  It is fully featured, just limited to the number of characters that can be encrypted.

Since any of the recommended solutions should work I suggest:

ill - 100 points
ShogunWade - 100 points
Lori99 - 50 points
Bob LearnedCommented:
You can split this yourself.  There is an option down towards the bottom to do that.

Bob LearnedCommented:
My bad, didn't read who asked the question :)

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.