Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server 2005 Express Expand / Collapse
Author
Message
Posted Thursday, October 04, 2012 4:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 11, 2012 10:31 AM
Points: 2, Visits: 6
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
Post #1368763
Posted Friday, October 05, 2012 5:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:17 AM
Points: 7,070, Visits: 12,522
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1368945
Posted Tuesday, October 09, 2012 11:12 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:20 PM
Points: 32,764, Visits: 14,928
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1370498
Posted Wednesday, October 10, 2012 6:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 03, 2014 12:15 PM
Points: 189, Visits: 875
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.
Post #1370888
Posted Wednesday, October 10, 2012 4:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 11, 2012 10:31 AM
Points: 2, Visits: 6
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.
Post #1371155
Posted Wednesday, October 10, 2012 7:41 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:17 AM
Points: 7,070, Visits: 12,522
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.

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1371174
Posted Friday, October 26, 2012 12:59 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 5:29 AM
Points: 676, Visits: 1,333
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"
Post #1377796
Posted Friday, November 02, 2012 9:16 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:20 PM
Points: 32,764, Visits: 14,928
To follow on with Joie's note, here's an article: http://www.sqlservercentral.com/articles/Administration/implementing_efs/870/






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1380440
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse