Trouble connecting to Azure SQL Database from my PC SSMS

Question

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.

Answer 1

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]

Answer 2

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!