How SQL 2008 Encryption methods can be used to prevent access to the database through SSMS RRS feed

  • Question

  • Hi All,

    I hv an application developed in .Net platform that uses SQL-2008 database. What is want is, how can I prevent access to the database by any mean (including administrators) through the SQL Server Management Studio or any other SQL connectivity tool other than my .Net Application.

    I've tried implementing Application Roles but when the SQL-Server is re-installed then the database can be accessed by the administrator or may be through  MDF/LDF files or Backup restores.

    I've seen some third party utilities ( BLOKKK for example) which encrypts the database through a password and it hides all objects from being viewed from the SSMS. later-on by providing a password it starts viewing in in the current session.

    My question is, though SQL-2008 introduces new methods of database encryption, how can it be possible to implement the above scenario ( I do not want to use that third party utility ).

    Thanks in Advance

    Saturday, August 1, 2009 12:11 PM

All replies

  • No can do. If you have your hands on the box and you throuh that can get access to the database files or backup file, then you can restore/attach into your own instance and you have access to all the data.

    Database ecryption (as of 2008) can somewhat help, since you can't do anything with database files or backup unless you also have the encryption keys etc. But if you can lay your hands on the box (say a laptop), you will most likely have the encryption keys etc on that box. Unless some HW module was used (like USB device) to stored those things, of course.

    Also, if you can get into an existing SQL Server database (encrypted or not) as sysadmin, you will be able to see everything, here database encryption won't help.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Saturday, August 1, 2009 4:05 PM
  • I understand that through encryption key it is possible to get the data back. but we'll ensure that the encryption key backup lies with a secure hand.

    at any cost, I wanted to block the un-authorized access to the database and its schema from being viewed in the SSMS.

    As I guess that a third party encryption tool named BLOCKK uses all the existing SQL-2008 commands and tools to encrypt the database. it then allow to view the schema in the current session with the following statement:

            OPEN SYMMETRIC KEY Blockkk DECRYPTION BY PASSWORD='YourEncryptionPassword'

    on the other hand as I investigate the master and my database, there're so many certificates and symmetric keys are configured.  that means it is already using the SQL-server inbuilt encryption tools.

    I just wanted to know how it is doing, so that I can implement in my App independently.

    So Many Thanks,

    Monday, August 3, 2009 4:34 AM
  • Roshan,

    Any progress? As I  re-searching on the same solution.

    Please visit my Blog for some easy and often used t-sql scripts
    Monday, January 3, 2011 6:34 AM