How do I encrypt fields in SQL Server?

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

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 Microsoft 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. SQL 2000 will implement user-defined functions. As long as you can write your own encrypt/decrypt function completely in TSQL then this will be an option.

6. Use a DBMS that already supports 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.


v1.11 2000.02.02
Applies to SQL Server versions : All
Related FAQ articles : n/a
Related Microsoft Kb articles : n/a
Other related information : n/a

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.