Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to Encrypt Database Objects in SQL Server 2008R2


How to Encrypt Database Objects in SQL Server 2008R2

Author
Message
Dev
Dev
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1944 Visits: 1598
azhar.iqbal499 (12/1/2011)
Thanks.
I just want to save my database from any user at live, In case if He would be able to reach the database then He must not be able to see the code of database objects.
So What security meausres should I take to acheive this goal.

Thanks


I believe you need a proper User Access management than encryption. I would restrict a user at following levels...
Server Login Level
Database User Level
Schema Level
Object Level
Object level Encryption (SP / View)

Dev

*** Open Network for Database Professionals ***
http://www.linkedin.com/in/devendrashirbad

Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8780 Visits: 16554
azhar.iqbal499 (12/1/2011)
In case if He would be able to reach the database then He must not be able to see the code of database objects.
So What security meausres should I take to acheive this goal.

Thanks

anybody granted access to the database will have public access which provides the ability to view certain objects. Exactly what is it you dont want users to see?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
EdVassie
EdVassie
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3144 Visits: 3815
The DBA of a production system will have rights to see all atabase objects.

If you use 'encrypted' stored procedures and functions, the DBA will not be able to view the contents of these in SSMS, but as Gail says this is just obfuscation and is trivial to reverse.

If this is an in-house application then you have next to zero justification for trying to hide your database objects from the production DBA. You just make your system less reliable to operate.

If this is a system that is to be sold, then normal practice is that you protect yourself with contractural agreements, not by trying to hide the internals of your system.

Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 1 Dec 2016: now over 39,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Quote: "When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist." - Archbishop Hélder Câmara
Sagesh
Sagesh
SSC-Enthusiastic
SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)

Group: General Forum Members
Points: 112 Visits: 220
Use WITH Encryption Keyword with all the SQL objects while installing on the production system.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47189 Visits: 44356
Sagesh (10/11/2012)
Use WITH Encryption Keyword with all the SQL objects while installing on the production system.


As I mentioned earlier in this thread, that's not actually encryption and it's trivial to reverse.


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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


swoozie
swoozie
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 403
What you describe sounds more like you need to implement a strong role based security model not TDE encryption as a solution to your problem.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search