Solved

SQL Server Logon Triggers:  What Permissions Are Needed

Posted on 2013-11-11
10
1,786 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

896 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

15 Experts available now in Live!

Get 1:1 Help Now