1

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?

Community
  • 1
Sam Mazed
  • 11
  • 1
  • 1
    use roles and windows groups and don't allow users ANY explicit access – Mitch Wheat Apr 30 '13 at 02:55
  • App names are just a parameter on the connection string for certain connections. They're easily faked. – ConcernedOfTunbridgeWells Apr 30 '13 at 09: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
  • IMHO, Logon Trigger will suit your need. You can refer to my answer http://dba.stackexchange.com/questions/40155/prevent-users-from-using-power-pivot-excel-connections-to-a-database/40157#40157 as well as a detailed script is http://dba.stackexchange.com/questions/37379/limit-connection-rights-on-sql-server/37382#37382 – Kin Shah Apr 30 '13 at 14:06
  • Thank you Kin for the helpful info. I like the script. My question is whether we can use a "Whitelist" instead of the script's "Blacklist". Is it possible to predict all allowed app names as a Whitelist and reject any other app name? Does this idea put the server at risk of unpredicted lockouts over time? – Sam Mazed Apr 30 '13 at 14:39
  • 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 01 '13 at 02:11

0 Answers0