how to lock stored procedure code without encryption?

Question

how to lock stored procedure code without encryption?

mizan700

SSC Enthusiast

Points: 193
More actions
July 19, 2016 at 10:18 pm

#328311

Dear Experts,
I want to hide/lock my some important store procedure code without encryption. no one can view my code,even not to see SA user of sql server 2012. Because any one easily get the encryption data by decryption. please assist me regarding this or any other way.
Regards
Mizan

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

There is no way to block SA from seeing the code.

Even if you keep it outside the database, a trace (extended events) or certain queries (DMVs) will show what is being executed.

You can preserve a version of the code in source control, if that's what you really want. But you can't block SA from seeing the code.

Why do you want to?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

There really is no way you can prevent somebody from viewing the code if they have access to the server. The ONLY way you can 100% prevent this is by hosting the database yourself and exposing the logic via webservices.

My question is why are you so concerned about the sql logic? There shouldn't be that much business logic wrapped in a procedure. If there is, I suspect you should do a better job of separating the layers in your application.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Smendle Hall of Fame Points: 3969 More actions · Answer 3

Just in case you were not already convinced there is no way to prevent an SA user from seeing your code if its housed on a server they have access too. In fact the "encryption" used to mask procedures is actually only at best an obfuscation of the object. A simple XOR operation can reveal the code quite easily. (I have had to "decrypt" objects on a server that were created "WITH ENCRYPTION")

Eric M Russell SSC Guru Points: 125560 More actions · Answer 4

Within the stored proceure, you could contain most of your T-SQL, or at least the most sensive parts, encrypted in a varbinary(max) variable. The symmetric key could then be supplied by the application as an input parameter. Use the key to decrypt the contents of the variable, and then execute the decrypted T-SQL text dynamically. However, even that could be defeated by the DBA using a SQL Profiler or SQL Audit event trace.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Eric M Russell (7/20/2016)
Within the stored proceure, you could contain most of your T-SQL, or at least the most sensive parts, encrypted in a varbinary(max) variable. The symmetric key could then be supplied by the application as an input parameter. Use the key to decrypt the contents of the variable, and then execute the decrypted T-SQL text dynamically. However, even that could be defeated by the DBA using a SQL Profiler or SQL Audit event trace.

And seriously...what t-sql could really be so sensitive? If there are business rules buried in there it is a good sign the business rules are in the wrong place.