Security Audit – Part 2

Once again, we delve deep into what is part of a security audit and why each part is important.

One part that I like to look at is the default directories where certain types of files are kept. To do this – on SQL Server 2012 and above – we simply run the following commands:

SELECT
SERVERPROPERTY('instancedefaultdatapath') AS [DefaultFile],
SERVERPROPERTY('instancedefaultlogpath') AS [DefaultLog]

EXECUTE [master].dbo.xp_instance_regread N’HKEY_LOCAL_MACHINE’, N’SOFTWARE\Microsoft\MSSQLServer\MSSQLServer’, N’BackupDirectory’

Notice that both the default data and default log directories are in the top query. This gives you, as it says, the folders that are setup, by default, for SQL Server to place the corresponding types of files in. Obviously, you can change the folder where the files are for individual databases.

A few things to pay attention to with this information:

1. Neither of these should be on the C-drive. (Unless there is only one drive for the entire server – which is a completely different story.)
   1. If the C-drive fills up with files, Windows could potentially shutdown. This is definitely not good for a Windows server, because you could have issues bringing the server back up and therefore, even more issues bringing SQL Server back up.
      1. Obviously, Linux works a little different, but the same basic ideas exist.
   2. There should be checks put in place to warn administrators as part of a monitoring solution when there is a certain percentage of free disk space. I tend to set it up so that I am warned at 10%, and again at 5%, but please change as you see fit.
2. The drives listed as part of the default must be part of the monitoring solution. This will keep the databases up and running.

The bottom query is one of those that I dislike running. It is having to read from the registry the default backup directory. Like above, the backup files should not be on C-drive. This could cause Windows to shut down quickly. Pre-2012 versions of SQL Server require that you get the default data and log file directories this way as well. The reader is encouraged to check on your favorite search engine for exactly where in the registry these values are kept.

Now, for an added wrinkle, with the backups, they should not be on the same drive as either the data or the log files. This is done to prevent data loss. If the drive where the data files are kept becomes corrupted, then the backups should be safe. The same can be said for the log files. At best, the backups should be on a completely different SAN than the data and log files, but that, as they say, is a whole other story.

So, until next time when we learn even more about security audits…