For ASP.NET webforms, I have always needed the following server login: [machine_name\ASPNET].
Questions
I think it depends on your connection string. You'll need to add whatever you're connecting with.
The requirements are the same for ASP.NET and ASP.NET MVC sites. If they use the same database connection metholodogy (string), their requirements are identical.
Also note that the id you provided will change for both ASP.NET and ASP.NET MVC sites if you move to IIS6 or IIS7.
MVC has nothing to do with it. Your ASP application will connect to SQL Server using the identity of the Application Pool that runs the ASP site. See:
By default application pools run under the built in NETWORK SERVICE account. When connecting locally to a SQL Server on the same host the NETWORK SERVICE authenticate as itself and you need to grant login permissions to it, but when it connect remotely to a SQL instance on a different host the NETWORK SERVICE will authenticate itself as the host machine account ('domain\machinename$') and that account needs to be granted login permissions.
Another piece of the puzzle is to know whether the ASP application impersonates the HTTP request or not. When impersonation occurs, the connection will be attempted under the original request caller identity. When the SQL server is on a different host the ASP application has to also delegate the impersonated context further to SQL Server, and in order to do so it has to be trusted for constrained delegation How To: Use Protocol Transition and Constrained Delegation in ASP.NET 2.0.
Ultimately the application pool identity is under your control and your responsibility to set correctly. Whatever identity the ASP pool has, that must be granted login permissions on the SQL Server. MVC is just a library loaded in the ASP application and does not change the security requirements.
Here's what you need to do.
Install SQL Server Express 2005. You'll need SQL Server 2005 Management Studio and the SQL Server Surface Area Configuration. These tools will come in handy when troubleshooting connections. A 'successful' install will provide both of the above.
Launch SQL Server Express 2005 Management Studio. Look for Server Type:Database Engine and Server Name:.\SQLEXPRESS. Keep in mind during setup the .\SQLEXPRESS value may have your machine name as a prefix ie. dan\SQLEXRESS. Copy this value and paste it into the web.config file - connnectionstring = data source = dan\SQLEXPRESS in your MVC project.
As for ASP.NET MVC - Install the 3.5 framework and install ASP.NET MVC version 1. Make sure the 3.5 framework is selected under your new MVC project. Running the project will launch the built-in cassini web server ( not IIS ).
In the app - Register yourself. Verify under the app_data folder in your project to see if a new .mdf file was created. This file gets created by the first user.
To quote ScottGu - Hope this helps ! Happy New Year !