none
Unable to connect to Azure Sql Database from SSMS RRS feed

  • Question

  • Hi

    I'm unable to connect to my SQL Database on Azure. 

    I followed the steps on: https://azure.microsoft.com/en-us/documentation/articles/sql-database-connect-query-ssms/ but i'm unable to get past step 3.

    I get the following error: 

    TITLE: Connect to Server
    ------------------------------
    Cannot connect to [server_name].database.windows.net.
    ------------------------------
    ADDITIONAL INFORMATION:
    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: TCP Provider, error: 0 - The wait operation timed out.) (Microsoft SQL Server, Error: 258)


    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=258&LinkId=20476
    ------------------------------
    The wait operation timed out

    What I've done trying to solve this:

    • Allowed port 1433 (added both inbound and outbound rules).
    • Added Client IP to Azure server.

    Connection Options in SSMS

    • Server Type: Database Engine
    • Server Name: [server_name].database.windows.net
    • Authentication: SQL Server Authentication
    • Login: AdminLogin

    Advanced Options:

    • Connect to Database: <default>
    • Network Protocol: TCP/IP
    • Connection Timeout 60
    • Encrypt Connection: Enabled

    Tracert:


      1    <1 ms    <1 ms    <1 ms  192.168.8.1
      2     9 ms    <1 ms     1 ms  197.97.198.145
      3     3 ms     3 ms     3 ms  197.96.223.53
      4    11 ms    11 ms    10 ms  196.38.87.189
      5    12 ms    11 ms    11 ms  196.38.87.190
      6     *        *        *     Request timed out.
      7    27 ms    36 ms    28 ms  168.209.100.101
      8    26 ms    26 ms    34 ms  196.26.0.130
      9    27 ms    27 ms    27 ms  microsoft.jb1.napafrica.net [196.46.25.133]
     10    27 ms     *       27 ms  ae0-0.jnb02-96cbe-1b.ntwk.msn.net [104.44.228.19]
     11    58 ms    58 ms    43 ms  ae1-0.cpt02-96cbe-1b.ntwk.msn.net [104.44.228.17]
     12    43 ms    43 ms    43 ms  ae0-0.cpt02-96cbe-1a.ntwk.msn.net [104.44.228.14]
     13   193 ms   494 ms   455 ms  ae7-0.lon04-96cbe-1a.ntwk.msn.net [104.44.227.23]
     14   173 ms   174 ms   173 ms  ae11-0.lon04-96cbe-1b.ntwk.msn.net [207.46.44.155]
     15   279 ms   280 ms   280 ms  104.44.5.28
     16   279 ms   279 ms   279 ms  be-1-0.ibr01.nyc04.ntwk.msn.net [104.44.4.50]
     17   534 ms   286 ms   285 ms  be-3-0.ibr01.was02.ntwk.msn.net [104.44.4.34]
     18   283 ms   280 ms   280 ms  be-4-0.ibr03.ch1.ntwk.msn.net [104.44.4.33]
     19   284 ms   279 ms   280 ms  be-2-0.ibr02.ch1.ntwk.msn.net [104.44.4.56]
     20   288 ms   288 ms   287 ms  be-5-0.ibr01.dm2.ntwk.msn.net [104.44.4.76]
     21   286 ms   286 ms   286 ms  ae63-0.dm2-96c-2a.ntwk.msn.net [104.44.8.145]
     22     *        *        *     Request timed out.
     23     *        *        *     Request timed out.
     24     *        *        *     Request timed out.
     25     *        *        *     Request timed out.
     26     *        *        *     Request timed out.
     27     *        *        *     Request timed out.
     28     *        *        *     Request timed out.
     29     *        *        *     Request timed out.
     30     *        *        *     Request timed out.

    Trace complete.

    Wednesday, May 11, 2016 10:03 AM

All replies

  • You say you opened port 1433 and I am guessing you did this inside the VM. You would also need to do this in the Network Security Group associated with the VM as in the screenshot below.  However this is not recommended and I only do it in Demos.  You should be using a VPN to Azure or move the whole service/ solution to Azure and setup networking for Azure VMs to see each other..

      Please mark this an answer if it helps, if not let us know what else you tried.
    Wednesday, May 11, 2016 1:50 PM
  • Good day Zachs

    Let's start from the start again...

    1. First go to the Azure new portal -> SQL Servers -> clicl the server you want to configure -> click "Show Firewall setting"

    > make sure that the "Allow access to Azure services" is set to "ON" (I have a feeling that this is your issue, by the way...)

    > Make sure that your IP is in the list (if you are using dynamic IP then make sure that this is the same IP!)

    > Make sure you have the main admin name and password

    Back to local machine...

    2. Configure your local machine firewall

    * Do you have any additional external firewall ?!?

    DO not open the firewall by port but by application. 

    > Open the Control Panel.

    > Click System and Security

    > Under Windows Firewall click Allow a program or feature through Windows Firewall

    Find the SSMS in the list or use "Allow another app...".

    3. Open SSMS and use these options:

    Is this work for you now?


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Wednesday, May 11, 2016 7:39 PM
    Moderator
  • This sounds like a connectivity problem to your Azure SQL Database. Troubleshooting steps follow:

    1. Is your Azure SQL database online?

    * Run the following command in command prompt:

    ping <servername>.database.windows.net

     * If the name resolution translates your server name to a logical address and returns 'Request timed out', your Azure SQL Server/database is online and listening

      * Otherwise, your Azure SQL Server/database is not available. Consider re-creating it or failing over to a secondary.

    2. Are you able to access the Azure SQL database through the firewall?

    * Add your Client IP address to the Firewall setting of your Azure SQL Database.

    * Alternatively you can login to your database using the SSMS preview release (July 2015 release or later)

    More detailed troubleshooting steps follow after this. Please reply back to this thread if you are able to resolve your problem.

    Wednesday, May 11, 2016 10:52 PM
  • Hi. I tried what you said, but still no luck. "Allow access to Azure services" is set to on. My IP address is in the list. I allowed the app through the firewall. Used the options you have suggested for SSMS.
    Thursday, May 12, 2016 7:04 AM
  • Did the ping test and I can confirm that the SQL Sever is online and listening.
    Thursday, May 12, 2016 7:05 AM
  • Hi Andrew. I did exactly as you suggested, but still no luck.
    Thursday, May 12, 2016 7:23 AM
  • Hi Zach,

      So sorry to hear you're still running into this issue. One thing you could try is bumping the Connection timeout to 60s (the recommendation from Azure is a timeout of at least 30s). Please reply to the thread if this resolves your issue.

    Source: https://support.microsoft.com/en-us/help/10085/troubleshooting-connectivity-issues-with-microsoft-azure-sql-database

    Friday, May 13, 2016 6:43 PM
  • Hi Zachs,

    >> Hi. I tried what you said, but still no luck. "Allow access to Azure services" is set to on. My IP address is in the list. I allowed the app through the firewall. Used the options you have suggested for SSMS.

    We are not done...
    That was the first step, which is the simple configuration :-)
    We confirmed that in first glance seem like the issue is probably not with the Azure SQL Server configuration, but a network is a complex net, and the issue can be in any node from your machine to the Azure SQL Server and back.

    Have you ever connected from your machine to a remote SQL Server? And if so, did you ever connect from your machine to Azure SQL Server?

    If the answer is no, I would recommend you to try this first using a server that you 100% that is configure correctly (other people connect to it OK). If this work well, then we can say that 99.9999'% the issue is with the Azure configuration, otherwise it is probably related to your machine or the network from your machine to the Azure. For example some Internet Service Provider (ISP) might block some ports (I have seen it in the past more then once!).


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Friday, May 13, 2016 8:47 PM
    Moderator
  • Hi Zachs007,

    Here you can find How to: Configure an Azure SQL Database firewall using the Azure Portal :

    https://azure.microsoft.com/en-us/documentation/articles/sql-database-configure-firewall-settings/

    Hope this will help you out.

    Best Regards,

    @Jamesvandenberg

    Saturday, May 14, 2016 7:20 AM