Create aspnetdb with SQL Server Standard edition (not Express) and Visual Studio 2010 for asp.net MVC project

Question

I am using VS 2010, MVC 3.0, SQL Server Standard edition (not Express). I am trying to create aspnetdb on a MVC 3.0 project (MusicStore) application.

When I click on WSAT (Web Site Administration Tool) - Security tab I am getting this error:

There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the button below to be redirected to a page where you can choose a new data store.

The following message may help in diagnosing the problem: Unable to connect to SQL Server database.

Can anyone please help on this.

Thanks.

Answer 1

Since you don't have SQL Server Express installed, you cannot use that connection string. The AttachDbFileName= and User Instance features are available exclusively in SQL Server Express.

You need to:

1. execute the aspnet_regsql utility (in your C:\Windows\Microsoft.NET\Framework\v4.0.30319 directory - adapt to your framework version, and possibly use the Framework64 if you're running on a 64-bit machine) against your SU\SQLSERVER instance to create the ASP.NET membership database on your server

2. you need to change the connection string to go to the server - not use the attached file:

   Data Source=SU\SQLSERVER;database=ASPNETDB;Integrated Security=SSPI;
   

Answer 2

I had faced the same issue and got it fixed by following below mentioned steps :-

1. Open SQL Server Management studio, just try logging in and check in the list of databases weather you have "aspnetdb" or not.
2. In case you dont have one, open command prompt and type this "c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe".
3. Click NEXT on first two screens and then on third screen check the value of the Server means server name. This name should be exactly the same using which you had logged in to Sql Server management Studio. In case the server name is different replace it with the one that you use for login. If you login using an IP address even that will do.
4. Keep the radio button for Windows Authentication checked and the database should be . Click on NEXT twice and then Finish.
5. Now you should be able to see "aspnetdb" if you repeat step 1.

6. Then add this code to your web.config file.

   <remove name="LocalSqlServer"/>
   <add name="LocalSqlServer"
        connectionString="data source=[YOUR LOGIN ID FOR SQL SERV MANG STUDIO];
     Initial Catalog=aspnetdb;
     Integrated Security=True;" />
   

7. Save it and then you can continue with ASP.NET Web Site Administration Tool .

   All the Best