4

Background

I am using SQL Server 2008 Enterprise on Windows Server 2003 Enterprise. I developed some stored procedures for SQL Server and the machine installed with SQL Server may not be fully under my control (may be used by un-trusted 3rd party).

Question

I want to protect my stored procedures T-SQL source code (i.e. not viewable by some other party) by using encrypt stored procedure function provided by SQL Server. I am not sure whether encrypt stored procedure is safe and whether the administrator of the machine (installed with SQL Server) will have ways to view stored procedure's source code?

Community
  • 1
George2
  • 1,137
  • 6
  • 22
  • 41

3 Answers3

3

It is possible to decrypt and view the source of encrypted SQL stored procedures, a quick search for "Decrypt sql stored procedure" shows you a fair number of hits, for example:

http://www.mssqltips.com/tip.asp?tip=1046

However encrypting your stored procedure does at least make it more difficult to see the source - I'm also not sure whether any of the current methods of decrypting stored procedures are actually viable on a live SQL server installation - last time I looked many methods required exclusive access to the SQL server instance, and prevented any other users from accessing the server (although this may not still be true).

Its also worth noting that encrypting your stored procedure can raise support headaches - because you are no longer able to see execution plans for your encrypted stored procedures you may have problems attempting to diagnose any performance issues.

I'm not aware of any other method of protecting your source.

Justin
  • 379
  • 6
  • 13
  • 1
    Red Gate's SQL Compare is one of the tools that can decrypt stored procs without requiring exclusive access, although you still do need admin access. –  Dec 14 '09 at 17:44
2

To clarify marc_s answer (deleted now)

For SQL Server 2005 and above, the threshold for getting at the "plain text" is higher than before. Basically, sysadmin over DAC. At this point, you're already pwned anyway.

For SQL Server 2000 and before, it was a lot easier. A hard stare, quite frankly, did the job.

So, it's safe enough for end users and developers (without sysadmin) but not if want to use it to protect IP at a client's site.

gbn
  • 6,009
  • 1
  • 17
  • 21
  • pwned? do you mean "owned"? Or is this a DBA-slang I'm not familiar with? :-) – marc_s Dec 14 '09 at 17:52
  • 1
    @marcs: Do keep up! 21st century y'know ;-) http://en.wikipedia.org/wiki/Pwn "...mid-2000s..." – gbn Dec 14 '09 at 17:54
0

"I would like to package my database application in a form that would allow a customer to use it, but without him ever being capable to access the actual data stored in it. I think encrypting the database should help".

the answer is alwasy the same: what you ask for is called Digital Rights Management and SQL Server does not support DRM. This answer applies whether you refer to data, to schema design or to stored procedures logic. See Who needs encryption? for a more detailed discussion.

Remus Rusanu
  • 8,253
  • 1
  • 19
  • 22