Serious SqlServer security problem: Microsoft's state of denial

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.

The Problem
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:

  1. 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.
  2. 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 'hacks' workarounds?
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.

9 Comments

  • Totally agree Frans, but you can also add the common scenario of hosting (like myself) where you have a firewall between the live server and the databse box.





    I setup the two by letting access to nothing except the port to talk to SQL.





    So the two cases you talked about are absolutly not working at all in this scenario.





    Maybe the light will come from Yukon where apparently SQL will be closer to the Framework model.


    BTW, nice stylesheet on your page ;-))

  • Oh I forgot also a case I know well where for some specific reasons too long to explain, the live server can connect either to a SQL box or a MySQL box(on Unix).





    Using some DAL to switch easily from one to another, how MS think about trusted connections with an ASP Net username.





    No way ;-)

  • The firewall solution is indeed a possible 'fix', but firewalls are not that cheap, plus you already run the box inside a DMZ of a firewall. :) What I really would like to see is the ability to use application trusted connections as they are defined in ADO/Sqlserver. You can then define the trusted connection with an application and be done with it, which is in fact what you want. At the moment it's a chewing-gum and iron wire solution: it will hold but for how long? :)





    about the css: thanks :))

  • I have a comment, storing connection string in the compiled form it is "secure" as storing it in text file. The reason is simple if you run a dissembler available with .net framework on this assembly, connection string becomes fully visible. The only solution is encryption.

  • It's possible to store connection strings in the registry, and apply decent DACLs to that. I believe you could also use a local LSA secret if you really wanted to. Writing Secure Code by Michael Howard and David LeBlank has some good info on this. Of course if the web server is 0wn3d that still leaves your SQL box vulnerable, but it certainly raises the bar.

  • All the tricks of hiding / protecting the connection string are nice and clever, but they all show how bad the mechanism is. No-one thinks its ok to store your windows credentials in a string somewhere so you can log on; these credentials are stored in a userdatabase and you are granted access. What the text based connection string shows us is that the mechanism is flawed: it should be possible to define which application or which user has access to given objects inside a database, without having to specify a textbased string somewhere with a password.





    The integrated security option is in theory a good replacement, in practise it needs some work though.





    Btw, Joseph: the idea is neat, but the ASPNET user f.e. is not able to read from the registry... therefor you have to give up some security settings which are applied for you when the .NET runtime is installed by giving ASPNET access to the registry.

  • I think discussion missses two important facts:


    1. Problem with integrated security is the way challenge/response works, i.e. passing challenge from web server to SQL which is not a domain controller does not work. However, there is a way to pass credentials securily (including *nix boxes, mind you). It's called Kerberos and 2000/2003 servers do support it.


    2. In large scale implementation where people are concerned with SQL administrator having access to web server and vice versa, I think the solution would be to move your data access to a separate box and use .NET remoting.


    Just my 2c


    Georged

  • What you're talking about is something like Passport, where the system (or SQL database) sends you off to a service to authenticate, and you either come back with a valid key to enter, or not.





    So, you could develop your own "Give Me My Connection String" web service that handles connection strings per app (if that's the way you do it). You use HTTPS and your app's authentication credentials (which can be compiled as part of the code - they shouldn't need to be changed). Then, get back your connection string.





    I agree that this is not integrated and what you're thinking about (because it introduces a 3rd party), but it eliminates the need to store connection strings as strings anywhere on the web server.

  • Isn't this where serviced components come in handy? Only the credentials of the COM+ application should be able to connect to the database.





    The ASP.NET web application can run with less permissions on the system then the COM+ application.

Comments have been disabled for this content.