February 17, 2016 at 7:11 am
Hi,
I use encryption for my stored procedures but RedGate can decrypt it !
Whats the best way to encrypt objects securely?
February 17, 2016 at 7:21 am
It's not Redgate that can decrypt it. The tool just makes it easy to do so. Anyone with sysadmin access can decrypt the procedures.
There's no way to encrypt 'securely'. SQL is an interpreted language, so the query execution engine has to get at the raw text of the procedure to run it, so it's available if someone knows how.
Best thing to do is use permissions and restrict who has access to the procedures
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 17, 2016 at 8:42 am
There are some third-party apps like sql-shield that can securely encrypt. I'm looking for free solution 😛
February 17, 2016 at 9:31 am
I have to ask - what's in the stored procedures that you want/need to encrypt them?
February 18, 2016 at 2:18 am
JustMarie (2/17/2016)
I have to ask - what's in the stored procedures that you want/need to encrypt them?
It contains some secret formulas 😎
February 18, 2016 at 5:13 am
If your formulas are really that secret, the only way I can think of protecting them is to keep them on your own secure servers. Your customers could then do some sort of RPC over the internet, with something like Web Services, and get the results returned. I would be inclined to do the remote call from the middle tier but you could look into doing it from a CLR stored procedure.
ps I suppose an alternative would be to host the application yourself and let your customers have remote access.
February 23, 2016 at 12:15 pm
Regardless of how or where the T-SQL is stored at rest, it must be decrypted before SQL Server can compile and execute it. Any user with SYSADMIN privillage can use a profiler or extended event trace to see the clear text of each statement being executed. They can also query this from the plan cache using sys.dm_exec_sql_text. Perhaps the best approach for protecting your top secret algorithms would be in CLR assemblies / functions.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 23, 2016 at 12:36 pm
Eric M Russell (2/23/2016)
Regardless of how or where the T-SQL is stored at rest, it must be decrypted before SQL Server can compile and execute it. Any user with SYSADMIN privillage can use a profiler or extended event trace to see the clear text of each statement being executed. They can also query this from the plan cache using sys.dm_exec_sql_text. Perhaps the best approach for protecting your top secret algorithms would be in CLR assemblies / functions.
But if somebody has access to the server it is trivial to decompile a .net assembly.
_______________________________________________________________
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/
February 24, 2016 at 2:14 pm
Sean Lange (2/23/2016)
Eric M Russell (2/23/2016)
Regardless of how or where the T-SQL is stored at rest, it must be decrypted before SQL Server can compile and execute it. Any user with SYSADMIN privillage can use a profiler or extended event trace to see the clear text of each statement being executed. They can also query this from the plan cache using sys.dm_exec_sql_text. Perhaps the best approach for protecting your top secret algorithms would be in CLR assemblies / functions.But if somebody has access to the server it is trivial to decompile a .net assembly.
Ultimately the solution is to restrict who has SYSADMIN and Local Admin access to the server. By default, users with only exec permission on stored procedures can't view the schema or a T-SQL trace.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply