question

raman_singh avatar image
raman_singh asked

Send notification via email for database level auditing in sql server

I want to database level auditing and send notification via email. I am able to audit the logs for database level but not able to send notification over the email. Can you guys help me?
sqltsql
14 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

anthony.green avatar image anthony.green commented ·
Have you setup database mail and using sp_send_dbmail to fire a mail when an event happens? what have you don so far, what is not working, what errors do you get?
0 Likes 0 ·
Shawn_Melton avatar image Shawn_Melton commented ·
Please provide code or scripts you have tried to use and what error you are getting.
0 Likes 0 ·
anthony.green avatar image anthony.green commented ·
Looking at your post which has now been deleted, your using Gmail, but didn't see any authentication details. Gmail needs to you authenticate with DB Mail. Also ensure that you can route to external sources over port 25, your organization may limit to use of SMTP externally as a security method. Also check your antivius software for things that block "mass mail worms" McAfee has that feature for sure, which is a right pain in the ass. If they are all showing as failed in sysmail_allitems, then your mail configuration is incorrect or something is blocking it.
0 Likes 0 ·
raman_singh avatar image raman_singh commented ·
I am using port 587 for Gmail in account. Requirement is : Create Database level or Server level audit. If i make db level audit, then if i make any change in any sp, any table, any trigger or function, then Audit will generate logs and i am storing these logs into sqlAudit file which I made explicitly. Now I want if any new entry comes into Audit Logs file so i should be notified via email. What I did so far : I am able to generate the logs into Audit logs file and i can retrieve those logs as well using "msdb.sys.fn_get_audit_file" but i am not able to get notification for these logs. For Mail Part : I configure DBMail and alert all the things which i need for it. I am using exec msdb.dbo.sp_send_dbmail @profile_name='ProfileName', @recipients = 'mail id', @body=N'exec msdb.sys.fn_get_audit_file('file location',default,default) @subject = 'Notification for Audit logs' When i am doing this and checking using command "msdb.dbo.sysmail_allitems" then it is showing as failed
0 Likes 0 ·
raman_singh avatar image raman_singh commented ·
script i writing to get notification declare @mailQuery nvarchar(max) select @mailQuery= event_time from msdb.sys.fn_get_audit_file('E:\b\backup\Raman\SSIS\*.sqlaudit',default,default) exec msdb.dbo.sp_send_dbmail @profile_name='NewProfile', @ recipients='sramandeep486@gmail.com', @body=@mailQuery, @subject = 'Notification for Audit logs' And in sysmail_allitem, all queued mail are showing as failed
0 Likes 0 ·
Show more comments

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.