October 4, 2012 at 4:06 pm
Hello all, to say that I am new to SQL would be an understatement. At my company, I have been tasked to secure our SQL server. What I am finding is that it doesn't matter which authentication method I use, someone could still dump the database to say, a thumbdrive, move it to another computer with a different installation of SQL Server 2005 Express, and use SQL Server Management Studio to attach the database and view its contents. I have been testing this with different computers on and off my domain with different installations of SQL Server 2005 Express. The databases I have been using to test are the AdventureWorks databases so no real data is at risk. So, other than encrypting my computer that stores the database, how can I prevent what I mentioned above from occurring? Thank you so much for your time. -Steve0
October 5, 2012 at 5:24 am
Securing a SQL Server is an extremely vast topic and requires a comprehensive approach not limited to just worrying about who has access to the instance as a sysadmin or the data files, but everything from those items to the code running on the instance, the features that are enabled, and where and how the backups are stored, among many other things. Here is a good place to start to get a view of what you need to consider:
SQL Server 2005 Security Best Practices
Outline your security goals and go from there. Every environment is different and while "protecting the data" has to be the first priority that phrase can take on many different meanings depending on the type of data and things unique to the environment.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 9, 2012 at 11:12 am
You can use third party products to encrypt your backups or databases. If this is Express, you can't use TDE, and the backup password isn't really encryption.
If someone has sa/sysadmin rights on their local Express instance, there isn't much you can do.
October 10, 2012 at 6:34 am
Opc.Three and Steve are dead on. I would take a step back and take an inventory of who has SA rights, who has local admin rights on the box, etc. I posted an Instance Security Audit script previously on this site, URL below. I use it all the time to get a handle on who has what rights in SQL Server, even does object permissions for each database. You will have to examine the local box Windows permissions separately. Doing these should at least give you a starting point, and possibly some arguments to restrict some accessibility that some users have that maybe don't need it. Just because it is Express Edition doesn't mean that security should be open.
Restricting box access and being very stringent on the database rights (access to only the tables they need, etc.) are good starting places. Sure beats doing nothing.
URL for Instance Security Audit documentation:
http://www.sqlservercentral.com/Forums/Topic1251262-146-1.aspx?Update=1
Hope this helps.
October 10, 2012 at 4:17 pm
Thank you all for the excellent replies. I'm still trying to work out our security issues. I set up the SQL server with Windows authentication. I set it up so that my domain account would be SA. It looks like anyone who has access to the domain (not just local users) are able to access the database using SQL Management Studio. What really has my head spinning is that if I remove the drive on which our database is installed from my domain computer and attach it via USB enclosure to my laptop which is OFF the domain and also running SQL Server 2005 Express, I can attach the database to my SQL server without requiring ANY authentication. Is it because authentication pertains to SQL Management Studio and not the database themselves? Thanks again for your time, I really appreciate it.:crazy:
October 10, 2012 at 7:41 pm
steveo1967 (10/10/2012)
Thank you all for the excellent replies. I'm still trying to work out our security issues. I set up the SQL server with Windows authentication. I set it up so that my domain account would be SA. It looks like anyone who has access to the domain (not just local users) are able to access the database using SQL Management Studio. What really has my head spinning is that if I remove the drive on which our database is installed from my domain computer and attach it via USB enclosure to my laptop which is OFF the domain and also running SQL Server 2005 Express, I can attach the database to my SQL server without requiring ANY authentication. Is it because authentication pertains to SQL Management Studio and not the database themselves? Thanks again for your time, I really appreciate it.:crazy:
Authentication occurs at the instance-level. Authorization occurs at the database-level. This is why you can take a database's data and log files from one instance, attach them to another instance and members of the sysadmin Role on the new instance will have full control over the database.
You can protect data and log files at rest using TDE (transparent data encryption) but that is only available in Enterprise Edition 2008 and above.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 26, 2012 at 12:59 pm
In-lieu of having TDE available you can use file-level encryption like EFS to encrypt the folders/files where SQL has the data/log/backup files.
As for your access issue in Windows on your domain, SQL Server 2005 and earlier kept the built-in/administrators group in the sysadmins server role. Ensure the authorized personnel who need sysadmin rights have logins set on your instance and are a part of the sysadmins server role. Once that is done you can remove the built-in/administrators role. That will take care of any non-authorized domain/server admins from accessing your instance, unless they bring it up in single-user mode.
Joie Andrew
"Since 1982"
November 2, 2012 at 9:16 am
To follow on with Joie's note, here's an article: http://www.sqlservercentral.com/articles/Administration/implementing_efs/870/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply