CODEWITHSUNDEEP
asp.net-mvc-3azureazure-sql-database
JayneT
JayneT

Reputation: 765

MVC app deployed on Azure cannot connect to SQL Azure Database but can connect via SSMS

I have an MVC 3 app using Entity Framework that was happily hosted in Azure and talking to SQL Azure Database 2 days ago. I then had a minor schema change to the database and so dropped and re-created the database via SSMS and also published the application again as I had added the default MVC app login role controls to the site - since then I cannot get my app to connect to the database and get the following error logged in an Elmah xml file:

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)" source=".Net SqlClient Data Provider" detail="System.Data.EntityException

I can connect to the SQL Azure Database using SSMS and when I run my application in the Azure emulator connected to the SQL Azure Database everything runs perfectly but once published it refuses to connect. Any help would be massively appreciated!

Upvotes: 1

Views: 1408

Answers (2)

hydr
hydr

Reputation: 408

A SQL Azure connection string (using EF Model First / Database First) typically lookes like this:

<connectionStrings>    
    <add name="SampleEntities" connectionString="metadata=res://*/Models.SampleEntities.csdl|res://*/Models.SampleEntities.ssdl|res://*/Models.SampleEntities.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=tcp:abcdefg.database.windows.net,1433;Database=asdf;User ID=manager@abcdefg;Password=XXXXXX;Trusted_Connection=False;MultipleActiveResultSets=True;Encrypt=True;&quot;" providerName="System.Data.EntityClient" />
</connectionStrings>

A SQL Azure Connection string (using Code First) typically looks like this:

<connectionStrings> 
   <add name="SampleContext" connectionString="Data Source=tcp:abcdefg.database.windows.net,1433;Database=asdf;User ID=manager@abcdefg;Password=XXXXXX;Trusted_Connection=False;MultipleActiveResultSets=True;Encrypt=True" providerName="System.Data.SqlClient" />   
</connectionStrings>

The second connection string will also work without Entity Framework.

Upvotes: 1

Igorek
Igorek

Reputation: 15860

I believe you've published a config to Azure that does not have a correct SQL Azure server name, but rather some local/on-premise SQL server. Could this be it? Can you remote into the instance and validate correct .config and SQL Azure server?

From the error it appears that your app is not using TCP/IP to connect to your SQL Azure, but rather Named Pipes, which usually implies that the SQL Azure server name is not likely a valid TCP/IP host.

Upvotes: 0

Related Questions