Unable to connect to SQL Server running on VM from Azure Web App

karanlik 21 Reputation points
2021-03-12T10:01:22.15+00:00

Hi all,

I've some connectivity issues between an SQL server running on an VM (on Azure) and an Azure Web App connected to the same VNET.

Setup details;

  • VM Name: vmtest1
  • SQL Server running on 1433 (and I can connect to this SQL Server via "vmtest1" Public IP address)
  • Virtual Network: "vnet1"
  • Subnet1: 10.0.0.0/24 (for VM NIC)
  • Subnet 2: 10.0.1.0/24 (for App Services)
    ** This VM has a "primaryNic1" associated with Virtual Network "vnet1"
  • Network Security Group: nsg1 (for "primaryNic1" only)
  • An Azure Web App (.NET Framework 4.8 + Windows OS): "website1"
  • both "Azure Web App" and the "VM" is in the same "Resource Group" & "subscription"
  • "App Service" is connected to "vnet1" via "VNET Integration". And I can see that it is assign WEBSITE_PRIVATE_IP from 10.0.1.0/24 subnet with 10.0.1.254 IP address.

Now with this setup;
* I'm able to connect to SQL Server running on VM locally (SQL Server is running & accepting connections)
* I'm able to connect to SQL Server locally within VM via sqlcmd;

C:\Users\testadmin1>sqlcmd -S tcp:10.0.0.4\MSSQL2014 -U sqltestuser1 -P correctPassword
1> select db_name()
2> go

my_db_name

(1 rows affected)
1> exit

  • I can "tcpping" from "App Service" the VM private IP Address & port 1433;

C:\home\site\wwwroot>tcpping 10.0.0.4:1433
Connected to 10.0.0.4:1433, time taken: 78ms Connected to 10.0.0.4:1433, time taken: <1ms Connected to 10.0.0.4:1433, time taken: <1ms Connected to 10.0.0.4:1433, time taken: <1ms Complete: 4/4 successful attempts (100%). Average success time: 19.5ms
C:\home\site\wwwroot>

  • I'm able to see that with a wrong SQL server password, I'm getting rejected by SQL Server (from App Service Kudu console)

C:\home\site\wwwroot>sqlcmd -S tcp:10.0.0.4\MSSQL2014 -U sqltestuser1 -P wrongPassword
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login failed for user 'sqltestuser1'..
C:\home\site\wwwroot>

  • But with the correct password I'm not able to connect to SQL server (and get the prompt successfully). Similary I'm not able to connect from my "website1" app.

I guess there is some sort of "routing problem" from VM side to App Service side. But couldn't figure it out so far.

Any advice is highly appreciated..

thanks

SQL Server on Azure Virtual Machines
Azure App Service
Azure App Service
Azure App Service is a service used to create and deploy scalable, mission-critical web apps.
6,865 questions
Accepted answer
  1. Erland Sommarskog 100.9K Reputation points MVP
    2021-03-14T11:31:35.737+00:00

    So this is the typical "server not found" error, which indicates that your connection string is pointing in the wrong direction.

    However, if you get this error after 1-2 seconds, it seems that your connection timeout is too low. The default connection timeout is 15 seconds, so I think that you first you scrutinise your application for this setting. If you can verify that it takes 15 seconds to get this error, it is time to look at the connection string. (I think that the connection string is one of these places where you can set the connection timeout.)

    But, wait, you said you were not able to connect with SQLCMD with the correct password? What error message do you get in this case, if any? And what error message?

    Keep in mind, that in order to help you, we need to rely that you give us accurate diagnostics.

    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. karanlik 21 Reputation points
    2021-03-14T07:19:51.123+00:00

    Hi,

    What happens? Do you get an error message? Or does it just hang?
    --> waits for 1-2 seconds and "C:\home\site\wwwroot>" prompt comes back. sp_who and sp_who2 only returns my session from my laptop (via public IP).

    And advice?

    Thanks.

  2. karanlik 21 Reputation points
    2021-03-14T11:18:33.477+00:00

    Below is the error message in the application logs:

    2021-03-14 07:18:05,710 ASP.error_aspx user_name : Anonymous -- Error Message: 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) -- Page : main_page.aspx -- Function : Void .ctor(System.Data.ProviderBase.DbConnectionPoolIdentity, System.Data.SqlClient.SqlConnectionString, System.Data.SqlClient.SqlCredential, System.Object, System.String, System.Security.SecureString, Boolean, System.Data.SqlClient.SqlConnectionString, System.Data.SqlClient.SessionData, System.Data.ProviderBase.DbConnectionPool, System.String, Boolean, System.Data.SqlClient.SqlAuthenticationProviderManager) -- Details : 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)

    0 comments
  3. karanlik 21 Reputation points
    2021-03-22T13:46:21.123+00:00

    Hi All,

    I just wanted to give some extra information, in case some other stucks in this problem as well.

    To summarize; my problem was about the connection string unfortunately :( My SQLServer instance was not accepting "instance name" in the connection string, but unfortunately I couldn't figure out the problem while using different clients (sqlcmd and .NET Framework connection).

    • My very first problem was about my SQL Server instance not listenin to TCP/IP ports. I needed to update these IP adresses using regedit and finally managed to listen correct IP/Port pairs
    • Second item was about firewall. I did my tests with firewall disabled (won't recommend this)
    • Third item was probably came and went during my tests; VNET Integration in my region had some problems during my test hours, but this was not the root cause.
    • Last; the main "misleading" point is the sqlcmd itself running within App Service domain. I was making connectivity checks with tcpping and sqlcmd from AppService console. tcpping was successfull but sqlcmd was not providing a prompt ( "> .." ) which is the behaviour upon successful connection with "ordinary" cmd line environments. PLEASE DO NOT FORGET THAT sqlcmd WON'T GIVE YOU SQL PROMPT ON SUCCESSFUL CONNECTION.

    either remember it or look for the problems at network parts like me :)

  4. karanlik 21 Reputation points
    2021-03-23T06:43:04.443+00:00

    Hi,
    I didn't try with a command you've mentioned (sqlcmd -Q "select @@version"). Instead I was trying to connect and have a prompt. Your previous questions was this:

    "But, wait, you said you were not able to connect with SQLCMD with the correct password? What error message do you get in this case, if any? And what error message?"

    and my response was "waits for 1-2 seconds and "C:\home\site\wwwroot>". So to make it more clear, below is the behaviour;

    C:\home\site\wwwroot>sqlcmd -S tcp:10.0.0.4\MSSQL2014 -U sqltestuser1 -P correctPassword
    C:\home\site\wwwroot>

    I am just getting back the "C:\home\site\wwwroot>" command line prompt. Try this from a Windows CMD line and you'l get ">" prompt in which you can execute your sql statements.

    I hope it is clear now.

    0 comments