Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 592
  • Last Modified:

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

0
Dishan Fernando
Asked:
Dishan Fernando
  • 2
  • 2
  • 2
  • +3
3 Solutions
 
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

I found the way to doing something like this.

SELECT PWDENCRYPT('d')

But its undocumented !!



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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 2
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now