SQL Server - hide password 1

[katehol]

I am trying to find out how to hide/obscure a password in SQL server. Currently the passwords are being held as a text field, which means that anyone who queries the table or just opens it can see everyones passwords.

Is there some way of hiding this? I was just wondering if there was a function/bit of code that would change a column so that the data was hidden. eg ALTER TABLE Users ALTER COLUMN password xxxxxx...

Thanks

 

[philhege]

You can encrypt/decrypt the data natively in SQL 2K5. 2K requires third-party solutions.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Pity the insomniac dyslexic agnostic. He stays up all night, wondering if there really is a dog.

 

[katehol]

I do use SS2005.

Would this be possible to do to a whole columns-worth of data? What function would be used for this? And if I were to encrypt it, would someone else be able to decrypt it?

Thanks

 

[philhege]

Open Books Online and paste this link in the address window:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/d792dc22-e978-46b0-a04b-e8b9e4eda250.htm

 

[katehol]

Unfortunately, encryption is not working, as the password is used to sign on to a site and therefore the application would need to decrpt the password first - all getting a bit complex!

Is there not a simple way just to hide the data in one column in SQL server? It seems so basic, but I can't find out how to do it! In MS Access there was a datatype of password - is there not a similar data type or option within SQL Server?

Thanks for any help - this is driving me mad!

 

[TysonLPrice]

Is locking down the table to the application an option and then creating a view for the table (minus password) for other uses?

 

[scorpio66]

Could you not create a view containing all of the columns except your password col and lock down the original table so that no-one could select from it?

That would stop someone casually looking through the passwords (except a DBA, of course)

Chaz

 

[katehol]

Thanks Tyson & Chaz,

I was hoping that there would be an option to simply hide the column, but it's looking unlikely. This may well be my only option...

It seems ridiculous that you can't simply hide a column! If you can do it in Access - the most simple of databases, then why not SQL Server?!?!

 

[scorpio66]

Kate,

There is one more thing - SQL2K supports some undocumented encryption functions which can be used to obscure the password.

As stated, these are undocumented - I don't know if they're in SQL2K5.

select pwdencrypt('HELLO WORLD')
select pwdcompare('HELLO WORLD', encrypted_value)

You'd have to encrypt all of your fields and once done, it is not trivial to get the original values back again.

Chaz

 

[katehol]

Thanks for that Chaz,

I have already tried the pwdencrypt function, but unfortunately as it is used to log on to a web based application, we would need to build the decrypt function into the application, which is currently not possible.

Thanks anyway. )

 

[mrdenny]

It is recommened that you not use the pwdencrypt functions as the hashing methods which Microsoft uses change between versions and you could end up with hashs that the next release of SQL can't deal with.

This FAQ over on the admin forum covers using the encryption within SQL 2005 faq962-5964.

No there is no way to simply hide a column as there is no reason to do so. Any one with sysadmin rights, or ownership rights to the database would be able to unhide the column.

Microoft SQL Server and it's client tools are development and management tools. They are not designed for end users to use as a front end application (which is why you can't design forms directly in SQL Server like you can in Access). If you don't want users to be able to see a column or table you simply do not give them rights to that column or table.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[scorpio66]

All excellent points Denny. I've never considered the possibility that MS would change the hashing algorithm so that definitely deserves a star.

Kate, if you do decide to use pwdencrypt (and seriously consider Denny's concerns) then you do the authentication on the server. For example,

create procedure prc_IsAuthenticated(@userId int, @password nvarchar(64))
as
begin
  if exists(select 1 from userTable where userId = @userId and encryptedPassword = pwdencrypt(@password)
    return 1
  else
    return 0
end

This code hasn't been tested.

Chaz

 

[scorpio66]

All excellent points Denny. I've never considered the possibility that MS would change the hashing algorithm so that definitely deserves a star.

Kate, if you do decide to use pwdencrypt (and seriously consider Denny's concerns) then you do the authentication on the server. For example,

create procedure prc_IsAuthenticated(@userId int, @password nvarchar(64))
as
begin
  if exists(select 1 from userTable where userId = @userId and encryptedPassword = pwdencrypt(@password))
    return 1
  else
    return 0
end

This code hasn't been tested.

Chaz