Managed SQL Instance: Entra ID and SQL authentication

If the different endpoints would also appear as in different endpoints inside the managed instance, as seen in sys.endpoints, it would certainly be possible. In that case you could revoke the CONNECT permission for public on the public endpoint, and then grant it a server role which comprises all Entra logins.

Alas, from what I can see on my managed instance, this is not the case, but this thing with vnets goes a little over my head, so I may be missing something.

Hi @RobBul,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

@Erland Sommarskog unfortunately, there's not a separate endpoint in sys.endpoints that would allow for differentiating connections from the public internet vs. VNET local connections.

I'm looking into a Logon database trigger that would check against sys.dm_exec_connections to verify if the incoming connection is from the internet AND is SQL authenticated (client_net_address and auth_scheme values for the session).

I'd rather prefer to be able to set this up at the Azure control plane level (perhaps a feature idea?).

This would be the trigger - not sure if that's the correct or preferable way to do it - any comments appreciated:

ALTER TRIGGER public_sql_logon_trigger ON ALL SERVER
-- DENY SQL AUTHENTICATED LOGINS FROM PUBLIC IP ADDRESSES
FOR LOGON AS BEGIN
    DECLARE @client_net_address NVARCHAR(48);
    DECLARE @auth_scheme NVARCHAR(256);
    --DECLARE @login_name NVARCHAR(256);
    --DECLARE @session_id INT;
    SELECT TOP 1 
        @client_net_address = c.client_net_address,
        @auth_scheme = c.auth_scheme
    --    @login_name = s.login_name,
    --    @session_id = s.session_id
    FROM sys.dm_exec_connections AS c
    --LEFT JOIN sys.dm_exec_sessions AS s ON s.session_id = c.session_id
    WHERE c.session_id = @@SPID;

    IF (@client_net_address NOT LIKE '10.%' AND
        @client_net_address NOT LIKE '172.1[6-9].%' AND
        @client_net_address NOT LIKE '172.2[0-9].%' AND
        @client_net_address NOT LIKE '172.3[0-1].%' AND
        @client_net_address NOT LIKE '192.168.%' AND
        @client_net_address NOT LIKE '127.%' AND -- Exclude loopback
        @client_net_address NOT LIKE '0.%' AND -- Exclude 0.0.0.0/8
        @client_net_address NOT LIKE '169.254.%' AND -- Exclude APIPA (Automatic Private IP Addressing)
        @auth_scheme = 'SQL') --SQL auth
    BEGIN
        ROLLBACK;
    END
END;

Maybe that logon trigger will work, but I agree it does not look pretty. And it's not fun if you lock yourself out.

I guess that the problem with having this rule in the outer layers, is that these layers are only concerned from where the traffic is going and from where it is coming, but they have no knowledge of what's in those network passages.

It seems a bit of a feature gap, indeed.