Solved

SQL Server Logon Triggers:  What Permissions Are Needed

Posted on 2013-11-11
10
1,750 Views
Last Modified: 2014-01-03
I am creating a SQL Server Logon Trigger.  Once it was enabled, I could not logon to the system:

Logon failed for login 'xyz' due to trigger execution.
Changed database context to 'Master'.
Changed language setting to us_English (Microsoft SQL Server, Error: 17892)

I disabled the trigger.

I believe my issue to be related to permission on database objects.  The trigger reads from a table, and inserts some information in another table.  These tables are in database ABC, not the master database.

What objects do I need to give permission to?  The tables?  The database?  And who gets the permission?  Public?  Guest?

Thanks In Advance,

- Michael
0
Comment
Question by:mjs082969
  • 5
  • 5
10 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39640137
Would have to see the trigger code to offer any hard advice -- it's clearly impossible to know otherwise what the trigger's actually doing.
0
 

Author Comment

by:mjs082969
ID: 39641696
CREATE TRIGGER XYZ_Trigger ON ALL SERVER
    FOR LOGON
AS

BEGIN

      DECLARE @IPAddress NVARCHAR(50) ;

      -- Set @IP to the IP attempting access
      SET @IPAddress = EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(50)') ;
                                                                                   
      -- Is IP NOT part of the 198.2.0 Subnet?
      IF @IPAddress NOT LIKE '198.2.0.%'
            BEGIN
                  -- Compare program name to 'Our Application%'  
                  IF PROGRAM_NAME() NOT LIKE Our Application%'                                      
                        BEGIN

                              -- compare the IP to the addresses in the table
                              IF NOT EXISTS ( SELECT IP FROM XYZ.dbo.ValidIPAddress WHERE IP = @IPAddress )
                                    BEGIN
                                          INSERT INTO XYZ.dbo.Login_Details (PostDateTime, IP_Address, Account, ProgName, Event_Detail, TSQL_Detail )
                                                VALUES (GETDATE(), @IPAddress, USER_NAME(), PROGRAM_NAME(), EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'))
                                          SELECT  @IPAddress
                                          ROLLBACK --Undo login process
                                    END
                              ELSE
                                    BEGIN
                                          INSERT INTO XYZ.dbo.Login_Details (PostDateTime, IP_Address, Account, ProgName, Event_Detail, TSQL_Detail )
                                                VALUES (GETDATE(), @IPAddress, USER_NAME(), PROGRAM_NAME(), EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'))
                                    END
                        END
            END
      ELSE
            BEGIN
                  INSERT INTO XYZ.dbo.Login_Details (PostDateTime, IP_Address, Account, ProgName, Event_Detail, TSQL_Detail )
                        VALUES (GETDATE(), @IPAddress, USER_NAME(), PROGRAM_NAME(), EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'))
            END
END
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39641823
Thanks!

In this case, easiest would likely be to add the "Guest" user to the XYZ database, then:
USE XYZ
GRANT INSERT, SELECT ON dbo.Login_Details TO PUBLIC

Hopefully that will do it.  If not, you might have to add the specific AD group(s) to the XYZ database.
0
 

Author Comment

by:mjs082969
ID: 39668909
I tried the GRANT statement but it didn't work.

What might be preventing it from working?

This trigger does do a lookup succesfully.  The Trigger works until an INSERT statement is added.

Thanks Again

- Michael
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39669230
>> What might be preventing it from working? <<

What error did it get?  What does "didn't work" mean exactly?

If you're on a case-sensitive server, you might need to check the case:

--      ????
USE XYZ
--                                                          ?????????????
GRANT INSERT, SELECT ON dbo.Login_Details TO public
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:mjs082969
ID: 39669327
I am on a case-sensitive server, but that wasn't the cause of the issue... I had matched case.


I did read someplace that it was not advisable to have logon triggers access databases other than master and msdb.  Is this general concensus?
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39669414
So, what is the error then?


>>  it was not advisable to have logon triggers access databases other than master and msdb.  Is this general concensus? <<

Yes, to insure there's no logon trigger error if for some reason the referenced db does not exist when you start up the instance (obviously master, model, msdb and tempdb will always exist, so any/all of them can safely be referenced).
0
 

Author Comment

by:mjs082969
ID: 39695784
The error is the same as identified inititally:

Logon failed for login 'xyz' due to trigger execution.
Changed database context to 'Master'.
Changed language setting to us_English (Microsoft SQL Server, Error: 17892)
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 39695948
Often that error means a referenced db does not exist, or that login has no authority to that db.

Probably want to explicitly grant connect just in case:

USE XYZ
GRANT CONNECT ON dbo.Login_Details TO public

Also, check whether the dbo.Login_Details table has a trigger that fires on INSERT that attempts to access/modify any other tables.
0
 

Author Comment

by:mjs082969
ID: 39754453
I did attempt to explicitly grant, but this did not work either.

The solution I implemented was not wht I had hoped it would be.  I was hoping to log information into a table from the login trigger.  For now, I was able to accomplish what needed to be done.  Thanks for all of your assistance; I do hope to attempt to implement this again in the near future.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now