Unable to connect to SQL Server database

Question

I'm creating a web application in mvc 4.0 and I need to enable Membership and Authorization using Asp.netWeb Site Administration Tool but when I clicks the security tab it gives me an 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.

and my connection string is given below

 <connectionStrings>
    <add name="MusicStoreEntities"
    connectionString="Data Source=|DataDirectory|MvcMusicStore.sdf"
    providerName="System.Data.SqlClient"/>
  </connectionStrings>

I cant find any error in the code so please help me

which sql server version you are using – Shafqat Masood May 01 '13 at 12:18 — Shafqat Masood, May 01 '13 at 12:18

score 9 · Accepted Answer · edited May 23 '17 at 12:25

Set these in your web.config

<membership defaultProvider="SqlMembershipProvider">
  <providers>
      <add 
        name="SqlMembershipProvider" 
        type="System.Web.Security.SqlMembershipProvider" 
        connectionStringName="MusicStoreEntities"
       />
  </providers>
</membership>

Roles

<roleManager defaultProvider ="SqlRoleProvider" >
   <providers>
     <add
       name="SqlRoleProvider" 
       type="System.Web.Security.SqlRoleProvider" 
       connectionStringName="MusicStoreEntities"
     />
   </providers>
</roleManager>

see http://msdn.microsoft.com/en-us/library/ms731049.aspx for more info

p.s. nicked the example from here

score 0 · Answer 2 · edited May 01 '13 at 13:28

I do not know the error is due to wrong credentials or what but I can tell you an another method by which you can also check your Database connection.to do So

Go to Visual Studio, Visual Studio tools and then open the Visual Studio Command Prompt.
now write aspnet_regsql.exe command to run the ASP.NET SQL Server Setup Wizard. Click next in this screen to move on another screen.
again click next without changing any option from radio buttons so here it will ask you about Database information.
Check your connection and after you are done, you will see ASp.net membership tables will automatically created in the database you selected.

This error can also be caused due to lack of permissions or the database you are using could be already having membership tables, you can check that in your database. If above suggestion does not works for you then try this or your method by using a new database.

Unable to connect to SQL Server database

2 Answers2

Linked