MVC 4 Connectionstring to SQL Server 2012

问题:

I've created a brand new MVC 4 application in C# using Visual Studio 2012. I'm trying to connect to a brand new SQL Server 2012 (Standard) instance but I can't seem to get my connection string set correctly.

My connection string from my Web.config:

  <connectionStrings>
    <add name="ConnectionStringName"
        providerName="System.Data.SqlClient"
        connectionString="Data Source=MyServerName;
                          Initial Catalog=MyDatabaseName;
                          Integrated Security=False;
                          User ID=MyUserID;Password=MyPassword;
                          MultipleActiveResultSets=True" />
  </connectionStrings>

Every time I go to ASP.NET Configuration from within Visual Studio, the page loads, but as soon as I click "Security" I get the following message:

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.

I've verified that my credentials are correct (I can use them to connect via SQL Management Studio). Is there anything else I can check? I'm stumped.

UPDATE:

I wasn't able to connect to my default instance from within SQL Management Studio (MSSQLSERVER) so I reinstalled SQL, creating a named instance (LHSQLSERVER). Now I'm able to connect to that instance in SQL Management Studio, but I'm still getting the same error from the ASP.NET Configuration.

Another thing to note - the aspnet_regsql tool I ran was from the Framework64\v4.0.30319 folder. Is that correct if I am using .NET 4.5?

UPDATE 2:

I've tried replacing my connection string with a connection string to a remote site (i.e. mysite.winhost.com) that I know works, but I'm still getting the same error in the ASP.NET Website Configuration Tool? FWIW I'm also using Windows 8, but I didn't think that would matter.

Any thing else I can check?

UPDATE 3:

I found this post that says you don't need the aspnet_regsql tool anymore for MVC 4, so I re-ran the tool removing all the settings, but again, no luck. Has anyone done this with MVC 4 before?

UPDATE 4:

See my answer below for the solution I found.

回答1:

If you are able to connect using Sql Management Studio then I believe there must be problem with Connection String -

Connection String You are using follows format -

Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;
User ID=myDomain\myUsername;Password=myPassword;

Try using IP Address, Port in Data Source field like -

 <connectionStrings>
    <add name="ConnectionStringName"
        providerName="System.Data.SqlClient"
        connectionString="Data Source=190.190.200.100,1433;
                          Initial Catalog=MyDatabaseName;
                          Integrated Security=False;
                          User ID=MyUserID;Password=MyPassword;
                          MultipleActiveResultSets=True" />
  </connectionStrings>

OR try other variations -

Standard Security

Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;

Trusted Connection

Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;

Connection to a SQL Server instance

Server=myServerName\myInstanceName;Database=myDataBase;User Id=myUsername;
Password=myPassword;

Reference - http://www.connectionstrings.com/sql-server-2012

回答2:

didn't try with SQL 2012 but this Connection string worked fine with SQL 2008 R2

connectionString="Server=ServerAddress;Database=DataBaseName;User Id=Username;Password=Password;"

or

connectionString="user id=UserName;password=Password;initial catalog=DatabaseName;data source=SQLServerIPorFQDN;Connect Timeout=30;"

回答3:

You may need to enable the role manager in config

<roleManager enabled="true"/>

回答4:

Thanks everyone for the input, I have finally found a solution. I came across this article that describes the new Simple Membership that VS 2012 uses for MVC 4. I basically just started over, creating a new database, new MVC 4 project, and just followed the instructions in the article. Sure enough, just "registering" on the web page created the table(s) in my database and stored the user information.

回答5:

While lhan16's answer helped me, it would have been simpler to say that all you needed to add was a DefaultConnection. I started from the same place, and the database connection that the MVC uses doesn't work for the security framework. The authors of the framework never imagined that you would not use the DefaultConnection.