Case #2: Some of the tables and stored procedures got dropped/deleted and recreated in the production database mysteriously. Nobody is taking responsibility for this.
Case sections:
| Case Description (go top) |
|---|
| Application users suddenly started getting errors, since around mid-day. No changes have been made to the appliction or database according to the application manager. Most of the errors referred to missing objects or mismtached schema, as shown below: Msg 208, Level 16, State 1, Line 8 Invalid object name ‘App.DocumentRevisionHistory’ Msg 213, Level 16, State 1, Line 14 Column name or number of supplied values does not match table definition. Application support engineer has done the initial investigation. Looking at the create_date and modify_date columns of sys.objects she found that few new database objects were created and some existing objects were modified around mid-day. She concluded that someone ran a release script into the production database, without making the corresponding application changes. The application is down and unusable. Nobody is admitting to running the release script in the production database. Issue has been escalated to the production DBA team for further investigation, with a view to finding out: – who made these changes in production – get the database to a last known good state, and get the application working again |
| Resolution (go top) |
|---|
| After discussing the issue with application support team, the DBA has decided to restore the production database, to get it to a last know good state. Application errors have started aroound 12:25 PM. A decision was taken to restore the database to a point in time, which is 12:20 PM. DBA sucessfully restored over the production database using the below backup files: – Restore from the full backup taken at 2:30 AM that morning with NORECOVERY option – Restore all the transaction log backups since 2:30 AM till 12:15 PM with NORECOVERY option – Restore the transaction log backup from 12:30 PM, with RECOVERY & STOPAT clause to restore until 12:20 PM Application support team has restarted the application and the app health checks are green, and the errors have stopped. Application is now up and running and users are back online. DBA now needs to find out who made these unauthorised changes to the production database. |
| Root Cause Analysis (RCA) (go top) |
|---|
| Here are the usual DBA steps to find out who did what on an SQL Server database: SQL traces (or Profiler sessions): There were no SQL traces or Profiler sessions running at the time of the unauthorised change in production. There are no third party auditing tools like Guardium configured on this server – so this option is unavaiable for investigation. Activity logging processes: There are no activity logging processes like sp_WhoIsActive scheduled on this server. A procedure like sp_WhoIsActive when configured and scheduled, will log all activity to a table, which can later be reviewed. This option is also ruled out for investigation. DDL triggers or Change Data Capture (CDC): This server has not got any DDL triggers or CDC in place to track DDL activities, hence this option is also ruled out. Extended Events sessions or SQL Server Audit: No extended events or server audit sessions exist either and this is not an option for investigation. Schema Changes History Report: SQL Server Management Sutdio (SSMS) provides the built-in Schema Changes History Report. You can access it by right clicking on the database and choosing Reports -> Schema Changes History. This has showed that a lot of objects were dropped and recreated and new objects were added, but this report does not show the login name of the user that has made the changes. While it has confirmed the unauthorised changes, it couldn’t tell who made the changes. LAST RESORT OPTION – the Default System Trace: Luckily the default system trace runs by default and captures lots of useful diagnostic information to help DBAs troubleshoot problems. Fortunately this also captures object changes like dropping/creating/altering tables and other objects. Good news: This has clearly showed when those unauthorised changes were made on the production database and by who. FInally we have our culprit! Bad News: Whoever did this had used an SQL authenticated login to make these changes, rather than using their own Windows login. So we couldn’t identify the specific user who made the change. Good new again: Fortunately, the default system trace also captures the host name from where the change was run into production. Our IT department has a record of all PCs and their users. This information has finally led us to the actual user who made those changes in the prodution database. It turned out there was no malicious intent there. The user had meant to run this change in a non-production environment, but connected to the production server by mistake. It sitll caused an outage and highlighted some serious holes in the system and security configuration. |
| Prevention (go top) |
|---|
| Production access control: Make sure no account has permanent elevated access to production databases. Elevated production access should only be granted to users by a DBA, once a corresponding change request has been approved. This would have prevented the above accidental production deployment. Login and password seggregation: Do not use the same logins across production and non-production environments. Have a separate set of accounts for production and non-production environments. In case of SQL authenticated logins, make sure the passwords are different across the environments. This would have prevented the accidental production deployment, as the non-prod password provided by the user would not have matched the production password, and would have resulted in a login failure. |
| Additional Notes (go top) |
|---|
| Here is some additional information on how to prevent incidents like above, and best practices to put in place, to effectively troubleshoot such incidents: Windows authentication & access management: Make sure Windows authentication is used as much as possible, so we can tie down any actions to a specific user. If you are using shared generic Windows accounts for production access, you should manage access to those accounts using tools such as CyberArk safes, so that there is a log of which user is checking out this account from the safe. Tracing and auditing: If you have a critical database, or if the data is bound by regulatory frameworks, you must have some sort of auditing in place. This can be achieived by having a continuously running server side sql trace, extended events, server/database auditing, DDL triggers or Change Data Capture (CDC). Test out which solution works best for your environment and implement it. This would give you a definitive log of who is doing what on your sensitive production databases. Best practice around workstation use: In this particular incident, we could not identify the individual user easily, as they used an SQL authenticate login. So we had to investigate using the host name captured by default system trace. Make sure your IT deparment has a log of who is using wihch workstation. Ideally, all workstation computer objects in the Active Directory (AD) should have a comment with the name of the user that it’s been assigned to. Worst case, a system administrator should be able to work out who was logged onto a work station at a given time, by examining the interactive login events from Windows System Event logs. Additional information about default system trace: SQL Server by default runs a default trace, which starts when SQL Server starts. You can see if its enabled or disabled by running one of the below commands: EXEC sp_configure ‘default trace enabled’; SELECT name, value_in_use FROM sys.configurations WHERE name = ‘default trace enabled’; The default trace captures various events that are useful for troubleshooting. To see the list of events captured by the default trace, run this queyr: DECLARE @TraceID int = (SELECT id FROM sys.traces WHERE is_default = 1) SELECT trace_event_id, name FROM sys.trace_events WHERE trace_event_id IN ( SELECT DISTINCT eventid FROM sys.fn_trace_geteventinfo(@TraceID) ) As you can see from the output of the above query, it captures the below events, which are useful for identifying schema and DDL changes: Event ID 46 – Object:Created Event ID 47 – Object:Deleted Event ID 164 – Object:Altered You can now query the current default trace for these events, as shown below (change the database name and date/time filter as required, in the WHERE clause): DECLARE @Path nvarchar(1024) = (SELECT [path] FROM sys.traces WHERE is_default = 1) SELECT e.name, t.DatabaseName, t.ObjectName, t.LoginName, t.HostName, t.ApplicationName, t.SPID, t.StartTime, t.ObjectID FROM ::fn_trace_gettable(@Path, NULL) AS t JOIN sys.trace_events AS e ON t.EventClass = e.trace_event_id WHERE e.name IN(‘Object:Created’, ‘Object:Deleted’, ‘Object:Altered’) AND t.DatabaseName = ‘YourDatabaseName’ AND t.StartTime BETWEEN ‘20260629 09:00’ AND ‘20260629 12:20’ If you look in the SQL Server error log folder, you will find the previous trace files as well, which would be useful for investigating issues from some time ago. Just specify the required trace file (.trc file) as input to ::fn_trace_gettable() function. |
