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

Question

Hi,

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

This problem is not related to asp.net mvc at all, it's purely a database issue.

From what you described.

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.

I would make sure :

1> sql express is running. 2> what kind of protocol are you set for connectivity. I normally select tcp/ip.

Hope this helps.

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.

Answer 3

You can look up how to run aspnet_regsql against SQL Express (there's lots of blog posts about it). I've had issues getting it working right in the past (bad lucky, probably) but you can also do this:

1. run the MVC app after creating the project
2. click the Log On link, then register a new account (this will create a SQL Express database for you)
3. (optional but handy:) in VS, add the MDF file to the project (it's under App_Data)
4. (optional:) rename the MDF file, and update the connection strings in web.config (replace aspnetdb.mdf with NerdDinner.mdf or whatever name you chose)

Lame workaround, I know, but it will get you up and running without too much fiddling around.

Answer 4

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.