SQL 2000 security

Interesting comment from Frans:


Funny. The one source for sql server related security problems is Microsoft itself. The reason for that is that is that it is impossible to impersonate f.e an asp.net website into a native windows domain user which has role based security in a sqlserver. You simply can't do that. You have to maintain credentials on multiple machines. Most people therefor do not use trusted connections but sqlserver connections using sqlserver accounts for situations like this (which are very common), which force you to store the credentials in the connection string.

MS clearly made a terrible mistake when they designed the asp.net user security and multi-machine websites. Ah well... thankfully sqlserver 2000 at the moment still allows you to specify a sqlserver account (but I heared support for this will be dropped in a later release)...

Also, MS has made it very hard (if not impossible) to communicate encrypted with your sqlserver machine. You have to jump through hoops to get it working and even then... (using ssl). In the unix world, they have ssh and can tunnel any connection through that secure connection protocol. Very clever and neat. How else did MS envision the secure, remote maintainance of webservers and sqlservers in a DMZ which are not part of ANY domain?

 

3 Comments

  • You can - it's just usually turned off. In the AD for Windows 2k it's called trusted for delegation. In 2k3 they have a more controllable method called constrained trusted for delegation.

  • You can enable impersonation within ASP.NET by simply changing the <identity> section of either your web.config or machine.config configuration file.





    In general, though, impersonation is a bad idea and can lead to a variety of other security and performance issues (which is why we turned it off specifically with asp.net). Specifically, it can cause the database to "leak" access to data to end-users on middle tier applications (ie: because a user has access to the data, they can bypass the webserver middle tier logic and access the sprocs/tables directly -- something that a lot of apps don't protect against).





    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.





    Hope this helps,





    - Scott














  • "You can - it's just usually turned off. In the AD for Windows 2k it's called trusted for delegation. In 2k3 they have a more controllable method called constrained trusted for delegation."


    No you can't. THe IIS box is running as a standalone server, as MS advices everyone. ASPNET is a locally defined user and you can't send those credentials over the wire to use it on another box in the DMZ. _that_s the problem. If everything runs in one domain, it's not a problem, but who will run the webserver in a domain? No-one.

Comments have been disabled for this content.