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
DishanF

LVL 8
Dishan FernandoSoftware Engineer / DBAAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
illConnect With a Mentor Commented:
create table users ( id int, name nvarchar(200), pwd nvarchar(200) )
GO
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
end
close c deallocate c
end
go
insert into users values ( 1, 'admin', 'encrypted')
insert into users values ( 2, 'admin2', 'moreencrypted')
insert into users values ( 3, 'admin3', 'mostencrypted')
GO
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
0
 
imrancsCommented:
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.


Imran
0
 
illCommented:
There is no encrypt option for  column is sql server.
i personally handle  password encryption with dynamically created SPs created "with "with encryption" option.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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 .
0
 
Dishan FernandoSoftware Engineer / DBAAuthor Commented:
Hi all

I found the way to doing something like this.

SELECT PWDENCRYPT('d')

But its undocumented !!



0
 
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.
0
 
ShogunWadeCommented:
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.
0
 
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.

http://www.xpcrypt.com/
0
 
Lori99Commented:
Since any of the recommended solutions should work I suggest:

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

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

Bob
0
All Courses

From novice to tech pro — start learning today.