I've created the following simple logon trigger on my SQL Server 2005:
CREATE TRIGGER LOGON_RESTRICTION ON ALL SERVER FOR LOGON AS BEGIN PRINT 'Hello World' END GO
Which seems to be working fine in most cases except whenever the DTC is involved. Error messages I'm getting are:
The server failed to resume the transaction. Desc:4d00000273.
Process ID 77 attempted to unlock a resource it does not own: DATABASE: 36 . Retry the transaction, because this error may be caused by a timing condition. If the problem persists, contact the database administrator. An error occurred during the changing of transaction context. This is usually caused by low memory in the system. Try to free up more memory.
I've come across this article which goes some way to explaining the possible cause but the solution at the end isn't quite what I'm after.
Ultimately I'd like to use the trigger to restrict access from certain IP addresses to certain logins.