question

junidev-0033 avatar image
0 Votes"
junidev-0033 asked pituach edited

Connect to SQL Azure Database - from Home vs from Office

Hi,

I have an Azure SQL database on my test subscription

I'm able to connect to the DB with SSMS from my Home Network (my IP is whitelisted on server and DB azure firewall)

So, the Azure DB connectivity is doable

In order to be able to connect to the DB from the corporate network, I did the following

Added the company machine IP to the Azure firewall whitelist (server and DB firewalls)

Requested an exception to the company firewall to enable this traficc (indication the company machine static IP and the azure DB dns and ip )

But I'm still not able to connect to the DB from the company machine

What logs can I check in Azure to verify if the connection requests are reching azure and with what IP?

Note: I'm a developer, not an admin. So I might need some detail about the type of logs to look for

Many thanks

azure-sql-databaseazure-firewallazure-ad-audit-logs
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Usually, if it is a firewall issue, you will get a popup asking you to add your IP address to the whitelist when connecting. If you are not getting that popup, then I would double check that your company firewall is allowing you access to the database.

0 Votes 0 ·

Doublecheck to ensure you have the correct IP Address value for the company machine, such as it's Public IP value, added to the Server-level firewall allow list. If you have an explicit deny/allow rule at the database level, ensure that list is modified. Lastly, ensure port 1433 is open in your network. To test this, use the netstat utility to monitor traffic on that machine while using SSMS or specific client, to see there is network activity.


0 Votes 0 ·

1 Answer

pituach avatar image
0 Votes"
pituach answered pituach edited

Good day juni, @junidev-0033


my IP is whitelisted on server and DB azure firewall


This sound strange since Azure Database does not have any firewall in the database level but only in the logical SQL Server level. Please confirm that you are using Azure SQL Database. I will assume in the meantime that you checked twice the same firewall from two different places in the portal for example.


Update! To clarify, In my above is a mistake since Azure Database level firewall is available using Transact-SQL.


Added the company machine IP


1) In most companies the users are working with internal network and not directly to the internet. This means that the machine IP has no meaning since it is internal IP address. Usually all employees have the same Public IP. At least this how it is in small companies, which has only one Internet service provider (ISP) account and a single router connected to the internet.


When you connect to the Azure Database, the IP which the firewall see is not your machine internal IP but the shared public IP.


In order to get your public IP you can use one of the online free services or search Google for: what is my ip


2) please confirm that your operating system firewall does not block you. remember that you have a firewall in the OS, you have a company firewall, and you probably have a firewall in the router as well. All need to be open to port 1434 and not to blog your machine.


Requested an exception to the company firewall to enable this traficc (indication the company machine static IP and the azure DB dns and ip )


1) Make sure the company opened the right port 1434 in the firewall. Usually in companies this port is close since the users only use port 80 and 443 for their browsers


2) Make an attempt with the help of your IT (the person in-charge on the network) to connect SQL Server which is outside your company. For example try to use one at your home. You can install the free SQL Server express edition for the test. This will confirm if they actually opened everything that they need to and configured the network well.


remember that there can be multiple configuration beside to the IP address, like port, load balancing, proxy, and so on.


Please inform us if this solve your needs 🙂


By the way, As you mentioned, this is not an issue related to the Azure Database according to your description, since you can connect to database from your home. This is an issue, which your company IT should solve (if needed wit h the help of forums in the field of networking).





· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

IIsn´t there a way I can see in some Azure log the connection attempts to my database to see if any requests are reaching there and check their IP Address?

0 Votes 0 ·

Hi @junidev-0033 Did this get resolved, or are you needing additional assistance? Please see the following Blog for the best method for troubleshooting issues. Please see: AZURE SQL DB AND LOG ANALYTICS BETTER TOGETHER – PART #1 (link)

The second feedback is to simplify the issue and remove the database level firewall and stick with the server-level firewall functionality. Server level applies to all databases hosted on the logical server but database level firewall setting only apply to the database it is set for.


1 Vote 1 ·