A question was posed to our user group's listserv yesterday. After typing up a response I saw that I had written a short novel on the subject, and thought it may be of use to those outside our group. First the question posed:
> My employer doesn't want to expose it's production databases to the world, so they sit safely behind a firewall. The production web servers want to talk to those databases, so we open the right ports to let them talk through matching, local machine user accounts. This way if the production environment accounts were compromised, domain privileges don't exist for these users, so the individual account cannot do anything with the internal domain (i.e. can't bring down the whole corporate network). Brilliant! Except...
> Problem: This solution requires us to replicate user accounts across every machine and we're getting more and more of them as we grow. It's a burden and it's also a QoS (Quality of Service) and security risk in its own right, because access is subject to account synchronization. What if password policy (like expiration) isn't enforced on an account? What if one machine in the web farm isn't synch'ed and we suddenly lose connectivity?
> There are controls that help mitigate risk with the current solution, but I have to believe there is a better way. Can someone explain alternatives to me in non-network-geek English?
The environment you describe is pretty common in the corporate world, with the majority of ports, including 135 / 137 / 139 for NetBIOS, 53 for DNS, and 88 / 464 / 500 / 3268 / 3269 for Kerberos, blocked by a firewall. Corporate policy usually restricts you from joining any externally-facing web servers to an internal domain. So basically you are the one that ends up doing the work that a domain controller would otherwise be doing, synchronizing everything, all in the name of better security.
Speaking of security, let's consider it to be kind-of like a war, us against hackers. Our web servers are at the very front lines, the "tip of the spear", and are the ones most likely to be compromised. If a web server falls into enemy hands, the security info it knows about can be obtained through "interrogation" by the enemy. So if the web server was part of a domain, a hacker could search that domain for resources. This is risky, as many computer names and user names would then be exposed. The same kind of information you see when you go to add NTFS permissions to a file or folder, and click "Advanced", then "Find Now". You can then see a list of users, groups, and computers. It's too big a risk to take for most organizations. Comparing it to war, sensitive intelligence is kept from those on the front lines, thereby limiting your exposure if someone gets captured. So you end up doing all those "need to know" security updates manually to the machines on the front lines.
So how can we simplify administration for security between web servers and SQL servers? There's a couple of options I can think of.
Option 1 -- Add a domain
Some corporate policies will let you set up a separate domain just for your SQL and web servers, so all those common "need to know" security details for the machines on your front lines can be easily set up. There's little risk of sensitive data from the internal domain making its way to the outside because there's a real separation there. No trust relationships between the inside and outside domains. Having a separate domain greatly simplifies setting up web farms. It's also essential to have your SQL server nodes joined to a domain when configuring database clusters. If the web servers and SQL servers are on the same domain or trusted domains then it's much simpler to use SQL server in "Windows Authentication" mode. So let's explore this option a little further. Here's a diagram:
Internal corporate network
Your Vista / XP computer is sitting on a desk in here somewhere
| Firewall allowing SQL requests only one direction
| (OK, maybe also terminal services connections
| in order to simplify administration via port 3389.)
* Domain Controllers for web environment (at least 2)
* SQL server(s)
| Firewall allowing SQL, Kerberos, and NetBIOS requests
| (ports 53 / 88 / 135 / 137 / 139 / 464 / 500 / 3268 / 3269,
| plus whatever custom port you use for SQL.)
* Web servers joined to the "web" domain. Each machine is "multi-homed" (has 2 network adapters), with one connection to the network above, and one connected below.
| Firewall allowing HTTP and HTTPS
In this case note that the domain controllers and SQL server(s) would use an IP addressing scheme that does not route on the Internet, like 10.x.x.x, or 192.168.x.x. Also each web server would have an IP on this network.
If a web server were compromised then there would still be risk of an attacker discovering computer names and IP addresses of other web servers and database servers. Also account names used on that separate "web" network. But no risk that they would discover that same information on the corporate network. You may want to explore this option to simplify your day-to-day administration. Adding a new web server to the environment is as simple as having it join the domain, and then establishing file replication service (FRS), ROBOCOPY scripts, or something similar to synchronize files and permissions.
If data in the SQL servers has to be accessible on the corporate network then you could set up a firewall allowing just requests from the corporate network to be answered by the database servers, and then only on one port. (Often better for this not to be the default of 1433!) As long as you use a good firewall then it's pretty strong security, and very low risk of the corporate network getting exposed. No domain-specific information would traverse the wire.
In addition to the 3 firewalls shown above, you can also establish IPSec for even better security. This would be especially useful between the corporate environment and the segment with SQL / domain controllers. Also somewhat useful between SQL and web servers because a compromised web server could potentially sniff network traffic going between other web servers and the database server, exposing sensitive data. But bear in mind that you take a performance hit when using IPSec, so enabling this between web servers and database servers would slow down requests.
Option 2 -- Enable "Mixed" mode on your SQL server(s)
This one is quick and dirty. It avoids the need to configure special Windows accounts at all, and has you change out your connection strings to use SQL logins. It's functionally similar to what we saw with Option 1 in that security for the externally-facing resources is maintained separately from any internal credentials. In Option 1 it was done in a totally separate domain. In this option it's done by simply leveraging the security system baked into SQL server. But then the security credentials have to be exposed in the connection string. This doesn't protect the data in your SQL server nearly as well since a compromised web server could then give the attacker access to passwords used by the SQL service, potentially giving more access than in a scenario where the SQL server uses only Windows authentication. Because of this many corporate security policies do not allow SQL server to be configured in mixed mode. At least this option is simple, not requiring any additional hardware in your scenario.
With this option the SQL server(s) are still on the corporate network, so a major consideration is to harden the machines as much as possible. Disable risky extended stored procedures. This is more than just xp_cmdshell. What about xp_runweb, xp_regread, and xp_regwrite? Not to mention xp_dirtree and xp_enumgroups. Here's a fairly good checklist of security stuff for SQL server:
A sideline note -- especially important now that Windows Server 2008 has gone gold -- IIS 7 can simplify permissions when deploying websites. Instead of setting up NTFS permissions and having to copy them between web servers, you can use URL Authorization to configure the security right in the web.config. This way all you need to do is copy the files onto the web server and you're done. No more messing with NTFS permissions.
Hopefully this info will help you to find an easier way to administer your web environment!