ASP.net and SQL2005: ASPNET account setting

Question

I'm a bit confuse about the ASPNET account in SQL2005. In my web site connection string, i use sa account to connect to the sql database. When i use firefox to login to my website, i got a error "Login failed for user: 'AccountName'". i have found the answers on ms support site. It mentioned 3 methods to correct this method and one of them is grant the correct permission in the sql server for ASPNET.

I selected public for Server role, permission to connect to the database is granted and login enabled and i also selected the database in the user mapping. I still get the permission error on my database store proc.

what am i missing?

edit: i set ASPNET as db owner in my database and everything work. if i just set read and write, it doesn't work. isn't setting ASPNET account to db owner a security risk?

Answer 1

Firstly do not ever connect as SA from a web application, that's a huge security risk.

It sounds like your connection string is messed up, that it's using NT authentication rather than the specified user/password combination.

You shouldn't need to set the application pool account to be dbo unless you're directly accessing tables; rather add it to the public group for that database; that way you can limit it properly.