Login Failed : user ASPNET (MSDE)

[asif m hmood]

i have MSSql Desktop engine installed and i m connecting to a database from ASP.net page. i m using oleDBconnection but when i Open() connection it gives me exception "Login failed 'MATRIX\ASPNET' " where Matrix is machin name. it is working well in Windows form but only givin problem in webforms , can any body tell wats wrong with it , i m using same connection string as for windows forms.

[Mike Dimmick]

You're using Integrated Security in your connection string; that is, Windows Authentication. In this mode the database uses the credentials of the thread to authenticate the connection. Your Windows Forms application is running under your own user account, which is probably a member of the machine's Administrators group. The machine's Administrators group is granted login rights by default, and is a member of the System Administrators server role by default. In effect, your Windows user account has unlimited control over the database server. By contrast, ASP.NET applications by default run under the ASPNET account on the local machine. This account is only a member of the Users group, which by default cannot log in to SQL Server. You can allow the MATRIX\ASPNET user to log in using the following command (use osql to run the command):

EXEC sp_grantlogin 'MATRIX\ASPNET'

The user can now log in to the database server, but won't be able to access any databases. Grant access to the user on the database by executing:

USE database
EXEC sp_grantdbaccess 'MATRIX\ASPNET'

where database is the database that the user is granted access to. This adds the user to the public database role. All users granted access to a database are members of this role. By default, this role has no permissions on objects created by the database owner. To grant permissions, you need to use the GRANT statement. It's recommended that you grant the public role only the permissions that are needed to perform the tasks. For example, if you wanted to allow all users to read data from the authors table, you would say

GRANT SELECT ON authors TO public

Don't get confused between the REVOKE and DENY statements. DENY allows you to filter members from a group - for example I could use the statement

DENY SELECT ON authors TO Mike

and SQL Server would then allow everyone except me to read from the authors table. REVOKE is used to undo an earlier GRANT or DENY operation. If you want to give the ASPNET user complete control over the database - which is unadvisable - you can instead run

EXEC sp_addrolemember 'db_owner', 'MATRIX\ASPNET'

I really don't advise this, though, as a mistake in the data access layer could corrupt your database. I recommend restricting the db_owner role to development and deployment use onl