Thanks for the guidance Sergiy, I looked into what you suggested and implemented most of your ideas.

CREATE TRIGGER [<the trigger>]

ON ALL SERVER

WITH

EXECUTE AS 'sa'

FOR LOGON

AS

BEGIN

IF ORIGINAL_LOGIN() = '<the restricted user>'

AND (APP_NAME() NOT IN ('.Net SqlClient Data Provider' ,'EntityFramework')

OR

NOT EXISTS (SELECT 1

FROM <table of allowed host names> AS ah

WHERE ah.HostName = HOST_NAME()))

BEGIN

ROLLBACK;

INSERT INTO <denied connections auditing table>

(

[User]

,HostName

,LoginTime

,SessionId

,AppName

)

SELECT ORIGINAL_LOGIN()

,HOST_NAME()

,GETDATE()

,@@SPID

,APP_NAME()

END

END;

It appears to have improved matters, but the problems still exists. When CPU is high, innocent connections get blocked, seemingly by the trigger failing to complete (again, there's no entry in the audit table, unlike when I try to log in in violation of the rules).

There was a batch of blocked connections today where the CPU was fine, but there was a spike in disk queue length and disk transfers/sec... I'm more and more sure this is a resource contention issue, but I don't know what I can do about it.

Is there a timeout value for the trigger itself I can adjust? I can't find any references to such a thing, but some SQL features are a bit obscure...