SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server 2005 Express


SQL Server 2005 Express

Author
Message
steveo1967
steveo1967
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15527 Visits: 14396
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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: Administrators
Points: 65721 Visits: 19118
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
My Blog: www.voiceofthedba.com
vikingDBA
vikingDBA
SSC-Addicted
SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)

Group: General Forum Members
Points: 444 Visits: 929
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.
steveo1967
steveo1967
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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.Crazy
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15527 Visits: 14396
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
Joie Andrew
Joie Andrew
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2419 Visits: 2032
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"
Steve Jones
Steve Jones
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: Administrators
Points: 65721 Visits: 19118
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
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search