Obviously the way we are told by Microsoft to login to a database is to pur our connection string in the web.config file and to connect to the database as that user every time. Frans supports this just fine, but I'm wondering if anyone logs on to the database with each specific user. Now, I totally understand that in a web environment, you'll have people that are anonymous users. But, in my specific scenario, I know each person that will be logging in to the system because only pre-authorized people will be logging in. Therefore, I could use specific credentials to connect to the database, which is how I currently do things in my thick client app. Are there any pitfalls to this and or any reasons why I shouldn't do things this way? From the way I see it initially, I know then who is on the system by doing a sp_who on the back end, I can keep all my "update_user" and "insert_user" column triggers in place, and I can keep the same permissions that I already have set up. Seems like a win win to me.

NickD wrote:

Obviously the way we are told by Microsoft to login to a database is to pur our connection string in the web.config file and to connect to the database as that user every time. Frans supports this just fine, but I'm wondering if anyone logs on to the database with each specific user. Now, I totally understand that in a web environment, you'll have people that are anonymous users. But, in my specific scenario, I know each person that will be logging in to the system because only pre-authorized people will be logging in. Therefore, I could use specific credentials to connect to the database, which is how I currently do things in my thick client app. Are there any pitfalls to this and or any reasons why I shouldn't do things this way? From the way I see it initially, I know then who is on the system by doing a sp_who on the back end, I can keep all my "update_user" and "insert_user" column triggers in place, and I can keep the same permissions that I already have set up. Seems like a win win to me.

Here's my two cents:

I've always developed my applications with a single SQL Server User account and passed user cradentials to the DB as necessary. This allows for connection pooling and a much simpler security model as you only grant database access to a single account.

Using Integrated Security IIS will pass through user cradentials to your application. You won't need to do an sp_who as you'll already know who is who.

I'm sure you'll get additional opinions.

NickD wrote:

Obviously the way we are told by Microsoft to login to a database is to pur our connection string in the web.config file and to connect to the database as that user every time. Frans supports this just fine, but I'm wondering if anyone logs on to the database with each specific user. Now, I totally understand that in a web environment, you'll have people that are anonymous users. But, in my specific scenario, I know each person that will be logging in to the system because only pre-authorized people will be logging in. Therefore, I could use specific credentials to connect to the database, which is how I currently do things in my thick client app. Are there any pitfalls to this and or any reasons why I shouldn't do things this way? From the way I see it initially, I know then who is on the system by doing a sp_who on the back end, I can keep all my "update_user" and "insert_user" column triggers in place, and I can keep the same permissions that I already have set up. Seems like a win win to me.

I assume you mean using NT Authentication in your connection string and using Windows Authentication in your web app.

I think this is perfectly acceptable way to do security for an Intranet app where all the users already have windows domain accounts.

The main downside I see to this is that you users have access to the SQL Server data directly and could connect to the server and query data. Of course, this is why so many people advocate data access only via SP's cause the SP can control what data the user is able to access cause querys are controled at the SQL level using SQL security.

However, there are some ways to pass the username to the server that your triggers can use and still only grant access to the ASP.net worker process rather than using impersonation.

BOb

Paul.Lewis wrote:

I've always developed my applications with a single SQL Server User account and passed user cradentials to the DB as necessary. This allows for connection pooling and a much simpler security model as you only grant database access to a single account.

This would be the pro with the most weight I think, but it's a matter of learning a new way. I'm a thick app guy coming to this strange and bizare world of "state" where variables go to die. So, for me, it is normal and comfortable to let people log in with their Active Directory credentials and not look back. But, then like you say, connection pooling is not possible at that point.

The computer gods giveth and taketh.

NickD wrote:

Paul.Lewis wrote:

I've always developed my applications with a single SQL Server User account and passed user cradentials to the DB as necessary. This allows for connection pooling and a much simpler security model as you only grant database access to a single account.

This would be the pro with the most weight I think, but it's a matter of learning a new way. I'm a thick app guy coming to this strange and bizare world of "state" where variables go to die. So, for me, it is normal and comfortable to let people log in with their Active Directory credentials and not look back. But, then like you say, connection pooling is not possible at that point.

The computer gods giveth and taketh.

You can still use Windows Authentication with a single sql login account.

BOb

pilotboba wrote:

You can still use Windows Authentication with a single sql login account.

Please enlighten me

NickD wrote:

pilotboba wrote:

You can still use Windows Authentication with a single sql login account.

Please enlighten me

1. Set ASP.Net to use Windows authentication.
2. Set IIS to use Windows authentication for your apps virtual directory (turn off anonymous access).
3. Do not use impersonation.

Once the above is done only users with valid windows domain accounts will be authenticated and passed to your ASP.Net application.

The threads CurrentPrinciple will contain an WindowsPrinciple object.

You application will still run under the ASPNET account (Network Service for Win XP/2003).

The above is seperated from your db connection. Use a connection string that specifies a SQL server login, or better yet use NT Authentication in your connection string. If you do this all db access will occur under the ASPNET/Network Service domain account.

Here is a pretty good basic article that goes into more detail:

http://aspnet.4guysfromrolla.com/articles/031204-1.aspx

BOb

Thanks! I'll take a look.