REPEATABLE READ

It's so easy to hose yourself with SQL Server. Especially when it comes to security. Right now I'm playing around with some Double-Hop Delegation issues on some of my production Servers. It's tricky getting it to all to work CORRECTLY; and correctly means: getting it to work adhering to the principle of least privilege. My current endeavors made me reflect back upon a scenario that I once encountered, where two bad moves combined to create a complete security nightmare. (All details have been 'scrubbed' to protect the innocent.)

A long time ago, in a galaxy far, far away... Consultants were brought into an enterprise, to help set up some Clustered SQL Servers. Three cluster groups were created at various locations around the country, and then each server was linked to the other via SQL Servers Linked Servers infrastructure. Everything worked fine-ish, for months/years.

Then I came along and inherited the mess. I wasn't a domain admin on the production network, but had long suspected something was a bit fishy with the way these servers were set up. So, one day, I got with the Domain Admin and we began looking at the privileges, permissions, rights-assignments given to the service accounts that the SQL Servers were operating as. To our horror, we found that they were members of the Domain Admins group. Now, I have seen companies go this route to allow Linked Servers to more easily do Double-Hop Delegation (i.e. to allow self-mapping of security credentials between linked SQL Servers.) It's definitely NOT a best practice, but it can provide a quicker/easier deployment of Linked Server functionality. Only, it was painfully obvious that the consultants who set these servers up hadn't granted Domain Admin perms to facilitate Linked Server self-mapping. The Domain Admin (who also inherited this mess) and I came to the conclusion that the consultants may have gone this route to get the clustering to work (which can also be tricky, but still definitely doesn't require Domain Admin rights).

Now, all of that babbling above indicates that the situation was bad-ish. But not quite horrible (well, these servers were also accessed by code that was totally susceptible to SQL Injection, but that's another story). The horror came when I figured out how the consultants had managed to get Delegation to work between the linked servers. Despite the fact that the SQL Server Service accounts were set to run as Domain Admins, there was some other issue with Kerberos/AD... which was preventing these servers from passing SSPI contexts back and forth. How did these geniuses solve that? Simple, they created accounts on all of the clustered servers for NT AUTHORITY\ANONYMOUS LOGIN; then they made these accounts sysadmin.

What does all of this mean? Well... the problem that the consultants tried to fix was a rather complex one. For example, each time user domainName\userName logged into SQL Server A (Kerberos would authenticate them, and then 'map' them to their permission set), everything would work fine. But if user domainName\userName tried to do a distributed query to SQL Server B via a Linked Server (from server A) they weren't able to create an SSPI context correctly... so they'd get an error telling them that a connection couldn't be established for a user with a 'null' session. So the consultants created a LOGON for 'NULL Session', and mapped it to the sysadmin Server Role, so that everyone who hit the server via a Linked Server would have any permissions they needed.

This is a CLASSIC example of what happens when you let people into the kitchen who can't cook -- much less shouldn't be allowed to handle sharp or blunt instruments. Why? Simple, they'd created a NIGHTMARE security scenario. Anyone with access to the physical network could just bring in a laptop, plug it into the network and then open up Query Analyzer, and connect, using Windows Authentication, to any of these SQL Servers. Because these geniuses had mapped Null Session Windows users as sysadmin, not only would ANYONE be able to connect by just plugging into the network (and no, they wouldn't have to even be a member of the domain... a NULL Session is a NULL Session is a NULL Session), but once they got on to the SQL Server, they'd be on it as sysadmin. Then, couple that with the fact that the SQL Server was running as a Domain Admin...and you can see why this was the security scenario from hell; the consultants had created an easily exploitable scenario where anyone could immediately become a Domain Admin on the production network just by connecting to SQL Server with NT Credentials that couldn't be validated on the domain. (A little bit of xp_cmdshell from within Query Analyzer would grant 'hackers' a dos-prompt from within SQL Server as Domain Admins.)