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.

Nathan McKaskle
  • 2,926
  • 12
  • 55
  • 93
  • Is this a web.config file? – Jacob H Jun 06 '17 at 19:05
  • The string? Yes. – Nathan McKaskle Jun 06 '17 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. – Jacob H Jun 06 '17 at 19:07
  • Have you tinkered with your hosts file any? – Paurian Jun 06 '17 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? – CtrlDot Jun 06 '17 at 21:52
  • The windows firewall is not enabled. No hosts file tinkering has been done. The 2012 IIS server is on prem while the database server is in Azure. I don't know about the allow list in azure but I'm pretty sure it's not part of any Network Security Group in there. I can telnet fine to the SQL port from the IIS server. – Nathan McKaskle Jun 08 '17 at 15:43
  • Furthermore I've followed all these suggestions and nothing seems to apply here. TCP is enabled, firewall is disabled, SQL is started, remote connections are enabled, login account has access (SQL login). Only thing is I'm not sure if the computer account of the web server has access. http://support.sysonline.com/support/solutions/articles/4000016491-errmsg-a-network-related-or-instance-specific-error-occurred-while-establishing-a-connection-to-sql – Nathan McKaskle Jun 08 '17 at 15:51
  • Also tried creating an ODBC connection and that worked fine. Not sure what else it could be or why it works fine on the machine where I developed the app. – Nathan McKaskle Jun 08 '17 at 18:20
  • The SQL server shows nothing when I attempt to run the app and it gets that error. It's like it's not even hitting the SQL server at all, no traffic to that address. I can ping the address of course, I can telnet to it, I can even create an ODBC connection to it, but IIS won't talk to it. It's weird as hell. No firewalls are involved here. My other IIS server connects just fine. I don't get this. – Nathan McKaskle Jun 08 '17 at 20:05
  • Is this related to apppool permissions or something? I'm at a complete and total loss here. Even my .net 4.5.2 forms app I made to test sql connections works fine. – Nathan McKaskle Jun 12 '17 at 15:05

2 Answers2

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

Amor
  • 8,325
  • 2
  • 19
  • 21
  • Firewall is disabled. Also none of this is firewall related, SQL server is working fine from my personal Win 2012 VM. – Nathan McKaskle Jun 07 '17 at 15:11
  • 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. – Amor Jun 08 '17 at 08:11
  • Yeah firewall is disabled on all servers internally. Ports are open. I have no issues telnetting to that port. Definitely not a network issue. – Nathan McKaskle Jun 08 '17 at 15:34
  • 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(); – Amor Jun 09 '17 at 06:43
  • 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. – Nathan McKaskle Jun 12 '17 at 14:38
  • I run it on my developer machine it connects fine, I run it on the web server it gets the error. WTF? If none of what you put above applies and everything is correct, what could it possibly be? – Nathan McKaskle Jun 12 '17 at 14:50
  • Thanks, yeah tried all those and dynamic security isn't even a section in the web.config. I really don't get it at all, the web application is literally the only thing that cannot "see" the SQL server. It makes no sense at all. – Nathan McKaskle Jun 13 '17 at 13:28
  • The one thing I have not tried yet is enabling logging to see what it's trying to connect with. – Nathan McKaskle Jun 13 '17 at 13:30
  • It's trying to connect to something called "LocalSqlServer", I don't have a single mention of that provider name in my entire solution nor is it even mentioned anywhere on the web server. I have no idea where that's coming from. – Nathan McKaskle Jun 13 '17 at 13:58
  • Configuration Error Parser Error Message: The connection name 'LocalSqlServer' was not found in the applications configuration or the connection string is empty. Source Error: An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed remotely (for security reasons). It could, however, be viewed by browsers running on the local server machine. Source File: C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config\machine.config Line: 283 – Nathan McKaskle Jun 13 '17 at 14:04
  • It absolutely refuses to stop using machine.config. Using in the web.config doesn't work either. This makes absolutely no sense at all. It's like it won't see the web.config, period. – Nathan McKaskle Jun 13 '17 at 14:09
  • Please search LocalSqlServer in your web application solution. Or it is used by the referenced assembly. Please also try to delete original files before publishing as suggested in my reply. – Amor Jun 13 '17 at 14:57
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.

Nathan McKaskle
  • 2,926
  • 12
  • 55
  • 93