Automatic Securing of SQL Server Configuration Settings
February 7, 2000[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.
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.
This EXECUTE command will create a task Recovery Disk, scheduled to run at 12:00 am each day, with the command:EXECUTE sp_recoverytask 'Recovery Disk', 'SQLServer1', 1
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).EXECUTE sp_recoverydisk "SQLServer1"
Creation of stored procedures in master database:
Creation of a task to execute the stored procedure:
EXECUTE sp_recoverytask 'Recovery Disk', 'SQLServer1', 1
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.