Encryption MSSQL 7.0 Data & Table Structures.
BPMargolin
You can not encrypt table structures in MSSQL. Data encryption is something
you will have to handle at the client application level.
Rob Williams <r...@wisdomconsulting.com> wrote in message
news:uuB54.111$8n6...@news-server.bigpond.net.au...
> I cannot find how to encrypt data & table structures in MSSQL 7.0 - got
> triggers, stored procs & views encrypted but not getting anywhere with
table
> structures & data.
>
> Has anyone done this?
>
> Cheers,
> Rob.
>
>
Rob Williams
Neil Pike
> I cannot find how to encrypt data & table structures in MSSQL 7.0 - got
> triggers, stored procs & views encrypted but not getting anywhere with table
> structures & data.
Q. How do I encrypt fields in SQL Server? What about whole objects or the whole database?
(v1.9 1999.10.11)
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. A free-ware example
of this is available from www.vtc.ru/~andrey/xp_crypt
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. You can use the ODBC Encrypt function, but I don't think you can decrypt it. e.g. insert into x values ({Encrypt
N'Hello'}).
4. ** 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 found that the
passwords generated do not work in SQL 7.
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.
5. Wait and see if SQL 7.5/8.0 implements UDF's.
6. Use a DBMS that does support UDF's like DB/2, Oracle....
Note that any field you use a function on you won't be able to index effectively as indices are ignored when a
function is applied to a key in a where clause.
============
On to whole objects. SQL has a built-in function to encrypt stored-procedures - however the algorithms for 6.5 and
7.0 have been broken and so de-cryption is now possible if you know how. There is no encryption facility for
tables/data.
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. Windows 2000 comes with file level encryption with the new EFS (encrypted file
system) which can be used with SQL Server.
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 reply only to newsgroups)
SQL FAQ (374 Entries) see
sqlfaq.zip in lib 1 @ http://go.compuserve.com/sqlserver
or www.ntfaq.com/sql.html (+ ntfaq download)
or http://www.sql-server.co.uk