We have a DDL trigger on our server preventing access from Microsoft Office, Excel or Access applications for unauthorised users. Users and their workstation details are in a 'white list', which gives them access if they've been approved. Every one else gets a 'login failed due to trigger execution' message.
I've just found out that when trying to create a DSN connection using the ODBC manager (SQL Server Native Client 10), it also fires the trigger. Is this a security feature to stop Office users using DSN connections as a backdoor, or is the ODBC manager seen as part of the office suite? Having said that, I've also tried using the ODBC manager from a machine without MS Office installed, and it still failed.
Login trigger code:
/****** Object: DdlTrigger [Logon_Trigger_Monitor_Excel] Script Date: 11/02/2011 09:01:39 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TRIGGER [Logon_Trigger_Monitor_Excel]
ON ALL SERVER WITH EXECUTE AS 'xxxx\xxxx'
IF APP_NAME() LIKE '%MICROSOFT OFFICE%' OR APP_NAME() LIKE '%EXCEL%' OR APP_NAME() LIKE '%ACCESS%'
IF UPPER(ORIGINAL_LOGIN()) LIKE 'HERMES\%'
IF (SELECT COUNT(*)
WHERE UPPER(loginwho) = UPPER(ORIGINAL_LOGIN())
AND UPPER(loginfrom) = UPPER(HOST_NAME())) = 0 ROLLBACK
SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER OFF
ENABLE TRIGGER [Logon_Trigger_Monitor_Excel] ON ALL SERVER