0

I have a Web Forms .NET 4.5+ application running on an Azure VM web server (Windows Server 2019) using IIS 8.

The site will load with an index.html page in production (In other words, the web server connection is good LIVE, without a DB connection). The site fully loads locally, database included (Locally means running the site through Visual Studio 2019 / Chrome on my desktop)

My web.config is the same locally as it is in prod -> it has the same connection string, etc.

My SQL database is not on a traditional SQL Server instance, through a VM on a server. It is technically an "Azure SQL DB"

The problem, and error: when I enter the site URL, the page times-out and fails to connect to the SQL Server/DB. Here is the full error text:

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)

  • I have added an inbound AND outbound rule to my firewall for Port 1433
  • I have a valid login for my connectionString (again, the same SQL connection works locally
  • Again, the site loads without any attempt at a DB connection
  • I have attempted a "dumbed-down" version of the connection string (full string seen below). Meaning, removed certain qualifiers and flags. Tried different combinations

Here's the full connection string that Azure provides. This is what I'm using:

Server=tcp:dbservername.database.windows.net,1433;Initial Catalog=D2Items_DB;
Persist Security Info=False;User ID=myUserName;Password=myPassword;
MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

Excess/redundant information:

  • Entering the website's IP address or URL in my web browser is what fails
  • It does NOT fail when I am NOT making a database connection (example: When index.html is the ONLY file in the web site's folder on the server)
  • This is my first time deploying/publishing a web app to a public domain. I have 5-6 years of .NET full-stack experience, but I have always worked on intranet only, and on pre-configured servers. I chose Azure because it was free with a trial. I thought it would be easy. I was wrong. I'm considering other options.
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mike
  • 51
  • 8
  • What does _locally_ mean? Inside the VM or on your local laptop? Where is "production" ? It's unclear where various success and failure cases are working and not working. It'd help to do a table describing each success and failure case. Also how your Azure SQL firewall is configured – Nick.Mc Jul 04 '19 at 00:47
  • Updated. There is only one success case, and one failure case – Mike Jul 04 '19 at 01:11
  • 1
    @Mike, this is so common its unfair for us to keep guessing what it could be. Can you detail what you've tried that doesn't work looking at: https://stackoverflow.com/questions/1391503/ https://stackoverflow.com/questions/16256533 https://stackoverflow.com/questions/18060667 https://dba.stackexchange.com/questions/49365 – Jeremy Thompson Jul 04 '19 at 01:16
  • 1
    Reading through all that text carefully, it seems like your Azure VM cannot connect to the Azure SQL. Can you remote on to your Azure VM and try some bsaica connectivity tests to Azure SQL. Most likely you need to add a firewall exception to your Azure SQL to allow the VM to connect. – Nick.Mc Jul 04 '19 at 01:18
  • This would seem likely to me, too. Do you have any idea how I can do some simple SQL connection tests from the VM? – Mike Jul 04 '19 at 01:27
  • There about a million articles on google. How about this one: https://success.trendmicro.com/solution/1117712-creating-a-data-link-udl-file-and-testing-the-connectivity-to-a-microsoft-sql-server – Nick.Mc Jul 04 '19 at 01:29
  • This one is less spammy https://blogs.msdn.microsoft.com/steverac/2010/12/13/test-remote-sql-connectivity-easily/ – Nick.Mc Jul 04 '19 at 01:30
  • Nick, I wouldn't be here asking questions if Google had a solution to my problem. I've been in MSDN and Google HELL for 2 days. I don't think this will work, because my SQL instance is not technically a "SQL Server" on a VM, is it an Azure SQL instance. At first, I thought you meant testing the SQL connection through the web server, but I think you meant through the SQL Server – Mike Jul 04 '19 at 01:43
  • Yes this connectivity test will work - did you try it? Remote on to your VM and try it. I expect connectivity will fail and that will confirm that the issue is not your web application but your Azure SQL firewall setup. The server name is `tcp:dbservername.database.windows.net,1433`. – Nick.Mc Jul 04 '19 at 01:52
  • 1
    We can solve this and I will try to be a bit more polite about it :) – Nick.Mc Jul 04 '19 at 01:54
  • We are testing that your VM can connect to the SQL Server - I expect that it can't, and this will narrow the issue down. – Nick.Mc Jul 04 '19 at 01:57
  • Ok thank you :) I had to use your first hyperlink suggestion, because i had to "Show file extensions" to get it to work (since it's a new VM with default Windows settings). I tried this method, and it returned "Test Connection Succeeded" – Mike Jul 04 '19 at 02:04
  • 1
    Can you confirm this is from the VM running the web server? Interesting. This would indicate that there is no connectivity issue from the VM to Azure SQL, but there is when you try and connect from within the web application that is running on the web server. PS don't give up on Azure, there's just a few new things to learn first. – Nick.Mc Jul 04 '19 at 02:07
  • Yes, this test was done on the VM running the web server. I added the same user/pw from the connectionString (SQL Authentication) and ran the test. And yes you're correct - it's an issue from the App on the web server to the SQL DB. – Mike Jul 04 '19 at 02:15
  • Proof: https://imgur.com/ihvDm2L – Mike Jul 04 '19 at 02:26
  • 1
    I can't get to the picture but I believe you. Is it possible that you can echo the connection string within your web app just to be absolutely certain you are connecting to the server that you think you are connecting to from within the web app? My suspicion now is that the app running on the web server is not using the connection string that you think it is, in particularly the _server name_. Could you possibly also try temporarily removing the `,1433` part from your web app connection string and see if that makes a difference. – Nick.Mc Jul 04 '19 at 03:18
  • Yes, I had mentioned in my orig post that I'm using the exact same connectionString both locally and in Production for this reason. I tried keeping everything exactly the same between the two versions of the app. I did try removing the explicit port 1433 from the connectionString, and nothing changed. I may see if I can reach out to Azure support next, to get their take. Because I think this must be an Azure configuration issue, like some sort of config change on the Azure Portal that I've missed. – Mike Jul 04 '19 at 17:08
  • I wanted to make sure that a Transform file (like web.release.config) was not messing me up, so I copied my local web.config over to the web server exactly as it is, and that did not change anything either. This has GOT to be a port/firewall/networking issue!! – Mike Jul 04 '19 at 17:22
  • You've already established that you can connect from the VM to Azure SQL so it's unlikely to be a port /firewall issue. It would be great if you could alter your web page to code to echo the connection string right before you try to connect. – Nick.Mc Jul 04 '19 at 23:19

3 Answers3

0

This error occurs if your asset didn't load.

You need to try and check your base url inside your project,

Bhargav Rao
  • 50,140
  • 28
  • 121
  • 140
R oV
  • 59
  • 6
  • The base URL works locally (when I run through VS), and it works on the web server when I change the base URL to index.html (a blank html page) – Mike Jul 04 '19 at 01:24
0

Did you add your server IP address at SQL server FW config, https://learn.microsoft.com/en-us/azure/sql-database/sql-database-firewall-configure#creating-and-managing-ip-firewall-rules

Inzi
  • 348
  • 3
  • 12
  • If I understand correctly, are you saying that I need to add the Web Server's IP address to the "allowed" SQL Firewall IP addresses? If so, I tried that and it did not work. TY – Mike Jul 04 '19 at 02:10
-1

The server that SQL is installed on, should allow incoming connections on port 1433.

Check out this article here. I know it is for 2008 R2 but some of the settings are similar

Bhargav Rao
  • 50,140
  • 28
  • 121
  • 140
Darren M
  • 65
  • 1
  • 8
  • I don't know how to find out through Azure. I'm assuming you mean the SQL Server? I don't see anything through Azure's web portal about ports/networking on the SQL Server, only on the Web Server. On the web server, I have added inbound and outbound rules for 1433 (to allow) – Mike Jul 04 '19 at 01:15
  • Unfortunately, when I connect to the Azure server through SSMS, I do not have the same context menu when I right-click the server in the Object Explorer, as your msdn link shows. I'm not sure why that is, but it means that I cannot access the SQL Server's "Properties", at least not through SSMS – Mike Jul 04 '19 at 01:19
  • Hi Mike, Unfortunately I haven't had a lot to do with the Azure SQL on a VM but I did find this article which may be of some help https://learn.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-connect – Darren M Jul 04 '19 at 01:24
  • Per this suggestion, I have "Allow access to Azure Services" turned ON already: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ea26868e-95a4-4254-9ade-6a2efee37456/remote-access-to-azure-sql-server?forum=sqlgetstarted – Mike Jul 04 '19 at 01:26
  • I'll repeat my comment from 10 minutes ago: remote on to your VM (the one running the web server) and perform a basic SQL Server connectivity test. – Nick.Mc Jul 04 '19 at 01:28
  • I agree with Nick. If you log on to your VM and go into the SQL Configuration Manager is TCP/IP Enabled? In the console pane, click Protocols for MSSQLSERVER (the default instance name.) In the details pane, right-click TCP and click Enable if it is not already enabled. – Darren M Jul 04 '19 at 01:32
  • I only have a VM for the web server. I believe SQL is running through Azure somehow. I'm starting to think I need to use a different service than Azure. I will look closely through the Azure web portal and see if there is any explicit setting for TCP/IP connections – Mike Jul 04 '19 at 01:37
  • There is _no_ SQL Server configuration manager. Look at the connection string - it's connecting to Azure SQL. You're just confusing him. – Nick.Mc Jul 04 '19 at 02:03
  • Agreed. This has helped me to understand the difference, though. Now I understand that it is not a "traditional" SQL Server, and that there is no configuration manager (which I was confused about, because I have used a SQL config manager before) – Mike Jul 04 '19 at 02:12