Setting up a SQLEXPRESS Database for use with ASP.NET

If you set up a database using SQLEXPRESS for use with an ASP.NET site, you may get an error message similar to:

The login failed. Login failed for user 'MACHINENAME\ASPNET'

The reason for this is because the ASP.NET Worker process runs under the ASPNET User account, and if the database is using Windows Authentication it will deny access to that user.

Fortunately, the fix is very simple. Since SQLEXPRESS does not have any graphical management tools, you can use osql.exe to setup your database to work with the ASPNET account.

Fire up your preferred shell and type:

osql -E -S [YOURMACHINENAME]\SQLEXPRESS -d [YOURDATABASENAME]

This will fire up osql and connect to your database.

At this point, you should see a prompt. Enter the following lines:

EXEC sp_grantdbaccess [MACHINENAME\ASPNET]
GO
EXEC sp_addrolemember 'db_owner',[MACHINENAME\ASPNET]
GO

At this point, your ASP.NET site will be able to connect to the database, however it may not be able to perform operations to it. You may need to grant privileges to the account.

This is done on a table by table basis, so go back to osql and type:

GRANT SELECT,INSERT,UPDATE,DELETE ON [TableName] TO [MACHINENAME\ASPNET]
GO

Do this for each table in the database, if you have any stored procedures, you will also want to grant execute privileges:

GRANT EXECUTE TO [MACHINENAME\ASPNET]
GO

With all that done, you should be completely set up to use SQLEXPRESS with ASP.NET.