MVC 4 Connectionstring to SQL Server 2012

Question

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.

Solution 1

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.

Solution 2

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

Solution 3

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;"

Solution 4

You may need to enable the role manager in config

<roleManager enabled="true"/>

Solution 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.

View more solutions

Comments

[Travis J]

Can you show the code which produced this from your controller? Also, can you show us the assembly showing that role manager is enabled (since it is suggested in the error message)

[Parag Meshram]

Is it the exact connection string you are using? I am doubtful about MyServerName and MyDatabaseName. Just to confirm, have you replaced them properly?

[lhan]

@TravisJ - my controller didn't produce this. I'm seeing this error when clicking PROJECT -> ASP.NET Configuration in VS2012.

[lhan]

@paragMeshram - yes I verified I'm replacing them correctly.

[lhan]

Thanks for the reply. I've tried with your suggestion, but I still got the same error.

[lhan]

Thanks for the reply. I tried adding this under <system.web> but I am still getting the same error.

[lhan]

How do I know what IP address and port to use?

[Parag Meshram]

Keep port 1433 as it is, as it is a default. Try ping command for getting IP Address. Just type ping MyServerName in command prompt and you will see IP address in output.

[manuel]

please try this ConnectionString: user id=UserName;password=Password;initial catalog=DatabaseName;data source=SQLServerIPorFQDN;Connect Timeout=30;

[lhan]

I think something else may be wrong as I am not able to connect in SQL Management Studio using my default instance (i.e. MyServerName\MSSQLServer).

[Parag Meshram]

Is that sql server database installed on the same system you are developing ?

[Parag Meshram]

@lhan16 - which authentication mode you are using while connecting through Management Studio? Windows Auth or Sql Serve Auth?

[manuel]

Can you check if TCP connection to port 1433 to SQL server works? try this: telnet SQLServerIPorFQDM 1433 if SQL server is installed on default TCP port

[lhan]

SQL auth (mixed mode). Also, when I ping my server name it doesn't come back with an IP address, just the generic 0% packet loss, etc.

[lhan]

I'm not able to get the IP for my SQL Server. I've tried pinging my server (which is also the name of my computer) but it doesn't return an IP?

[manuel]

If SQL server and application which use SQL server is on same server, for server name localhost can be used (127.0.0.1). Try "ping localhost" this should work and in connection string in this case for server=ServarAddress you can use this: server=localhost or server=127.0.0.1

[Dan Beaulieu]

thank you for showing this in context, every example i've seen starts with "server... rather than connectionString="server