I am trying to stop our valid database users accessing our SQL Server remotely via applications other than our system application (e.g., MS Excel). These accesses need to be prevented because they cause problem with our access audit.
Among not very reliable solutions, I am interested in trying the Logon Trigger method.
Remus Rusanu provided a sample Trigger here https://stackoverflow.com/a/2867794/1957134
CREATE TRIGGER application_limit_trigger
ON ALL SERVER WITH EXECUTE AS '...'
FOR LOGON
AS
BEGIN
IF EXISTS (SELECT *
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID
AND program_name IN (N'Bad Program', N'Worse Program', N'Unmentionable')
ROLLBACK;
END;
The major issue with this method is unpredictable remote application Names.
My question: What may happen if instead of denying the access from specific application names, we include the acceptable application names and deny any other application name? Can this approach cause unexpected login issues even if we have a precise allowed application list?
Yes, someone may fake our valid app names and get connected, if he guesses the names. But isn't this exclusive method more reliable?
