The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

SQL Server - Hiding stored procedure code

Hi all,

I have a SQL Server 2005 database deployed to clients, I would like them to be able to backup and restore - but I would like to protect my stored procs from being viewed.... is this possible?
Marc A Send private email
Wednesday, October 10, 2007
 
 
create procedure ...
with encryption
Friday
Wednesday, October 10, 2007
 
 
You should be able to encrypt your stored procedures in the database (you can up to SQL Server 2000 at least). It's not very strong protection and a lot of people will say it is inadvisable, but you have the option.
anomalous
Wednesday, October 10, 2007
 
 
No.

SQL Server does provide for encryption of stored procedures, but naturally it's been cracked.

From a theoretical standpoint, you're necessarily putting both the stored procedure and whatever key you use to encrypt  it in the hands of the client. His computer needs the key in order to decrypt and run the sproc, and his computer is under his control. Therefore whatever scheme you devise can be broken.
clcr
Wednesday, October 10, 2007
 
 
The CREATE PROCEDURE topic in SQL Server 2005 help mentions this a bit. In general, each encryption scheme is "broken" by the time the next version comes out. For example, a decryption stored proc that decrypts encrypted SQL Server 7 stored procs requires datatypes available in SQL Server 2k.

The text of a stored procedure is stored in syscomments.

If you *really* need to hide it, you may wish to consider CLR stored procs.
Peter Send private email
Wednesday, October 10, 2007
 
 
"If you *really* need to hide it, you may wish to consider CLR stored procs."

Very decompilable, and still under control of the user.

Ok, let's talk fundamentals for a moment. There are a few things that the OP needs to understand, and that anyone ought to understand before they offer advice on this sort of thing. If you want to keep the user from seeing something, there's only one way: make sure that it's never in their control. That means that you never have an unencrypted copy on their machine, nor do you have both an encrypted copy and the key on their machine. Anything else is just obfuscation. Obfuscation can be useful, and it might be enough for the OP's needs, but it does not prevent the user from viewing the thing you want to protect.

Ultimately the only solution to the OP's problem is for him to host the database himself. Whether lesser solutions that make it harder for the user to get at the sproc, rather than impossible, are adequate for the OP depends on things that we haven't been told.
clcr
Wednesday, October 10, 2007
 
 
LOL @ Friday... :)

Clcr is entirely right of course - which makes me amused whenever people come to this forum asking how to "protect their code". Way to annoy customers by chasing an impossible dream!
Don't fix what ain't broke.
Thursday, October 11, 2007
 
 
"Way to annoy customers by chasing an impossible dream!"

If your dream is to prevent *anyone* from viewing your source then yes. But that is hardly what the OP is trying to do. He is just trying to obsfucate his source as much as possible.

This won't prevent leet hackers such as yourself from accessing his source but for the other 99% it's probably good.

If everyone chose your philosophy no software would ever be sold. But clearly selling obsfucated software is a viable business model - whereas selling open source software is not so viable.
Invader Zim
Thursday, October 11, 2007
 
 
Marc A.: "I have a SQL Server 2005 database deployed to clients, I would like them to be able to backup and restore - but I would like to protect my stored procs from being viewed.... is this possible?"

Don't give them query access to system tables. Don't give anyone at your client's DBA privileges. Don't allow anyone there to give users access to system tables.

Other than that, not much you can do. If they have the database, they have the source.

That being said, if you provided something vital to my business and didn't provide me with the ability to get to the source if something happened to you, I'd soon be finding a replacement.
Ken White Send private email
Wednesday, October 24, 2007
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz