Re: Encrypt a column in a table

Question

Post reply

Re: Encrypt a column in a table

AJ-92543

SSC Eights!

Points: 852
More actions
February 11, 2005 at 12:57 pm

#163682

Hi,
How do you encrypt a password column in a SQL Server table?
Thanks!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Digs SSCertifiable Points: 7333 More actions · Answer 1

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=169&messageid=161176

AJ-92543 SSC Eights! Points: 852 More actions · Answer 2

AJ-92543

SSC Eights!

Points: 852

February 14, 2005 at 6:21 am

#542025

Thanks Digs, for the helpful info.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Storing a password in a table, isn't the best solution, IMO. however, if you need to, consider doing the encryption at the application level. Can find the thread right now, but Brian Kelley has posted something on this topic here recently, IIRC.

And fwiw, have a look here: http://www.codeproject.com/database/xp_md5.asp

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

AJ-92543 SSC Eights! Points: 852 More actions · Answer 4

Hi Frank,

Thanks for the suggestions. Does this means the users will require to log in to the database whenever they open the database?

TiA!

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

I have an application here where the user connect via NT-Authentication and are additionally asked for a password. Don't ask why! I guess due to the lack of knowledge of the developer of that app.

The only case I can think of where this makes sense to me, is when your app has its own permission system so that different users do have different permissions within the app. Can you describe in which context you want to use this?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

AJ-92543 SSC Eights! Points: 852 More actions · Answer 6

Hi Frank,

Thank you again for replying.

I have an Access form which contains some check boxes. Some of these check boxes require authorization on scrapping or accepting the parts. If an user clicks on it I would like an input box to pop up and prompt user for the password but the password should display encrpted or something like ***. Then the entered value is to compare w/ the encrpted password field in SQL Server. I need the table in SQL SERVER 2000 to store authorized personnel's password inencrpted so users have permission to the db will not be able to read the password in that table.

What is the best way to accomplish this?

Thank you much!

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

So, I would search the web for a strong encrypting algorithm for Access VBA. Haven't tried myself, but I think it shouldn't be too difficult to find a complete working example on Access resources. I also think, it's much better to look for a client-side encryption here than an server-side solution. You can do encrypt/decrypt thingy in your client code and only need to store the encrypted password in a colum inside your table.

And for the record. A very, very basic encryption routine in T-SQL might look like this:

CREATE FUNCTION dbo.simpleencrypt (@input VARCHAR(10), @crypt TINYINT)

RETURNS VARCHAR(10)

AS

BEGIN

DECLARE @result VARCHAR(10), @i INT, @Tmp TINYINT

SET @i=1

SET @result=''

WHILE @i<=LEN(@input)

BEGIN

SET @Tmp=ASCII(SUBSTRING(@input,@i,1))

SET @result=@result + CHAR(@tmp ^ @crypt)

SET @i=@i+1

END

RETURN @result

END

GO

SELECT dbo.simpleencrypt('FRANK',10)

SELECT dbo.simpleencrypt('LXKDA',10)

DROP FUNCTION dbo.simpleencrypt

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

AJ-92543 SSC Eights! Points: 852 More actions · Answer 8

Hi Frank,

Thanks for the code.

What's the difference between User Defined Function and Stored Procedure? How do you use/call User Defined Function?

Thanks again.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

Uh, that's a broad topic. You might want to read BOL up first on these topics.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]