0

I'm getting the following error once I move the app off onto its own Windows 2012 R2 IIS web server that is on Prem. The SQL 2014 DB server is in Azure. Firewall is disabled.

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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Further:

The connection string specifies a local Sql Server Express instance using a database location within the application's App_Data directory. The provider attempted to automatically create the application services database because the provider determined that the database does not exist.

My connection strings DO NOT specify any local SQL Express DB:

 <connectionStrings>
    <remove name ="NewUserToolContext"/>
    <remove name ="DefaultConnection"/>
    <add name="NewUserToolContext" connectionString="Data Source=SQLDBSERVERNAME;Initial Catalog=NewUserTool;Integrated Security=False;User Id = USERID;password = PASSWORD" providerName="System.Data.SqlClient" />
    <add name="DefaultConnection" connectionString="Data Source=SQLDBSERVERNAME;Initial Catalog=NewUserTool;Integrated Security=False;User Id = USERID;password = PASSWORD" providerName="System.Data.SqlClient" />
  </connectionStrings>

My partial _Layout view has some code that checks role membership before displaying specific menu items. After doing a lot of searches on this I found old posts from 2012 and one post from way back in 2013 regarding some kind of membership initializer. The post was incredibly vague and confusing and didn't seem relevant. It also didn't explain anything about what to do, especially not for MVC 6 Entity Framework in 2017. I tried, I couldn't find anything they were talking about. I started learning ASP.NET last year with MVC 6.

Improve this question
10
  • Is this a web.config file? Commented Jun 6, 2017 at 19:05
  • The string? Yes. Commented Jun 6, 2017 at 19:05
  • Have you whitelisted the server in Azure? I don't think the SQL Express part is relevant. That seems to be the default behavior when the connection fails. Commented Jun 6, 2017 at 19:07
  • Have you tinkered with your hosts file any? Commented Jun 6, 2017 at 20:12
  • Are these VMs deployed in the same subnet? What DNS settings do you have? Can you confirm the windows firewall on the SQL box is allowing connections? Commented Jun 6, 2017 at 21:52

2 Answers 2

Reset to default
1

Firstly, have you enable 1433 port in the firewall of your VM? If no, you could turn on this port using following steps.

  1. On the Start menu, click Run, type WF.msc, and then click OK.
  2. In the Windows Firewall with Advanced Security, in the left pane, right-click Inbound Rules, and then click New Rule in the action pane (upper right corner).
  3. In the Rule Type dialog box, select Port, and then click Next.
  4. In the Protocol and Ports dialog box, select TCP. Select Specific local ports, and then type the port number of the instance of the Database Engine, by default SQL Server uses 1433. Please input 1433 and Click Next until finish the steps.

Secondly, you also need to enable 1433 port in Network Security Group which related to your VM. Steps below are for your reference.

  1. Find the Network Security Group name which related to your VM in Netowrk interfaces panel.

enter image description here

  1. Open this NSG, click [Add] button in the Inbound security rules panel

enter image description here

  1. Add a rule as following.

enter image description here

  1. After that, you could access the database hosted in VM.

Edit 2017/6/13

Firstly, I suggest you write the connection string to a log file before using it on your server. You will find whether the right connection string is used to connect your server. Maybe the connection string is override by other configurations.

In addition, is your web server hosted on Azure VM or local machine?

Please also try to enable 32-bit applications in your app pool advanced setting.

enter image description here

Some other things you could try.

  1. Please check whether you have configured Dynamic IP Security or ipSecurity in your web.config. It will block any IP addresses based on your configuration.

  2. Have you tried removing all the original files at your test server when deploying your release?

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

12 Comments

Firewall is disabled. Also none of this is firewall related, SQL server is working fine from my personal Win 2012 VM.
To check whether it is a SQL Server issue, you could run a telnet command on your web server. "telnet sqlserver-ip 1433". In addition, is your web app host on Azure VM? You also need to open the outbound port 1433 on your web server.
Yeah firewall is disabled on all servers internally. Ports are open. I have no issues telnetting to that port. Definitely not a network issue.
Since ODBC connection is working. To further debug your issue, I suggest you create a Console Application, and run following code to check whether the issue is related to ADO.NET SqlConnection. SqlConnection connection = new SqlConnection("put your connection string"); connection.Open();
So this morning I tried that using a .net 4.5.2 windows forms application and it worked fine. Connection successful. Not sure what the deal is now.
|
1

After discovering finally that it was not using the correct connection strings I provided in the web.config, but rather the LocalSqlServer connection string in the machine.config, I soon realized that it was using that because it was being referenced in the AspNetSqlMembershipProvider and AspNetSqlProfileProvider and role provider sections in the machine.config. I had to find/replace everything referencing LocalSqlServer and replace it with the connection name I wanted to use, DefaultConnection. I then had to run aspnet_regsql.exe on my developer machine from the visual studio command prompt, and point it to the database to create the schema. After this my application began working.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.