SQL 2000 security and impersonation

From Scott Guthrie


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

Thanks Scott, but my 2 cents on windows authentification. In the scenario of a Live server connected through a firewall to a SQL box, and if all the potentially unsecured services like the Netbios are disabled, is the windows authentication is still an option ?


 

1 Comment

  • &quot;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&quot;


    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.





    -&gt; 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.

Comments have been disabled for this content.