>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
>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.
MVP + MCP SQL Server and MCP Windows
Q. How do I encrypt fields in SQL Server? What about whole objects or the
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
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.