sp_send_dbmail inside a logon trigger.

Question

hello,

im using a simple logon trigger to check a SQL Login id, if its not a windows id and if its coming from particular workstation then raiserror and send email. Trigger is getting fired however email is not sent. This is the script. I have also tried logging this data by inserting hostname and login name in the table however sometimes it inserts the data sometimes it doesnt. Can someone tell me what am i doing wrong? Thank you

CREATE TRIGGER [RestrictAccessPerHostname] 
ON ALL SERVER --with execute as 'email_admin'
FOR LOGON
AS
BEGIN 
declare @srv varchar(100)
select @srv='SQL Login attempt from instance :' + @@servername

	IF
	((
	left(host_name(),5) in ('xyz')
	)
	and 
	 (SELECT name FROM sys.server_principals  WHERE TYPE = 'S' and name = ORIGINAL_LOGIN()) is not null)
	 begin
	 RAISERROR('Cant use SQL login from workstation',16,1);
	 EXEC msdb.dbo.sp_send_dbmail  
     @profile_name = 'email_profile',  
     @recipients = 'xyz@xyz.com',  
     @body = 'test ',  
     @subject =   'teststee' ; 
	 Rollback;
	 end
END

Answer

It may work if you put the rollback before sending the mail. It certainly will not work if you roll back after queueing the mail, since the mail is also rolled back.

I will say I get a little nervous when I see this. Logon triggers is not a place where you fool around with whims. It could bite you quite hard...

Answer

Hi @Heisenberg,

If the login trigger containing sp_send_dbmail is not properly designed, it could potentially trigger an email flood, where a large number of emails are sent in quick succession, potentially overwhelming email servers or recipients.

There are a few things that could be causing the issue:

Check that the Database Mail feature is enabled on your SQL Server instance. You can do this by running the following command:

EXEC sp_configure 'show advanced options', 1

RECONFIGURE

EXEC sp_configure 'Database Mail XPs', 1

RECONFIGURE

You can test this by sending a test email using sp_send_dbmail outside of the trigger.

Here's an updated version of your trigger with some modifications to address the above issues:

CREATE TRIGGER [RestrictAccessPerHostname]
ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
IF (
LEFT(HOST_NAME(), 5) IN ('xyz')
AND ORIGINAL_LOGIN() IS NOT NULL
AND SUSER_SNAME() <> 'sa' -- exclude the 'sa' account from the check
)
BEGIN
DECLARE @msg NVARCHAR(1000) = N'Cannot use SQL login from workstation ' + HOST_NAME() + N'.'
RAISERROR(@msg, 16, 1);

	INSERT INTO [dbo].[LogonAttempts] (Hostname, LoginName, EventDate) 
	VALUES (HOST_NAME(), ORIGINAL_LOGIN(), GETDATE());

	EXEC msdb.dbo.sp_send_dbmail  
	@profile_name = 'email_profile',  
	@recipients = 'xyz@xyz.com',  
	@body = @msg,  
	@subject = N'SQL Login Attempt from Restricted Workstation';
	
	ROLLBACK;
END

Here is a possible way for you to debug the issue:

Firstly, you need create a table [dbo].[LogonAttempts].

I've added a check to exclude the 'sa' account from the workstation check, since the 'sa' account can bypass most security measures in SQL Server. I've also added an INSERT statement to log the event to a table, which can be helpful in debugging issues with the trigger.

Best regards,

Seeya

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

sp_send_dbmail inside a logon trigger.

2 answers