Get Alert Whenever There is MS SQL Server Access
-
Anyone know the best way to do that? Email is easiest, but not a requirement. We just need an alert anytime that someone accesses MS SQL Server. Meaning, they connect to the service. This is connecting to the database, not the OS. It's a legal requirement, so doesn't need to make sense.
-
No idea. For fun, I asked ChatGPT and this was the response --
Yes, you can set up a notification system to receive alerts whenever a user connects to Microsoft SQL Server. One common approach is to use SQL Server Audit, which allows you to capture and log specific events in the SQL Server instance. Here's a step-by-step guide on how to set up SQL Server Audit to receive notifications for user connections: 1. Connect to your SQL Server instance using a tool like SQL Server Management Studio (SSMS). 2. Expand the "Security" folder in the Object Explorer. 3. Right-click on the "Audits" folder and select "New Audit...". 4. Provide a name for the audit, such as "User Connections Audit," and click "OK." 5. In the "Audit Destination" section, choose whether to save the audit logs to a file or the Windows Security event log. Select the appropriate option and configure the settings as desired. 6. Under the "Events" tab, click on the "Add..." button. 7. In the "Audit Action Type" window, expand the "Security" category, and select the "Audit Login" action. Click "OK" to add it to the audit. 8. Click "OK" to save the audit settings. 9. Right-click on the "Server Audit Specifications" folder and select "New Server Audit Specification...". 10. Provide a name for the audit specification, such as "User Connections Audit Specification," and select the audit you created in the previous steps. 11. Under the "Audit Action Type" window, select the "Audit Login" action. 12. In the "Object Types" section, select the "Server" object type. 13. In the "Principal Name" section, select the "Public" option to capture all logins. 14. Click "OK" to save the audit specification. Now, whenever a user connects to the SQL Server, the event will be captured by the audit and logged according to your configured settings. Depending on the destination you selected, you can set up email notifications or other alerts to be triggered whenever new audit logs are generated. Please note that setting up and managing SQL Server Audit may require administrative privileges and should be done carefully to ensure proper security and compliance practices. -
@scottalanmiller said in Get Alert Whenever There is MS SQL Server Access:
Email is easiest
How in the fuck is that "easiest?" It would be a train wreck of hundreds of emails for a barely used system, let alone a busy one.
Log to audit file and ship the log.
-
@Danp hahaha, that's awesome, thanks!
-
@JaredBusch said in Get Alert Whenever There is MS SQL Server Access:
@scottalanmiller said in Get Alert Whenever There is MS SQL Server Access:
Email is easiest
How in the fuck is that "easiest?" It would be a train wreck of hundreds of emails for a barely used system, let alone a busy one.
Log to audit file and ship the log.
It's a production database so there should be an email when the application connects and absolutely no user should ever, ever, ever be able to log in unless it's an admin doing an emergency backup and/or restore (likely alerts would be off during a restore.) There's no user ever authorized to just connect. So if there is more than one or two emails a month, that would be super interesting for us because there are just two applications that use the system and, I believe, they are holding the connection persistent.
-
@scottalanmiller said in Get Alert Whenever There is MS SQL Server Access:
It's a production database so there should be an email when the application connects and absolutely no user should ever, ever, ever be able to log in unless it's an admin doing an emergency backup and/or restore (likely alerts would be off during a restore.)
I cannot imagine a MS SQL Server based client-server application that does not make a billion DB calls all day long. So you will have to exclude that system user from being audited.
@scottalanmiller said in Get Alert Whenever There is MS SQL Server Access:
There's no user ever authorized to just connect.
The application user is always connecting. Repeatedly.
