question

giveusanr avatar image
0 Votes"
giveusanr asked AlbertoMorillo commented

Unsolicited connection failures (blocked_by_firewall) against Azure SQL Database Logical Server & [master] database

I am seeing multiple unsolicited connection failures (blocked_by_firewall) against an Azure SQL Database Logical Server & the [master] database in DMV sys.database_connection_stats - 236 in the last 48 hours.
These attempts all originate from 2 x IPs that seem to relate to other Azure customer's Azure SQL Database Logical Server (<individualname>.database.windows.net)
This is the TSQL I am using to spot these failures:
SELECT *
FROM sys.database_connection_stats
WHERE database_name = 'Master'
AND 24 >= DATEDIFF(HOUR, end_time, GETUTCDATE())
ORDER BY end_time;

Why am I seeing these?
Is this something to be concerned about?
Am I seeing these because these are other customers sharing the same underlying SQL infrastructure within the same Azure Region/DataCentre?

azure-sql-database
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.

1 Answer

AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered AlbertoMorillo commented

If you have the "Allow Azure services and resources to access this server" checkbox set off on the Azure SQL firewall settings, that may explain those failed conenctions.

201018-image.png


Those failed connections may be related to Microsoft services trying to gather statistics and management information from your logical server:

Please considere to enable threat detection in order to have probably more information and take action in case of anomalous activities.

You can also try to get more detailed information using Extended Events or by enabling Auditing. See the following query to teh ring buffer to gather more info like RemoteHost:

 ;WITH RingBufferConnectivity as
 (   SELECT
         records.record.value('(/Record/@id)[1]', 'int') AS [RecordID],
         records.record.value('(/Record/ConnectivityTraceRecord/RecordType)[1]', 'varchar(max)') AS [RecordType],
         records.record.value('(/Record/ConnectivityTraceRecord/RecordTime)[1]', 'datetime') AS [RecordTime],
         records.record.value('(/Record/ConnectivityTraceRecord/SniConsumerError)[1]', 'int') AS [Error],
         records.record.value('(/Record/ConnectivityTraceRecord/State)[1]', 'int') AS [State],
         records.record.value('(/Record/ConnectivityTraceRecord/Spid)[1]', 'int') AS [Spid],
         records.record.value('(/Record/ConnectivityTraceRecord/RemoteHost)[1]', 'varchar(max)') AS [RemoteHost],
         records.record.value('(/Record/ConnectivityTraceRecord/RemotePort)[1]', 'varchar(max)') AS [RemotePort],
         records.record.value('(/Record/ConnectivityTraceRecord/LocalHost)[1]', 'varchar(max)') AS [LocalHost]
     FROM
     (   SELECT CAST(record as xml) AS record_data
         FROM sys.dm_os_ring_buffers
         WHERE ring_buffer_type= 'RING_BUFFER_CONNECTIVITY'
     ) TabA
     CROSS APPLY record_data.nodes('//Record') AS records (record)
 )
 SELECT RBC.*, m.text
 FROM RingBufferConnectivity RBC
 LEFT JOIN sys.messages M ON
     RBC.Error = M.message_id AND M.language_id = 1033
 WHERE RBC.RecordType='Error' --Comment Out to see all RecordTypes
 ORDER BY RBC.RecordTime DESC



image.png (4.6 KiB)
· 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.

Hi Alberto,
Thanks for your response:
"Allow Azure services and resources to access this server" is checked on the server.
I too thought it might be MS service-related but when I looked up the 2 IPs on www.abuseipdb.com to see if they were known threats, they aren't, but they do show their logical SQL server names under the "whois" option!
Thanks for the TSQL, I too found this and this excellent bit of code is how I identified the 2 IP's.

My client is working on a budget so I think your suggestion regarding an EE session is my next course of action before going down the auditing route

0 Votes 0 ·

Let's see what extended events brings to the table, what knowledge give us.

0 Votes 0 ·