July 16, 2009 at 9:32 am
Hi all,
I am tasked with a general problem to come up with senarios and possible preevntive measures to take for the database of our product.
Our product could be installed in a Server environment managed by an IT department or on a laptops for field inspectors. So I need to come up with ways that our data could be compromised and possible steps to take to prevent it.
Senario 1: Laptop on which Sql Server is installed in stolen.
Senario 2: Database file is stolen
Senario 3: Since the OS-logins on laptops could be admins, they could use Trusted Connection (-E) to connect to database and compromise data. When the Field Inspectors are not paying attention, after loggin in, someone could connect to database and compromise/alter the data.
Senario 4: .....
What could be done to prevent or make it harder to break into.
Possible solutions:
Encrypt the table data
Use DDL triggers to prevent direct connection to database from command prompt or SSMS
Only allow connections from the application account and from webserver
....
Please share some info or point at an article that talks about this.
thanks a lot,
_UB
July 16, 2009 at 9:50 am
If someone gets hold of the data file they don't need to hack the windows password to use a trusted connection. They just have to attach the data file to another instance of SQL.
Table encryption won't stop that as the keys are in the database.
DDL logon triggers won't stop that.
SQL security won't stop that.
If you've got laptops in the field with confidential data on, look at something like bitlocker (if OS is Vista) to encrypt the drive. Other possibility is SQL 2008's transparent database encryption.
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
July 16, 2009 at 9:51 am
First, if someone gets administrative permissions on the machine, it doesn't matter what you do. The data will be compromised. Windows authentication is always enabled, so someone will get to your data. encryption of the data won't help.
In SQL Server 2008, you have TDE (transparent data encryption), http://qa.sqlservercentral.com/articles/Transparent+Data+Encryption/66334/, to protect the loss of the files.
As far as securing the laptop, I'd look at disk encryption and very short screen saver times that lock the machine. Train the people to lock their machines if they leave, and drill them on that.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 16, 2009 at 10:40 am
Thanks Gail and Steve.
Gail,
Database keys are stored in the database, but Service Master key is Instance level, right? And if there is a way to tie the instance key to unlock any database keys to be able to read data then may be, it'll work. But I am not sure, I vaguely remember the details of key-hierarchy.
I'll read more about the Disk Encryption and since we are on Sql 2005, I am not sure if TDE is possible. After I read more on TDE, I'll see if I can make a strong case to upgrade to SQL2008.
any more help is greatly appreciated,
_UB
August 24, 2011 at 9:21 am
What was the outcome? Thanks!
KU
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply