Post reply

How to Encrypt Database Objects in SQL Server 2008R2

  • November 30, 2011 at 12:24 am

    #248745

    I am using SQL Server 2008R2, I want to encrypt all database objects before sending these to Production.

    I want this to avoid any changes in the objets in Live Environment.

    What is the best way to do this and what are the pros and cons of this.

    Thanks.

    Azhar Iqbal

  • November 30, 2011 at 12:41 am

    #1414463

    I am recommending a lengthy but good article below. It will help you narrow down the term Encryption (for database / column etc.)

    Database Encryption in SQL Server 2008 Enterprise Edition

    http://msdn.microsoft.com/en-us/library/cc278098(v=SQL.100).aspx

  • November 30, 2011 at 10:22 pm

    #1415023

    azhar.iqbal499 (11/30/2011)

    I want this to avoid any changes in the objets in Live Environment.

    You have to consider permissions also. Grant minimum permission to the users.

    By revokeing ALTER permission, you can avoid changes.

  • December 1, 2011 at 12:06 am

    #1415043

    I have implemented TDE at Database. I want to test the TDE. What is the procedure of this. Should I create new user for this or should I take backup to test.

    Please help

  • December 1, 2011 at 12:20 am

    #1415049

    azhar.iqbal499 (12/1/2011)

    I have implemented TDE at Database. I want to test the TDE. What is the procedure of this. Should I create new user for this or should I take backup to test.

    Please help

    TDE will not prevent valid users from altering the objects.

    So creating new user is not required.

    Yes, backup/restore test you should do.

  • December 1, 2011 at 12:54 am

    #1415065

    I have created TDE from sa user and I logged in with another user with same rights. But all DB objects were looking unencrypted.

    My Question is that When this database would be uploaded at Liver Server then how it would be protected from attacks. Should I use other users for this? I want to test these scenarios at dev environment before shift it to Live.

    I think you understand my Question.

    Thanks for reply.

  • December 1, 2011 at 2:02 am

    #1415075

    i think the question is, how do you get the padlock on objects like some 3rd parties do, one example is LiteSpeed, where the properties of the object show as Encrypted and you cannot right click and modify the object via SSMS or by doing a sp_helptext on the object

  • December 1, 2011 at 7:21 am

    #1415243

    azhar.iqbal499 (12/1/2011)

    I have created TDE from sa user and I logged in with another user with same rights. But all DB objects were looking unencrypted.

    Yes, because TDE is encryption of the database file to prevent people from attaching it on other servers. Nothing whatsoever to do with the objects in the DB.

    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

  • December 1, 2011 at 7:24 am

    #1415247

    anthony.green (12/1/2011)

    i think the question is, how do you get the padlock on objects like some 3rd parties do, one example is LiteSpeed, where the properties of the object show as Encrypted and you cannot right click and modify the object via SSMS or by doing a sp_helptext on the object

    Just bear in mind that is not encryption. It's nothing more than a bit of obfuscation and extra checks by SQL. 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

  • December 1, 2011 at 10:08 pm

    #1415685

    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

  • December 1, 2011 at 11:46 pm

    #1415704

    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)

  • December 2, 2011 at 2:14 am

    #1415753

    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" 😉

  • December 19, 2011 at 10:01 am

    #1423198

    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: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    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

  • October 11, 2012 at 6:19 am

    #1548047

    Use WITH Encryption Keyword with all the SQL objects while installing on the production system.

  • October 11, 2012 at 6:37 am

    #1548055

    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

Viewing 15 posts - 1 through 15 (of 15 total)

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