Send email notifications when someone or Application tries to login using the 'sa' Login account whether Failed or Success?

Question

Hello, can anyone provide any methods to send email notifications when someone or Application tries to login using the 'sa' Login account whether Failed or Success?

My first guess would be some SQL Server level Trigger (scripts) or SQL Audit or something else.

(Including in the email would be further helpful if IP Address or Host name along with Date-Time stamp but not necessary since we have enabled SQL Login attempts for both Windows Authentication & SQL Authentication which should provide this)

Thanks in advance.

Answer 1

To send email notifications when there are login attempts using the 'sa' login account in SQL Server, you can use SQL Server triggers or SQL Server Audit. Here are the steps for both methods:

SQL Server Trigger:

• Create a new table to store the login attempt information, including the IP address, host name, date, time, and whether the attempt was successful or failed.
• Create a trigger on the SQL Server's sys.server_principals system view to capture login events for the 'sa' account.
• In the trigger, insert the relevant login attempt information into the table.
• Use the SQL Server Database Mail feature to send an email notification whenever a new record is inserted into the table. You can configure the mail profile, recipients, subject, and body of the email.

SQL Server Audit:

• Enable SQL Server Audit by creating a new server audit specification.
• Configure the audit specification to capture login events for the 'sa' account and include the desired information such as IP address, host name, date, and time.
• Create a server audit to store the audit logs.
• Use the SQL Server Database Mail feature to send an email notification whenever a login event for the 'sa' account is captured in the audit logs. You can query the audit logs periodically and send an email based on the results.

Both methods have their pros and cons. SQL Server triggers provide more flexibility and customization options, but they might have a performance impact on the server. SQL Server Audit is a built-in feature and provides a centralized auditing mechanism but requires careful configuration.

You can choose the method that best suits your requirements and environment. Ensure that you have the necessary permissions and consider any security and performance implications before implementing these solutions.

Answer 2

Hi @techresearch7777777,

You may try with below:

• A SQL Server Agent alert, using the WMI event AUDIT_LOGIN_FAILED
• Event Notifications, also using AUDIT_LOGIN_FAILED
• Manually consuming the error log

Check it out here: Get Alerts for Specific SQL Server Login Failed Events

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 3

Create a trigger on the SQL Server's sys.server_principals system view to capture login events for the 'sa' account.

You cannot create triggers system views. You can capture the same information with logon triggers, though.

You can only capture successful logins with logon triggers. For login failures, you need to have a job that reviews the SQL Server errorlog or other audit source. And in that case, you may as well capture the successful logins that way as well. And then you can arrange it so that you get a mail like once an hours with all logins in that period. I don't think you want a mail for every login.