Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 http://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?

share|improve this question

migrated from stackoverflow.com Apr 30 '13 at 9:10

This question came from our site for professional and enthusiast programmers.

1  
use roles and windows groups and don't allow users ANY explicit access –  Mitch Wheat Apr 30 '13 at 2:55
    
App names are just a parameter on the connection string for certain connections. They're easily faked. –  ConcernedOfTunbridgeWells Apr 30 '13 at 9:16
2  
You're trying to block all access except Excel? I'd be doing the opposite. :-) –  Aaron Bertrand Apr 30 '13 at 13:31
    
Thanks everyone for your comments. Yes, the name can be faked but in this case, at least they need to guess your valid app names before faking them. In the original idea, they just need to hide (or simply not generated name) or change their name to anything and pass the trigger. Aaron, no, I would like to block any Excel, Access ... and restrict the access to our system application only. –  Sam Mazed Apr 30 '13 at 14:05
1  
yoy run the risk of locking yourself out with a whitelist only approach. I would do some trials runs of how to recover from a bad login trigger so you are familiar with the process. –  StrayCatDBA May 1 '13 at 2:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.