Securing My Database

[TamedTech]

Hello Guys,

I'm after some advice on securing my SQL Server 2005 databases a little bit and thought speaking with you would be a good place to start. I'm running SQL2005 Standard on a Windows 2003 Standard box, and the only connections the DB really needs to recieve are from my ColdFusion applications, which again are hosted on the same box.

Firsly, what type of security is best for the server? I'm running with SQL Server Authentication at the moment oposed to Windows Authentication, is that the right way to have it set up? Generaly for working on the sever i'll log in through the sa account, but I'd imagine this is also not a wise move, I like to hear your thoughts on how that should be changed.

I also need to get some user accounts setup for my ColdFusion server to be able to make connections to the different databases, but these users need to have the real minimum of rights, they will need to run stored proceedures which do all the work, the stored procs esentialy contain all the basic CRUD methods and a couple have BULK INSERTS.

The users will not need any permissions above that, they dont create or drop tables, they dont add users or anything like that, just simple read and writes.

How do I best set those up on the server?

Another thing to note is that I keep getting heaps of logs in my windows event viewer of failed login attempts to my SQL Server, I mean, we're talking 5 or 6 a second in peak times, So i'd really like to cut it off from the outside world if thats possible? I dont have a dedicated firewall so whatever solution we chose for that it'll have to be somthing softwarish! I'm sure I remember reading that you can disable TCP-IP ports for the server.

Thanks guys for any advice, Keep in mind I'm a bit of a noob with db admin, so be gentle.

Rob

 

[ptheriault]

1. Delete Built-in/administrator. It will allow any server admin to login as 'sa' on the server. If you don't want to delete that account at least remove the sysadmin role.

2. Create you CF login and add to you CF databases. I would just make it a member of data reader and data writter for each CF database. Also something I have learned with CF. Create a ClientData db for each instance of CF. This will reduce the ammount of blocking and increase performance.

3. grant exec permissions to your procs.

4. Make sure you have disable port 1433 in you Firewall to the outside.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

Never log into the server with the sa account. Set the sa password to a VERY strong password (20+ characters) and change it every once in a while.

You should be logging in yourself with Windows Authentication.

If your server is available on the internet (which it sounds like it is) see if your routers support ACLs. If they do at the minimum block access to port 1433 TCP and 1434 UDP from the internet to your SQL Server. A beter option would be to block all inbound traffic except the web traffic to your web servers and mail access to your mail servers.

If you can't add an ACL, tell SQL Server to not accept remote connections. This will stop people from being able to try to log into the SQL Server. However it will not stop them from trying to break into the SQL Server's OS.

Create an application account for CF. Give this account only the rights that it needs. If everything is being done through stored procedures with no dynamic SQL then you are set. Just create a role called CF_ExecProcs (or something like that) and add the CF account to this role. Grant the Role rights to execute the needed procedures.

Grant your self (and any other DBAs) sysadmin rights manually, then remove the BUILTIN\Administrators group from the SQL Server.

It's better to use Windows Auth instead of mixed mode, that said almost no one does that.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[TamedTech]

Thank you both for your insights into that one, someone else has just been giving the scares by telling me the horror story of 'the little man who kept his sql and his webserver on the same box' lol

I'll make sure to change all those user setting and get myself runing on windows authentication instead of this mixed mode stuff, rest assured that my servers are all running long complex passwords (which somehow I actualy seem to remember without even thinking twice about them haha).

Thanks guys,

I'll let you know once i'm done, see if there are any other things we can do.

Rob