November 2, 2010 at 8:26 am
Traditionally, we have been an internally faced IT shop (as far as DBs). We are starting to build applications that will be opened up to the public internet and have some definite security concerns. My overall question is what are some key items to do/consider when building an externally facing database server, both from the install (port/files, etc.) and the security within SQL?
Thanks!
November 3, 2010 at 7:27 am
i cannot imagine any situation where you have to have an externally facing DB.
what is the reason why your company is doing this?
November 3, 2010 at 7:37 am
We have a few applications that will now allow external clients access to update their information.
November 3, 2010 at 7:41 am
as the DBA supporting these databases, here is what I would do...
I would tell management that what they are doing is a HUGE mistake and introduce them to a VPN.
November 3, 2010 at 7:42 am
I would create new roles, and double check what rights are granted them. Using db_reader/writer is a way to unintentionally allow access to tables.
Make sure that the instances are secured, and the Best Practices Analyzer can help.
Make sure passwords are strong.
November 3, 2010 at 7:48 am
Steve Jones - SSC Editor (11/3/2010)
I would create new roles, and double check what rights are granted them. Using db_reader/writer is a way to unintentionally allow access to tables.Make sure that the instances are secured, and the Best Practices Analyzer can help.
Make sure passwords are strong.
you will have to be heavily involved with your AD guys also. I would not run the DB server in mixed mode, so the AD guys would have to set up rules that make those users change their password often, that they are very long and complex.
I would also run SQL on a different port.
but, i would kick and scream about doing it and ask for a VPN solution instead.....
November 3, 2010 at 7:57 am
rather than opening your DB up to the net, why don't you develop with a three-tier architecture which would incorporate a middle tier that sits between the db and the app.
You could create this middle-tier as a webservice and keep the db off the net. This should be more secure and would be a lot more scalable.
November 4, 2010 at 2:28 am
Geoff A (11/3/2010)
Steve Jones - SSC Editor (11/3/2010)
I would create new roles, and double check what rights are granted them. Using db_reader/writer is a way to unintentionally allow access to tables.Make sure that the instances are secured, and the Best Practices Analyzer can help.
Make sure passwords are strong.
you will have to be heavily involved with your AD guys also. I would not run the DB server in mixed mode, so the AD guys would have to set up rules that make those users change their password often, that they are very long and complex.
I would also run SQL on a different port.
but, i would kick and scream about doing it and ask for a VPN solution instead.....
If they are external clients, unless the AD domain controllers and DNS servers are also exposed (this is bad, bad, bad, worse than exposing the SQL Server bad), there is no Windows authentication happening here. So you'd need to run mixed mode.
K. Brian Kelley
@kbriankelley
November 4, 2010 at 2:36 am
Yeah, it's not fun, but sometimes business requirements make you do this, right? I've worked with a vendor to expose a SQL Server some of our business folks use for an externally hosted application. Here are the things to consider doing...
1) Harden the server as best as you can using typical best practices. Among them ensure that you use IPSEC policy to restrict access from the external IPs to only the SQL Server related protocols and ports (TCP and the listening port you choose and UDP/1434 if you're running a named instance).
2) Choose non-standard ports. If you've got a default instance, make it something other than TCP/1433. True story, when we wanted to pick a port we broke out percentile dice (10-sided dice) and treated the 0 as a 0. That way it wasn't a predisposed geek number or something guessable. We went this path after our security analyst went, "Use port 1701!" (Star Trek reference to U.S.S. Enterprise)
3) Purchase and install a 3rd party SSL certificate from one of the known CAs (such as VeriSign). Toggle on the server side to use encryption. This will force clients to use encryption, meaning the data won't be sniffed over the wire.
4) Put a hardware firewall in front of the SQL Server and ensure that it only allows external IPs to hit the appropriate SQL Server ports. I know I told you to do this using IPSEC on the server in (1), but we're practicing Defense in Depth.
5) Check the accounts the external clients will be using and ensure they have the least privileges possible. No sysadmin level accounts and preferably no dbo or db_owner accounts, either.
6) If you're running on SQL Server 2005 or higher, enforce that your clients install the appropriate SQL Native Client drivers on their systems. When you set up the accounts for your clients to use, ensure the password policy enforcement rules are on. It'll pull from your domain, so that may mean if a login name is guessed, you could see an account lock out. But hopefully you've got IDS in place to alert you to a bunch of invalid logins so you can block the IP, right?
7) Ensure the SQL Server is patched quickly after patches come out. Even if this breaks from your normal cycle. Anything exposed to the perimeter is at higher risk.
I think that's it. If I remember anything else, I'll add on later.
K. Brian Kelley
@kbriankelley
November 4, 2010 at 6:46 am
Thanks, these are some really great pointers...and yes, we have internal IT folks saying "NO!", but business requirements are going to trump that so we just want to supply the best experience in the mose secure fashion. Keep them coming!!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply