Azure Sql connection from azure website not working

Question

I'm have the following connection problem when connecting to the website is deployed to azure. The site work locally when connecting to Azure Sql using the same account. The site is MVC 4 EF 5.

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)

Here is my web.config.

<?xml version="1.0" encoding="utf-8"?>
<!--
  For more information on how to configure your ASP.NET application, please visit
  http://go.microsoft.com/fwlink/?LinkId=169433
  -->
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=4.4.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <connectionStrings>
    <add name="MyWhesEntities" connectionString="metadata=res://*/MyWhesModel.csdl|res://*/MyWhesModel.ssdl|res://*/MyWhesModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=localhost;initial catalog=mywhes;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
    <add name="MyWhesContext" connectionString="tcp:ngw4ziez6g.database.windows.net,1433;Database=mywhes;UserID=********;Password=********;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;" providerName="System.Data.SqlClient" />
  </connectionStrings>
  <appSettings>
    <add key="webpages:Version" value="2.0.0.0" />
    <add key="webpages:Enabled" value="false" />
    <add key="PreserveLoginUrl" value="true" />
    <add key="ClientValidationEnabled" value="true" />
    <add key="UnobtrusiveJavaScriptEnabled" value="true" />
    <add key="enableSimpleMembership" value="false" />
    <add key="autoFormsAuthentication" value="false" />
  </appSettings>
  <system.web>
    <compilation debug="true" targetFramework="4.0">
      <assemblies>
        <add assembly="System.Data.Entity, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
      </assemblies>
    </compilation>
    <httpRuntime />
    <machineKey />
    <authentication mode="Forms">
      <forms loginUrl="~/Account/Login" timeout="2880" />
    </authentication>
    <pages controlRenderingCompatibilityVersion="4.0">
      <namespaces>
        <add namespace="System.Web.Helpers" />
        <add namespace="System.Web.Mvc" />
        <add namespace="System.Web.Mvc.Ajax" />
        <add namespace="System.Web.Mvc.Html" />
        <add namespace="System.Web.Optimization" />
        <add namespace="System.Web.Routing" />
        <add namespace="System.Web.WebPages" />
      </namespaces>
    </pages>
    <membership defaultProvider="WhesMembershipProvider">
      <providers>
        <clear />
        <add name="WhesMembershipProvider" type="MyWhes.Helpers.WhesMembershipProvider" connectionStringName="MyWhesContext" />
      </providers>
    </membership>
    <profile>
      <providers>
        <clear />
      </providers>
    </profile>
    <roleManager defaultProvider="WhesRoleProvider" enabled="true" cacheRolesInCookie="true">
      <providers>
        <clear />
        <add name="WhesRoleProvider" type="MyWhes.Helpers.WhesRoleProvider" connectionStringName="MyWhesContext" />
      </providers>
    </roleManager>
    <sessionState mode="InProc" customProvider="DefaultSessionProvider">
      <providers>
        <add name="DefaultSessionProvider" type="System.Web.Providers.DefaultSessionStateProvider, System.Web.Providers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="DefaultConnection" />
      </providers>
    </sessionState>
    <customErrors mode="Off"></customErrors>
  </system.web>
  <system.webServer>
    <validation validateIntegratedModeConfiguration="false" />
  </system.webServer>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Helpers" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-2.0.0.0" newVersion="2.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Mvc" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="0.0.0.0-4.0.0.0" newVersion="4.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.WebPages" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-2.0.0.0" newVersion="2.0.0.0" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
  </entityFramework>
</configuration>

    

Answer 1

Did you configure the firewall to allow connections from Azure services?

Answer 2

Yes and I set it as a local resource.

Answer 3

  <connectionStrings>
    <add name="MyWhesEntities" connectionString="metadata=res://*/MyWhesModel.csdl|res://*/MyWhesModel.ssdl|res://*/MyWhesModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=localhost;initial catalog=mywhes;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
    <add name="MyWhesContext" connectionString="tcp:ngw4ziez6g.database.windows.net,1433;Database=mywhes;UserID=********;Password=********;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;" providerName="System.Data.SqlClient" />
  </connectionStrings>     

Hello James,

Your Entity Framework Model "MyWhesEntities" connection string is still pointing to a local server, not to SQL Azure; or do you change it during runtime in your code?

And you should add the "data source=" property name to your second connection string, it's missing.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 4

You may refer to my slides that share in Slideshare

http://www.slideshare.net/cheahengsoon/how-to-create-sql-azure

Thank You.