Lesson Learned #20: What happen with my connection inside Azure when SQL Auditing is enabled?

  • Article

Hello Everyone,

Here we are with another lesson learned. At this time, I would like to show you what happen with my connection to my Azure SQL Database inside Azure when I have SQL Auditing enabled.

The first thing that we need to read is the documentation placed here: /en-us/azure/sql-database/sql-database-develop-direct-route-ports-adonet-v12, when we have all information about the connectivity methods inside and outside of Azure.

We are going to take a network trace to identify what is the connectivity path when SQL Auditing is enabled or not.

We will execute these two below commands to take these network traces. There is not needed to instal any application in our Virtual Machine on Azure:

  • netsh trace start capture=yes packettruncatebytes=512 tracefile=%temp%\%computername%_nettrace.etl maxsize=400 filemode=circular overwrite=yes report=no
  • Testing the connection to Azure SQL Database
  • netsh trace stop

Step 1: SQL Auditing is disabled either Azure SQL Database/Server.

  • As you could see the ephemeral port will connect to the port of the ring that our Azure SQL Database is running. If you need more information you could download more information from this URL

insideazureconn

Step 2: SQL Auditing is enabled either Azure SQL Database/Server saving the details as blob we will have the different communication path.

insizeazureconnsqlaudblob

As we could see the connection will go trough the Proxy of the datacenter.

Step 3: SQL Auditing is enabled either Azure SQL Database/Server saving the details as table we will have a different communication path.

insideazureconnsqlaudtable

As we could see the connection will go trough the Proxy of the SQL Auditing (datasec-x)

So, depending on the method of the SQL Auditing that you choose you need to be aware of the outgoing port and DNS to open.

Enjoy!