Does sql_login have rights on the blacklist table?

What you can do to debug is something like this (untested):

CREATE TRIGGER connection_limit_trigger

ON ALL SERVER WITH EXECUTE AS 'sql_login'

FOR LOGON

AS

BEGIN

BEGIN TRY

IF ORIGINAL_LOGIN()= 'sql_login' AND host_name() in (select hostname from admindb..sqllogin_hostname_blacklist)

ROLLBACK

END TRY

BEGIN CATCH

PRINT Error_message() -- goes into the SQL error log so that you can see what is causing the rollback

ROLLBACK TRANSACTION

END CATCH

END

You may want to add a print host_name() before you do the rollback, to ensure that you have a log of unauthorised access attempts. The SOX auditors I worked with wanted to see that.

I would also suggest, if possible, rather have a whitelist of hostnames that are allowed to use that username, so that some bright spark doesn't try using a virtual machine or similar to get around the limitations.

Regarding the devs, perhaps have a word with management about creating an IT policy (if one does not exist) and including in that that accessing system using system logins (like your sql_login) is an offence and will merit a disciplinary hearing (or whatever equivalent you have there). Trust me (been there, done that), make an example of one person (even if it's just a public warning) and the number of people breaking the rules will drop rapidly. 😀

How are they figuring out the encrypted passwords?