none
Unable to connect to Azure SQL database

    Question

  • Hi,

    I am unable to connect to Azure SQL DB.

    IPs, sub nets are added in SQL Firewall connection and access is "ON".

    I am able co connect to port 1433 via telnet but can not connect from SSMS or PS to the database.

    I have check in Network Watcher - Connection troubleshoot and there is no problem with connectivity.

    Please advise where else to check.

    Regards,

    Nikos

    Monday, April 15, 2019 2:41 PM

Answers

  • If traffic is between VMs in Azure and Azure SQL additional ports have to be open TCP/11000-11999.

    https://docs.microsoft.com/en-gb/azure/sql-database/sql-database-develop-direct-route-ports-adonet-v12

    • Marked as answer by NTDBA Tuesday, April 16, 2019 10:46 AM
    Tuesday, April 16, 2019 10:46 AM

All replies

  • Hello,

    If you are reaching your Azure SQL from internet you need to need to authorize your client's public IP from the Firewall rule of Azure SQL.

    If you are reaching your Azure SQL from a vnet you need to

    - Enable the Database service endpoint on your VM's subnet or vnet

    - Link your Azure SQL to your VM's subnet from the Azure SQL panel.

    Regards,

    James

    Monday, April 15, 2019 3:01 PM
  • Hi James,

    I have double check and everything is in place.

    I can telnet to the port and can open connection from .udl file however I am unable to connect from SSMS, sqlcmd and ps.

    Regards,

    Nikos

    Tuesday, April 16, 2019 10:20 AM
  • What error are you getting?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, April 16, 2019 10:37 AM
  • If traffic is between VMs in Azure and Azure SQL additional ports have to be open TCP/11000-11999.

    https://docs.microsoft.com/en-gb/azure/sql-database/sql-database-develop-direct-route-ports-adonet-v12

    • Marked as answer by NTDBA Tuesday, April 16, 2019 10:46 AM
    Tuesday, April 16, 2019 10:46 AM
  • The important detail not mentioned in your initial question is that you are connecting from an Azure-hosted client. Adding an excerpt to this thread from the "Outside vs Inside" topic in the referenced documentation here to better help others with similar a problem. Note that is ADO.NET 4.5 applications that require the additional ports, and only when connecting from inside Azure.

    <Excerpt> 

    Outside: Client runs on your desktop computer

    Port 1433 is the only port that must be open on your desktop computer that hosts your SQL Database client application.

    Inside: Client runs on Azure

    When your client runs inside the Azure cloud boundary, it uses what we can call a direct route to interact with the SQL Database server. After a connection is established, further interactions between the client and database involve no Azure SQL Database Gateway.

    The sequence is as follows:

    1. ADO.NET 4.5 (or later) initiates a brief interaction with the Azure cloud, and receives a dynamically identified port number.

    • The dynamically identified port number is in the range of 11000-11999.

    2. ADO.NET then connects to the SQL Database server directly, with no middleware in between.

    3. Queries are sent directly to the database, and results are returned directly to the client.

    • Ensure that the port ranges of 11000-11999 on your Azure client machine are left available for ADO.NET 4.5 client interactions with SQL Database.

    • In particular, ports in the range must be free of any other outbound blockers.

           - On your Azure VM, the Windows Firewall with Advanced Security controls the port settings.

    You can use the firewall's user interface to add a rule for which you specify the TCP protocol along with a port range with the syntax like 11000-11999.

    </Excerpt>


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Tuesday, April 16, 2019 12:02 PM