Trouble connecting to Azure SQL Database from my PC SSMS

Hartono Tanaja 25 Reputation points
2023-03-01T02:08:46.4166667+00:00

Do i need to configure my local machine ssms or firewall to allow me to connect to Azure Sql Database from my PC SSMS. I have configured the azure database firewall, but i still can't connect to it using my pc ssms.

Azure SQL Database
0 comments No comments
{count} votes

Accepted answer
  1. Alberto Morillo 32,986 Reputation points MVP
    2023-03-01T04:16:47.5033333+00:00

    Make sure Windows Firewall or any security software on your PC allows traffic on port TCP 1433. You can use the following command on Command Prompt to configure Windows Firewall.

    netsh firewall set portopening protocol = TCP port = 1433 name = SQLPort mode = ENABLE scope = SUBNET profile = CURRENT
    

    Alternatively you can use PowerShell

    New-NetFirewallRule -DisplayName "SQLServer default instance" -Direction Inbound -LocalPort 1433 -Protocol TCP -Action Allow
    

    Now let's make sure you are using the current IP of your Azure SQL Database server. Let's ping the name of the Azure SQL server. The ping will fail but it should also return the current IP of the SQL Azure Server if DNS resolution is working.

    C:\>ping <myserver>.database.windows.net
    
    Pinging data.sn1-1.database.windows.net [65.55.74.144] with 32 bytes of data:
    Request timed out.
    Request timed out.
    Request timed out.
    Request timed out.
    Ping statistics for 65.55.74.144:
    Packets: Sent = 4, Received = 0, Lost = 4 (100% loss)
    

    Now try to telnet to port 1433 using the IP address returned.

    C:\>telnet 65.55.74.144 1433
    
    Connecting To 65.55.74.144...
    

    If Telnet is successful, the window will change to a completely blank screen. Try connecting with that IP using SSMS again. If telnet is not successful, try tracert to identify at what step it fails to reach the Azure SQL Database server.

    C:\>tracert 65.55.74.144
    
    Tracing route to 65.55.74.144 over a maximum of 30 hops
    1 2 ms 1 ms 1 ms 192.168.0.1
    2 6 ms 5 ms 5 ms 10.33.34.50
    3 5 ms 4 ms 5 ms 4-1-753.DLLSTX-LCR-07.verizon-gni.net [130.81.107.26]
    4 8 ms 5 ms 5 ms so-6-2-0-0.DFW01-BB-RTR1.verizon-gni.net [130.81.28.208]
    5 44 ms 43 ms 44 ms so-11-0-0-0.LCC1-RES-BB-RTR1-RE1.verizon-gni.net [130.81.17.40]
    6 49 ms 44 ms 44 ms so-6-0-0-0.ASH-PEER-RTR1-re1.verizon-gni.net [130.81.10.90]
    
    2 people found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Rahul Randive 8,826 Reputation points Microsoft Employee
    2023-03-01T03:44:23.08+00:00

    Thanks for your question.

    Please find below document about “Connect to an Azure SQL Database”

    https://learn.microsoft.com/en-us/sql/ssms/quickstarts/ssms-connect-query-azure-sql?view=sql-server-ver16#connect-to-an-azure-sql-database-or-azure-sql-managed-instance

    If you haven't set up your firewall settings, a prompt appears to configure the firewall. Once you sign in, fill in your Azure account login information and continue to set the firewall rule. Then select OK. This prompt is a one time action. Once you configure the firewall, the firewall prompt shouldn't appear.

    Connection attempts from the internet and Azure must pass through the firewall before they reach your server or database

    Please refer below document about “How the firewall works”

    https://learn.microsoft.com/en-us/azure/azure-sql/database/firewall-configure?view=azuresql#how-the-firewall-works

    Let us know if this helps!

    If you are still getting an error, please share error details/screenshot for better understanding of an issue.

    Looking forward to hear from you.

    Thank you!

    0 comments No comments