none
A Logon Trigger to Deny the use of SQL Server Native Tools for a Functional Account(non interactive) account.

    General discussion

  • The trigger should deny login attempts for a specified set of accounts : (eg: 'app1_xxxx’,'app_xxxx','xyz')

    a. For any request that is not coming from host list ( eg: in this case: 'HostServer1',  'HostServer2')

                b. For the use of any known interactive tools remotely or on the host machine.

                            (

                'Microsoft SQL Server Management Studio'

                ,'SQLCMD'

                ,'OSQL-32'

                'Toad'

                ,'SQL Query Analyzer'

                )

    Thursday, April 08, 2010 4:26 PM

All replies

  • --I had been give the above task recently to provide a solution in just one day of time in hand. Quickest solution that I could think of then is as below:

     

    use master

    GO

    CREATE TRIGGER deny_FID_connection_request

    ON ALL SERVER WITH EXECUTE AS 'sa'

    FOR LOGON

    AS

    BEGIN

    -- Purpose - Deny connection attempt of any SQL Non Interactive Functional Account

     

    --This Trigger will Deny a Login attampt if

    -- -- -- -- -- -- a. Host Name is other than valid hosts

    -- -- -- -- -- -- b. If any known SQL Native tool is being used

    -- To add a Login Names, Host Names(twice) or SQL Interactive Tools 

    -- -- script out the trigger and modify the

    -- -- -- -- values below the commented sections

    -- -- -- -- values below the commented sections

    -- -- -- -- and recreate the trigger in **MASTER** database

    IF (ORIGINAL_LOGIN() in 

    -- -- -- -- -- -- -- -- -- --  List of Logins -- -- -- -- -- -- -- -- -- -- --

                      (

                      'app1_xxxx'

                      ,'app_xxxx'

                      ,'xyz'

                      )

                      AND

                      (

                            EXISTS

                                  (SELECT * FROM sys.dm_exec_sessions

                                  where session_id=@@spid

    -- -- -- -- -- -- -- -- -- -- -- Host Names -- -- -- -- -- -- -- -- -- -- -- --

                                        AND host_name in ('HostServer1', 'HostServer2')

                                        AND is_user_process = 1

     

    -- -- -- -- -- -- -- -- -- -- Interactive Tools -- -- -- -- -- -- -- -- -- -- --

                                        AND program_name in

                                              (

                                              'Microsoft SQL Server'

                                              ,'Microsoft SQL Server Management Studio'

                                              ,'Microsoft SQL Server Management Studio - Query'

                                              ,'SQLCMD'

                                              ,'OSQL-32'

                                              ,'Toad'

                                              ,'SQL Query Analyzer'

                                              )

                                  )

                            OR EXISTS

                                  (SELECT * FROM sys.dm_exec_sessions 

                                  where session_id=@@spid

    -- -- -- -- -- -- -- -- -- -- -- Host Names -- -- -- -- -- -- -- -- -- -- -- --

                                        AND host_name not in ('fdcgtsebawd01u')

                                        AND is_user_process = 1

                                  )

                      )    

          )

          BEGIN

                PRINT 'Intercative or Invalid Login Attempt of ' + ORIGINAL_LOGIN()

              ROLLBACK;

          END

    END;

     

    Thursday, April 08, 2010 4:34 PM