SQL 2000 security

Comment from Frans:

"Rather than use SQL authentication when connecting from ASP.NET to SQL Server, you should use native windows authentication -- but lock down access not to the identity of the calling user, but rather to that of the ASP.NET worker process identity of the running application. That way you can restrict access to only allow the application access to the data (not the end users using it). You also do not have to worry about the SQL authentication usernames/passwords ever being compromised and access in these scenarios -- since no username/password is ever stored in an unencrypted way"
That's wrong, sorry. If I have 2 machines, one being the IIS server and the other one being the sqlserver machine, the ASP.NET account is LOCAL to the IIS server. I can't use that account to access the sqlserver instance and do things there. I then have to add the SAME user to the sqlserver machine as well with the SAME password, also as local account.

-> 2 times the same user and it 'works' because they have the same credentials, but they are different users.

On the DOTNET-CLR mailing list we had a lengthy discussion about this a month or so ago and we all concluded that it wasn't possible to do this how MS tells us to do it. On 1 box, it's no problem (sqlserver and IIS on one machine). There is however a problem with the contradiction between the advice from MS not to run your sqlserver/iis boxes in a domain / as a PDC/BDC, so you use local defined accounts on the IIS box, not domain users, and the fact that it is impossible to access box B from box A when you are logged in on A as a local user (ASPNET) of A which is not known on B.

 

2 Comments

  • Yes it is possible. There is a Microsoft knowledge base article on how to make the ASPNET_WP a domain account. If it is a domain account you can add that account as a user (or better to make the domain account a part of a local group, and only grant logins to local groups). Second, you can "mirror" the ASPNET_WP on the SQL Server. You would then be responsible for maintaining the password (no longer machine-generated) but 2 local accounts on different machines with the same login name and password will work.

  • Darrell: running your two boxes (the IIS machine and the SQLServer machine) in a domain is FATAL for security. If the website runs under a domain user, and it gets compromised, the attacker has access to the domain. Not good. What you want is the credentials to ONLY log into the sqlserver instance and ONLY into the databases specified for that account. The ideal situation would be that the sqlserver machine (B) would validate the local ASPNET account of the iis machine (A) with A.





    'mirroring' credentials is also a hack. Not only is it not maintainable (you have to give administrators access to both machines to add users, plus you have to keep the passwords in sync), it's also weird that it works in the first place. "Keep the uid and the password the same and it works", this SHOULDN'T be possible, because you are already logged in on A (the webserver) as a given user, and can use that login info on box B 'because' the credentials are the same, even if the local user ASPNET of A is local to A, and ANOTHER user is local to B.





    All in all, I can only conclude Microsoft has its head up its @ss when it comes to serious security configuration and is still in a state of denial. Why is it so darn hard for them to create a solution for such a simple problem? Any solution based on "Domain", "Mirroring credentials" or "Sqlserver login credentials" are not enough. It's 2003, however most 2-3 machine based webapplications can't live without OR compromising their security configuration (by using a domain or worse) OR using sqlserver credentials, something that is scheduled to be defined deprecated.





    It's shocking how many people simply do not understand the problem.

Comments have been disabled for this content.