ASP.NET MVC newbie: can't get aspnet_regsql.exe to work

Question

I just installed Web Developer Express 2008 and the MVC framework (followed all the defaults, SQL Server Express was installed in the process). I followed the initial NerdDinner steps, created a database from the IDE and defined tables (worked fine).

To further define my tables, which heavily rely on the user-base, I had to figure out how to add the user tables to my newly-created database (so I can create tables with foreign keys to the users table), so I "jumped" to the authentication and authorization section in the manual, where they mentioned I need to run "aspnet_regsql.exe" from the directory "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727".

I found the file and ran it.

It asks for a server name and has some default (it's the initial of my computer name). I don't remember explicitly mentioning any such name during the installation of Visual Web Developer, I assume it's a default. "Windows Authentication" is selected. I can also choose "SQL server authentication" but I don't recall creating the user-name and password required for it.

When I come to choose a database, I click the drop-down of the "Database" combo-box, it takes a few seconds, and then it shouts the following error message:

"Failed to query a list of database names from the SQL server. A network-related or instance specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"

I researched this online and did not find a simple solution for my seemingly trivial problem.

To clarify, I don't want a remote connection but rather a local one.

Are there any steps I need to execute with my local SQL Server Express installation before I can go ahead with the regsql process?

This is plain vanilla, out of the box installation... Any ideas?

Answer 1

If you want to continue using the wizard it's actually a very simple solution. On the "Select the Server and Database" screen where you are getting this issue, the default value in the "server" textbox will be set to your machine name. Append the specific instance of SQL server you wish to connect to (ie LOCALHOST may become LOCALHOST\SQLEXPRESS).

If you point to a valid instance your "Database" drop down list will populate as expected.

Answer 2

First, copy a backup of your MDF file in case this command totally hoses your work. Try this: aspnet_regsql -A all -C "Data Source=.\SQLEXPRESS;Integrated Security=True;User Instance=True" -d "PATH_TO_MY_DATABASE.MDF_FILE"

Change PATH_TO_MY_DATABASE.MDF_FILE to the full path to your MDF file. It most likely resides in your APP_DATA folder.

This information comes from this blog post, and I have succesfully used it in the past to setup the membership tables for a SQL Server Express project with a MDF file database.