Checklist for suspicious activity on SQL Server

It seems something went awry with the SQL Server. It’s sluggish, behaves erratically, produces heavy network traffic, there is a significant increase in the server processor or memory utilization, and to top it all there are reports of or database objects and data being damaged or missing.

Where to start looking?

First, perform a basic smoke test on the machine where the SQL Server is installed, including:

• Make sure your antivirus/anti malware system is up, running and fully updated and perform a full scan
• Examine the currently running processes and services using the Task Manager or any other third-party tool. Not being able to identify all of the currently running process or services is definitely a big red flag
• Confirm that malicious software hasn’t latched onto you SQL Server process. A quick way to do this, is to check what runs on SQL Server startup using the following SQL script:

  SELECT NAME
  FROM master..sysobjects
  WHERE objectproperty(id, 'ExesIcStartup') = 1
  

Next, verify the following:

• All the SQL databases are still there and review their creation times and state to ensure that no one has dropped and recreated the database either manually or using a database backup or has hampered their functionality. To quickly obtain that information execute:

  SELECT NAME
  	,create_date
  	,user_access_desc
  	,CASE is_read_only
  		WHEN 1
  			THEN 'Yes'
  		WHEN 0
  			THEN 'No'
  		END AS is_read_only
  	,state_desc
  	,CASE is_in_standby
  		WHEN 1
  			THEN 'Yes'
  		WHEN 0
  			THEN 'No'
  		END AS is_in_standby
  	,CASE is_cleanly_shutdown
  		WHEN 1
  			THEN 'Yes'
  		WHEN 0
  			THEN 'No'
  		END AS is_cleanly_shutdown
  FROM sys.databases
  

• Data stored in sensitive tables or columns hasn’t been altered in any way
• All SQL database objects are still present and review their creation times as well as the exact time they were last modified. If the timing is unusual, for instance, over the weekend and you are positive nobody was in the office, that’s a huge red flag. To check this, execute:

  SELECT type_desc
  	,total_count = COUNT(*)
  	,last_create_date = MAX(create_date)
  	,last_modify_date = MAX(modify_date)
  FROM sys.objects
  WHERE sys.objects.type NOT IN (
  		'C'
  		,'D'
  		,'F'
  		,'UQ'
  		)
  GROUP BY type_desc
  

• None of the data is missing. To quickly check number of rows and columns in each SQL table execute:

  SELECT DB_NAME() DatabaseName
  	,s.NAME + '.' + o.NAME TableName
  	,SUM(p.rows) RecordCount
  	,COUNT(c.column_id) ColumnCount
  FROM sys.indexes i
  INNER JOIN sys.partitions p ON i.object_id = p.object_id
  	AND i.index_id = p.index_id
  INNER JOIN sys.objects o ON o.object_id = i.object_id
  INNER JOIN sys.columns c ON o.object_id = c.object_id
  INNER JOIN sys.schemas s ON o.object_id = s.schema_id
  WHERE i.index_id < 2
  	AND o.type = 'U'
  GROUP BY s.NAME
  	,o.NAME
  ORDER BY s.NAME
  	,o.NAME;
  

• There hasn’t been any rogue database or transaction log backup activity off schedule. To verify, execute:

  SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS SERVER
  	,msdb.dbo.backupset.database_name
  	,msdb.dbo.backupset.backup_start_date
  	,msdb.dbo.backupset.backup_finish_date
  	,msdb.dbo.backupset.expiration_date
  	,CASE msdb..backupset.type
  		WHEN 'D'
  			THEN 'Database'
  		WHEN 'L'
  			THEN 'Log'
  		END AS backup_type
  	,msdb.dbo.backupset.backup_size
  	,msdb.dbo.backupmediafamily.logical_device_name
  	,msdb.dbo.backupmediafamily.physical_device_name
  	,msdb.dbo.backupset.NAME AS backupset_name
  	,msdb.dbo.backupset.description
  FROM msdb.dbo.backupmediafamily
  INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
  WHERE (CONVERT(DATETIME, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
  ORDER BY msdb.dbo.backupset.database_name
  	,msdb.dbo.backupset.backup_finish_date
  

Now, it’s time to dive in the SQL Server logs. To do so:

1. In “Object Explorer”, expand the SQL instance, expand “Management”, and then expand “SQL Server Logs”
2. Right-click a log and click “View SQL Server Log”
   Please note that the number of records in those logs can be staggering. To process them easier, click “Filter” in the top menu to specify the time range in which the suspicious activity occurred or the user or computer that is suspected to be involved in such an activity. The following types of entries are good indicator that something is amiss:
   • SQL Server activity out of working hours or off schedule
   • Failed login attempts
   • Errors due to attempting to access SQL objects or data without the appropriate permissions

Everything seems fine. Does it mean that everything is fine?

Unfortunately, no. Some of the steps mentioned above are really effective if a mirrored SQL database or transaction log shipping is in place, or at least a reliable database backup is available to help verify that no objects or data have been lost. Even so, for databases containing a large number of objects or storing huge amounts of data this process is very slow, tedious and error prone. Sure, if suddenly half of the tables have zero rows that’s a clear sign that some foul play is involved but what only a single row or object was dropped, or even worse, altered? Is the only alternative to go over and check each row and each object manually?

Fortunately, no. The transaction log keeps a record of each change made to the database including information on when the change was made and who made it. The best part is – due to its nature, that information cannot be tampered with. But, there’s a catch – the transaction log isn’t humanly readable on its own. This is where ApexSQL Log comes into play.

ApexSQL Log is an auditing and recovery tool for SQL Server databases which reads transaction logs, transaction log backups, detached transaction logs and database backups, and audits, reverts or replays data and object changes that have affected the database, including the ones that have occurred before the product was installed.

To investigate any suspicious database changes with ApexSQL Log

1. Start ApexSQL Log
2. Connect to the database and click “Next”
3. If you have any transaction log backups made before the suspected activities have occurred, click on the “Add” button

4. Select appropriate transaction log backups and click “Open”

   

5. Click the “Next” button to advance to the filter setup which enables you to narrow down the results to a specific timeframe, operation, user, object or data row

   

The changes that meet the specified criteria, along with their details, will be listed in the application’s main grid. To narrow down the result set further, use the “Grid filter” in the left pane



In summary, examining your SQL Server for suspicious activity takes several steps – from checking for malicious software to manually reviewing the impact on the database itself and analyzing the SQL Server logs. However, if you want to ensure that no suspicious change to your SQL database made past you, examine its transaction log with ApexSQL Log.

Downloads

Please download the script(s) associated with this article on our GitHub repository.

Please contact us for any problems or questions with the scripts.

 

Related posts:

1. Continuous auditing of SQL Server database using the transaction log
2. Continuous SQL Server database auditing and reporting using the transaction log
3. SQL Server database auditing techniques
4. Before and after auditing in SQL Server
5. SQL Server true detective – Solving the case of the broken database
April 4, 2013