SQL Server 'SA' account  successful  logon Alert

tschary
tschary used Ask the Experts™
on
Hi Experts,

I want to capture 'SA' account successful logon alone , i need to send an email alert to security team.When anyone logged in using 'SA' account. We have SQL Server audit feature to achieve this . it captures all successful logins in security log. But i need only 'SA' user account successful audit information. Please provide your tips.

Thanks in Advance,

Sreenivasa
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Sreenivasa, in SQL Server you have Logon Triggers that fires every time a logon occurs. If you want to send an email only a 'sa' Login fires the trigger then you can do something like:
CREATE TRIGGER trg_login
ON ALL SERVER 
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'sa' 
    EXEC msdb.dbo.sp_send_dbmail @recipients = 'SecurityTeam@company.com', @body = 'ALERT: sa login detected.', @subject = 'SA Login' ;
END;

Open in new window

Author

Commented:
Hi Vitor,
It is working fine. I have modified the below email send line to inserting data into a temp table for testing purpose. It is working fine. But for single 'sa' login (using SQL Server Authentication mode) it inserting three records in the table instead one single record. Please suggest me on this.

 --EXEC msdb.dbo.sp_send_dbmail @recipients = 'SecurityTeam@company.com', @body = 'ALERT: sa login detected.', @subject = 'SA Login' ;
    INSERT INTO [tempdb].[dbo].[TestSA]([Value])VALUES ('tsc')
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Strange.
How are you logging in?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Hi vitor,
I have tried with email profile account as well, it is sending 3-5 mails when i login through SA account. I am logging via SQL Server authentication mode . User name: sa, passowrd: xxxxxxx.Please find my code and review.

CREATE TRIGGER [testSA]
ON ALL SERVER
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'sa'
   
   EXEC msdb.dbo.sp_send_dbmail @profile_name='testProfile',
@recipients='xyz@abc.com', @subject='Test SA message',
@body='SA log on occured in the server '
     
END;
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Sorry, I wasn't clear. What I meant is if you're logging in through an application or just SSMS?

I made some tests and looks like the logon trigger is fired many time when it SSMS. You just need to open some folders under the instance name and you get the trigger fired.

Author

Commented:
Hi Vitor,

Yes. I am trying through SSMS and not from an application. I can see trigger under server objects folder.
IT Engineer
Distinguished Expert 2017
Commented:
Unfortunaly is how SSMS works. Every refresh fires a login trigger. I think that you pretend to register all 'sa' logins, even from SSMS, right?
If not then you can always add a filter in the trigger:
CREATE TRIGGER [testSA]
 ON ALL SERVER 
 FOR LOGON
 AS
 BEGIN
 IF ORIGINAL_LOGIN()= 'sa' AND APP_NAME() NOT LIKE 'Microsoft SQL Server Management Studio%'
     INSERT INTO [tempdb].[dbo].[TestSA]([Value])VALUES ('tsc') '
END

Open in new window

Other validation that you can do is to check if the record exists. If so, don't insert it anymore.

Author

Commented:
Thanks for the solution

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial