Automatic Securing of SQL Server Configuration Settings

[SQL Server Version 6.5]

Through experience I have found it necessary to maintain an up-to-date
copy of the various SQL Server settings. It is also important to hold this
information separately from the live server in case of complete server
failure.

The following method is one that I have implemented successfully using
three stored procedures and a scheduled task. This task dumps the following
SQL Server information to a the text file
Recovery.out on a floppy
disk along with SQL scripts to create all existing databases.

 

• Date of Generation
• Server Name and User Name
• Current SQL Server Version
• Current Sort Order & Character Set
• Current Standard & Advanced Configuration Options
• Current Database Structure
• Current Device Structure

In order to execute the commands contained within the stored procedures,
the SA login is used, but this exposes the SA password. In order to hide
the password, it is stored within a task, created by the SA. Since only
the SA can examine SA created tasks through the ‘Manage Scheduled Tasks’
window the password cannot be seen by users. This does not fully protect
the password though, because the DBO of database MSDB can examine the system
table SYSTASKS, which contains the task information (see table below).

 

The ‘Recovery Disk’ task is created through the execution of the stored
procedure,  sp_recoverytask. This task will contain the SA
password within the command, but this will only be visible by SA and MSDB
DBO. The SA password [VarChar (30)] is passed to sp_recoverytask as
a variable, this ensures that the password is not hard coded. Along with
the password, the name for the task and an indicator are passed to the
stored procedure.

The task name [VarChar (100)] is the name that the task will be referred
to, and the overwrite indicator [TinyInt] determines if an existing task
of the same name, will be overwritten. By default this is set to 0 and
the task will not be overwritten. To overwrite set to 1.

EXECUTE sp_recoverytask
‘Recovery Disk’, ‘SQLServer1’, 1

This EXECUTE command will create a task Recovery Disk, scheduled
to run at 12:00 am each day, with the command:

EXECUTE sp_recoverydisk
“SQLServer1”

This task, Recovery Disk,  will execute sp_recoverydisk,
which in turn executes sp_structurescript
to produce file recovery.out,
and sp_help revdatabase to produce script files for each database
(e.g. master.sql and mydb.sql) and one script file for all
databases (alldbs.sql).

Installation

Creation of stored procedures in master database:

1. Download the file sqlsecur.sql (8kB)
2. Open file sqlsecur.sql in SQL Query tool, with master database selected
   under SA login
3. Execute the script

Creation of a task to execute the stored procedure:

1. Execute procedure, sp_recoverytask, with parameters Task Name, SA
   Password and Overwrite Indicator:

EXECUTE sp_recoverytask
‘Recovery Disk’, ‘SQLServer1’, 1

2. Confirm the task works correctly by manually running it, to create the
   files on floppy disk
3. Confirm the output within the file is correct and contains no errors (e.g.
   login failures)

Comments

The floppy disk can now be left permanently in the drive of the server,
though I recommend changing the boot options of the machine so as to boot
from the hard drive first, to ignore the non-bootable disk.

The scheduled time of the task can be modified.

Altough the SA password is been held within table msdb..systasks,
and only visible by SA and DBO, SELECT permissions on this table should
be restricted.

Comments welcome.