Several blogs have been posted lately, especially by Paschal, about SqlServer security and .NET. People on the DOTNET-CLR mailing list are already familiar with the problem, but looking at the reactions on Paschal's blogs I can only conclude that the rest of the world, including Microsoft, is in a severe state of denial.
"Close your eyes, deny that there is a problem and you'll see... there is no problem." I have the feeling that's the thought that pops into many heads when they're confronted with the problem about security related to SqlServer and .NET. I can tell you: even if you deny it, the problem will be there, and will stay there, until someone fixes it.
Consider you are building a web-application and it will span two machines: a webserver with IIS and your ASP.NET GUI tier and Business logic tier (machine A) and a database server with SqlServer 2000 (machine B). Both machines are used solely for the same application and are running in a DMZ (demilitarized zone) of the corporate firewall. It's obvious that A will connect to B to read or write data. To connect with SqlServer and to be able to do something, you need permissions. There are several possibilities to do this. Let's sum them up in a small list:
- Use SqlServer security. This is the easiest way to do it and a lot of people use this. You simply define an account in SqlServer (on machine B) and use that account to connect with SqlServer by specifying that account plus its password in the connection string that is used by A to connect to B.
- Use Integrated security. This is the way Microsoft wants you to use: you use the Windows account you're currently logged in with (or better: the security context the application runs in which connects to SqlServer) to connect to SqlServer. You therefor have to map the Windows account on a SqlServer account. You do not have to specify account credentials in the connection string, the connection is 'trusted'.
Both have problems. Let's begin with option 1, the SqlServer security way. Because you have to store credentials in the connection string, you can't hard-code this connection string in the application, otherwise you have to recompile the application when the password changes (some organizations require this, f.e. each month). You can store this connection string in f.e. the web.config file or the .config file of the executable. The problem is that it is readable for a human and mutable. You can crypt it but this degrades performance in some way plus it requires you to have an encrypt utility when changing passwords and you have to update the connection string. There is also a secure local storage called Isolated Storage. You then also have to use a utility to update the connection string when you store the file which contains the string in that isolated storage. In short: it works but there are a lot of disadvantages.
Option 2 doesn't suffer from this problem, you simply specify the account with windows and be done with it. Perfect, right? Well... no. Going back to our 2 machines A and B with our ASP.NET application we can't use this option. The problem is related to "Security on Windows for dummies", Chapter 1, line 1: do not run your internet site on a machine that is part of a domain. You see, the problem is that when you run the ASP.NET application on machine A, it runs under the "My Hands Are Tied"-user ASPNET. That account is a local account. Because every person who understands security and has read Microsoft's excellent papers on the Technet website, will not run the machines in a domain, thus ASPNET on machine A is not known on machine B. The ASP.NET application can't connect to B using a trusted connection simply because ASPNET account isn't defined on B and you then can't make a mapping on B for the ASPNET user.
If you read the SqlServer 2000 security papers, it's clear Microsoft pushes trusted connections and integrated (windows) security over SqlServer security. And understandable. However, in this case (and trust me, this is a very common scenario), with two or more machines which run a complete web-application and control will flow from one machine to the other, initiating from the IIS machine / ASP.NET application, SqlServer security is the only security that actually works. How's that possible? How could Microsoft ignore this issue which is already with us since SqlServer sports windows integrated security? I don't see why Microsoft hasn't fixed this a long time ago. Every person who has written a distributed webapplication with SqlServer knows that keeping the application secure even after a compromise of the front end (the webserver) knows that integrated security is a nice thing for the books but in practise not usable.
Aren't there any
Well, a few. Let's get a list of these shabby workarounds:
- Use a domain. That's right. Simply ignore "Security: Best Practices 101" for now and define a domain for machine A and B, eventually with a separate domain controller (you can also use one of the two machines as a domain controller). This will solve the problem of the unknown user. You simply run the ASP.NET application under a given domain user and because it's a domain user, it is known on the SqlServer machine and can be mapped to a SqlServer account. From A you can use a trusted connection and everything works OK. The problem is... if the webserver is compromised, the SqlServer machine is also compromised, because the website runs as a domain user. That's why every Windows security document recommends to run the webserver not in a domain but as a separate server.
- Mirror credentials. Another funny 'solution' is to define the user who is used to run the ASP.NET application also on machine B with the same username and password. So, f.e. define a new password for the ASPNET user on A and define a new local user ASPNET on B with the same password. When the ASP.NET application wants to connect to B using a trusted connection, it can actually log in using the credentials of the security context it runs in on A. Think about this for a minute. We have a logged in user, ASPNET, on A, which has a security token SID and can get access to machine B using that SID or at least the credentials related to that SID. Why is this possible? Apparently Windows security doesn't work with SID's but still with usernames and passwords because a local SID from machine A would otherwise not be recognized on B, because it is not known there. Ok, besides that, it has other problems: the administrator who is in charge of the webserver now also knows security information related to the SqlServer, because the accounts have to be kept in sync. Not all organizations want this. It also makes the point of the locally defined ASPNET user which is not able to do anything rather moot because that locally defined account is able to log into another machine.
Well, what should be the solution then?
For starters, Microsoft should look at common situations where applications are forced to use other security settings than planned or recommended. When they had done this years ago, we wouldn't be in this situation right now. Then, when they got that started up, it should be possible for f.e. SqlServer to verify a given SID token received from another machine with that same machine. In our example, A's locally defined ASPNET user will send its SID over the wire to SqlServer on B when connecting to it using a trusted connection, and SqlServer will then verify with A if this SID is of any good and known by A. Then, inside SqlServer it should be possible to map accounts from A on SqlServer accounts so local defined users on A can have only access to a given SqlServer instance and only to the objects they have rights for defined inside SqlServer. This way, those locally defined users on A cannot and will not have any rights on B. They have only rights inside SqlServer, which is what's required, because an application requires data from that SqlServer instance.
Perhaps it sounds paranoia, but security is something you have to take seriously, very seriously. One compromise on a single box should never be able to escalate to other boxes as well. Perhaps some people now will say I'm wrong and Microsoft has it all figured out and there and there are articles to find how to set it up correctly etc. etc.: please consider for a moment that what I described above is not fantasy, it's reality, and till today, Microsoft hasn't come forward with a solid, simple solution which complies to strong security requirements. Integrated security, trusted connections, I'd like to use them if I could, because the concept is good. However they are not applicable due to reasons I have ventilated in this blog. I truly hope Microsoft will fix this in the (near) future or some brilliant mind has a solution that is secure, simple, reliable and not a shabby workaround.