passwords in SQL server

passwords in SQL server

Post by Douglas DeBor » Mon, 15 Mar 1999 04:00:00



Hello,

I have an app that stores a users password in a field in a database.  I was
wondering if there is a way to set a data type as "Password" or something
similar.  I don't want anyone to be able to see the passwords if they happen
to open this database.  Am I going about this the correct way?  Is there a
way to encrypt the password as it is saved to the database?

Thanks in advance for any assistance.

Doug

 
 
 

passwords in SQL server

Post by Gianluca Hot » Tue, 16 Mar 1999 04:00:00



>Hello,

>I have an app that stores a users password in a field in a database.  I was
>wondering if there is a way to set a data type as "Password" or something
>similar.  I don't want anyone to be able to see the passwords if they
happen
>to open this database.  Am I going about this the correct way?  Is there a
>way to encrypt the password as it is saved to the database?

Have a look at the below FAQ article.

HTH
--
Gianluca Hotz
MVP + MCP SQL Server and MCP Windows
http://users.alphasys.it/ghotz

Q.   How do I encrypt fields in SQL Server?  What about whole objects or the
whole database?
(v1.3 3.12.1998)

A. Taking the fields first - there is no supported, documented way of doing
this, and because you can't write user-defined functions yet then your
choices are :-

1.  Write your own extended-stored-procedure (XP) to do it.  However this
can't be applied as a function, so it is messy - you need to call the XP per
column and then issue an update with the value it returns.

2.  Do it in the application, back at the VB, C etc. level.  This is what
most people do and is the recommended method.

3.  ** This option only here for completeness **
There are undocumented pwdencrypt() and pwdcompare() functions - they are
for MS internal use and their function is likely to change/break in future -
people who ignored advice and used them in 6.x applications have reported
problems using them in SQL 7.0 Beta's.
As many people now know about these functions they are mentioned here for
completeness, but if you use these functions you will not receive support
from Microsoft and will be completely on your own when you got problems with
a new SP/version.

4.  Wait and see if SQL 7.1/8.0/whatever implements UDF's.

5.  Use a DBMS that does support UDF's like DB/2, Oracle....

On to whole objects.  SQL has a built-in function to encrypt
stored-procedures - however the algorithm for 6.5 and earlier has been
broken and so de-cryption is now possible.  The code for this is available
to subscribers of SQL Professional (www.pinpub.com).  There is no encryption
facility for tables, views etc.

Finally, the whole database.  There is no SQL wide encryption function that
would prevent users from hex-editing your devices and gleaning data from it.
It is possible to use NT file-system level encryption as SQL won't know it
is there.  However, you have to put a password to "unlock" the file(s)
somewhere if you want to automate the process of starting SQL after NT
re-boots.

You need to ask yourself if you need encryption - unless the person(s) you
are worried about have physical or NT network access to the SQL devices then
the only way to them is via a SQL logon which can be secured.

If the reason you want to prevent access is that the raw data/schema is
being hosted by a 3rd party/customer and you want to protect your
intellectual property rights, then currently all you can do is :-

Make all access to data via stored-procedures.  Put all logic you want to
hide in these.
Give users access to these sp's, but NOT to any underlying tables/views.
Then delete the syscomments entries for the sp's - this leaves the compiled
version in sysprocedures.

If you have to give someone sa rights to maintain the database then they
will still be able to get to the schema/data, but at least they won't be
able to see the stored-procedure code.

 
 
 

passwords in SQL server

Post by Neil Pik » Tue, 16 Mar 1999 04:00:00


Doug,

Quote:> I have an app that stores a users password in a field in a database.  I was
> wondering if there is a way to set a data type as "Password" or something
> similar.  I don't want anyone to be able to see the passwords if they happen
> to open this database.  Am I going about this the correct way?  Is there a
> way to encrypt the password as it is saved to the database?

Q.     How do I encrypt fields in SQL Server?  What about whole objects or the
whole database?
(v1.3  3.12.1998)

A. Taking the fields first - there is no supported, documented way of doing
this, and because you can't write user-defined functions yet then your choices
are :-

1.  Write your own extended-stored-procedure (XP) to do it.  However this can't
be applied as a function, so it is messy - you need to call the XP per column
and then issue an update with the value it returns.

2.  Do it in the application, back at the VB, C etc. level.  This is what most
people do and is the recommended method.

3.  ** This option only here for completeness **
There are undocumented pwdencrypt() and pwdcompare() functions - they are for
MS internal use and their function is likely to change/break in future - people
who ignored advice and used them in 6.x applications have reported problems
using them in SQL 7.0 Beta's.
As many people now know about these functions they are mentioned here for
completeness, but if you use these functions you will not receive support from
Microsoft and will be completely on your own when you got problems with a new
SP/version.

4.  Wait and see if SQL 7.1/8.0/whatever implements UDF's.  

5.  Use a DBMS that does support UDF's like DB/2, Oracle....

On to whole objects.  SQL has a built-in function to encrypt stored-procedures
- however the algorithm for 6.5 and earlier has been broken and so de-cryption
is now possible.  The code for this is available to subscribers of SQL
Professional (www.pinpub.com).  There is no encryption facility for tables,
views etc.

Finally, the whole database.  There is no SQL wide encryption function that
would prevent users from hex-editing your devices and gleaning data from it.  
It is possible to use NT file-system level encryption as SQL won't know it is
there.  However, you have to put a password to "unlock" the file(s) somewhere
if you want to automate the process of starting SQL after NT re-boots.

You need to ask yourself if you need encryption - unless the person(s) you are
worried about have physical or NT network access to the SQL devices then the
only way to them is via a SQL logon which can be secured.  

If the reason you want to prevent access is that the raw data/schema is being
hosted by a 3rd party/customer and you want to protect your intellectual
property rights, then currently all you can do is :-

Make all access to data via stored-procedures.  Put all logic you want to hide
in these.
Give users access to these sp's, but NOT to any underlying tables/views.
Then delete the syscomments entries for the sp's - this leaves the compiled
version in sysprocedures.

If you have to give someone sa rights to maintain the database then they will
still be able to get to the schema/data, but at least they won't be able to see
the stored-procedure code.

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 (Please post ALL replies to the newsgroup only unless indicated otherwise)
 For SQL FAQ entries see www.ntfaq.com/sql.html
 and http://www.swynk.com/faq/sql/sqlserverfaq.asp
 and GO MSSQL Lib 1 on Compuserve