Mwp.Reid at Queens-Belfast.AC.UK
Mwp.Reid at Queens-Belfast.AC.UK
Thu Apr 17 16:44:57 CDT 2003
Heres a basic security script from sqlsecurity.com
May prove useful to some of you.
Martin
<Copy from here>
--SQL Server 2000 Lockdown Script
--by Chip Andrews (www.sqlsecurity.com)
--12/23/2002
--
--The purpose of this script is to provide administrators (SQL Server or
otherwise) a baseline
--lockdown configuration for new installations. These settings should disable
potentially dangerous
--functionality while leaving the server operational and still capabable of
Service Pack and hotfix
--installations. Feel free to provide feedback at www.sqlsecurity.com if you
find any issues or
--have any suggestions for improvement.
--
--Project Goals:
-- * Must support named instances
-- * Must not break future Service Pack and hotfixes installations
-- * Must strive to disable rarely used functionality but not break common
applications (80-20 rule)
-- * Must be easily runnable from the command prompt for mass distribution
--
--Notes:
--
--*You will note that no Extended Stored Procedures have been dropped in the
script. This is due to several reasons:
-- 1. It causes some problems with Service Packs and hotfix installations
when certain functions are disabled
-- 2. Blocking access to non-sysadmin users is more easily achieved by
dropping execute permissions
-- 3. Sysadmins can easily add them back so dropping them really serves no
real purpose
--*The last script item has been commented out because it effectively blocks
all network access to the SQL Server
-- and thus violates the 80-20 rule. Feel free to enable it for local-only
SQL Server installs.
--
--
SET NOCOUNT ON
PRINT '*** Begin SQL Server 2000 Lockdown Script v1.0 ***'
PRINT ''
PRINT 'SERVER NAME : ' + @@SERVERNAME
PRINT ''
--
--
--Check SQL Server Service Account for LocalSystem Authority - Send warning
--It should be noted that it may be possible to create a local account if
LocalSystem is found
--and alter the service account here in the script. However, since there are
also file ACL and registry
--permissions to deal with then its probably best left to the Enterprise
Manager to do this.
CREATE TABLE #user (value VARCHAR(50), data VARCHAR(50))
IF (charindex('\',@@SERVERNAME)=0)
INSERT #user EXEC
master..xp_regread 'HKEY_LOCAL_MACHINE' ,'SYSTEM\CurrentControlSet\Services\MSS
QLSERVER','ObjectName'
ELSE
BEGIN
PRINT 'Note: SQL Server was determined to be a named instance'
PRINT ''
DECLARE @RegistryPath varchar(200)
SET @RegistryPath = 'SYSTEM\CurrentControlSet\Services\MSSQL$'
+ RIGHT(@@SERVERNAME,LEN(@@SERVERNAME)-CHARINDEX('\',@@SERVERNAME))
INSERT #user EXEC
master..xp_regread 'HKEY_LOCAL_MACHINE' , at RegistryPath,'ObjectName'
END
SELECT TOP 1 DATA AS [SQL Server Service Account] FROM #USER
IF (SELECT TOP 1 DATA FROM #user)='LocalSystem'
PRINT '*** ALERT LOCALSYSTEM AUTHORITY BEING USED FOR SQL SERVER
SERVICE ACCOUNT IS NOT RECOMMENDED. ***'
DROP TABLE #user
PRINT ''
--
-- Confirm the latest service pack and hotfixes have been applied by selecting
-- the server version and comparing it to the most current SQL Server
-- version (at the time of writing that was 8.00.665 for SQL Server 2000).
-- (Although we are not applying the latest patch in this script, we can still
-- output a message warning the user of the script to apply the needed
patches as long as you capture the output.)
SELECT @@version as [SQL Server Version]
IF NOT (charindex('8.00.665',@@version)>0)
BEGIN
print '*** WARNING - SQL Server NOT PROPERLY PATCHED! ***'
END
GO
--
-- Enable Windows Authentication as the only login method to prevent
against ‘sa’
-- account attacks and the weak internal SQL Server authentication model.
IF (charindex('\',@@SERVERNAME)=0)
EXECUTE master.dbo.xp_regwrite
N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'LoginMode
',N'REG_DWORD',1
ELSE
BEGIN
DECLARE @RegistryPath varchar(200)
SET @RegistryPath = 'Software\Microsoft\Microsoft SQL Server\'
+ RIGHT(@@SERVERNAME,LEN(@@SERVERNAME)-CHARINDEX('\',@@SERVERNAME))
+ '\MSSQLServer'
EXECUTE
master..xp_regwrite 'HKEY_LOCAL_MACHINE', at RegistryPath,N'LoginMode',N'REG_DWORD
',1
END
GO
--
-- Set strong ‘sa’ account password (in this case a concatenation of two
-- unique identifiers). This password can easily be reset later by using a
-- trusted connection while logged in as a local administrator or any user
-- who is a member of the System Administrator role.
DECLARE @pass char(72)
SELECT @pass=convert(char(36),newid())+convert(char(36),newid())
EXECUTE master..sp_password null, at pass,'sa'
GO
--
-- Enable full auditing to monitor both successful and failed access to the
-- SQL Server. You may want to scale this back to failed-only is log space
-- is a problem.
IF (charindex('\',@@SERVERNAME)=0)
EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',N'AuditLevel',N'REG_DWORD',3
ELSE
BEGIN
DECLARE @RegistryPath varchar(200)
SET @RegistryPath = 'Software\Microsoft\Microsoft SQL Server\'
+ RIGHT(@@SERVERNAME,LEN(@@SERVERNAME)-CHARINDEX('\',@@SERVERNAME))
+ '\MSSQLServer'
EXECUTE
master..xp_regwrite 'HKEY_LOCAL_MACHINE', at RegistryPath,N'AuditLevel',N'REG_DWOR
D',3
END
GO
--
-- Disable SQLAgent, Microsoft Distributed Transaction Coordinator (MSDTC),
and MSSEARCH
-- since they may potentially represent unnecessary services. There are no
multiple instances of these services.
EXECUTE msdb..sp_set_sqlagent_properties @auto_start = 0
GO
EXECUTE master..xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'SYSTEM\CurrentControlSet\Services\MSDTC', N'Start', N'REG_DWORD', 3
GO
EXECUTE master..xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'SYSTEM\CurrentControlSet\Services\MSSEARCH', N'Start', N'REG_DWORD', 3
GO
--
--Diable adhoc queries for each data provider since this functionality is ripe
for abuse. Once again, if
--your application requires this you can add the functionality back on a per
provider basis.
EXECUTE master.dbo.xp_regwrite
N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Providers\SQLOLEDB',N'Di
sallowAdhocAccess',N'REG_DWORD',1
GO
EXECUTE master.dbo.xp_regwrite
N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Providers\Microsoft.Jet.
Oledb.4.0',N'DisallowAdhocAccess',N'REG_DWORD',1
GO
EXECUTE master.dbo.xp_regwrite
N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Providers\MSDAORA',N'Dis
allowAdhocAccess',N'REG_DWORD',1
GO
EXECUTE master.dbo.xp_regwrite
N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Providers\ADSDSOObject',
N'DisallowAdhocAccess',N'REG_DWORD',1
GO
EXECUTE master.dbo.xp_regwrite
N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Providers\DB2OLEDB',N'Di
sallowAdhocAccess',N'REG_DWORD',1
GO
EXECUTE master.dbo.xp_regwrite
N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Providers\MSIDXS',N'Disa
llowAdhocAccess',N'REG_DWORD',1
GO
EXECUTE master.dbo.xp_regwrite
N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Providers\MSQLImpProv',N
'DisallowAdhocAccess',N'REG_DWORD',1
GO
EXECUTE master.dbo.xp_regwrite
N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Providers\MSSEARCHSQL',N
'DisallowAdhocAccess',N'REG_DWORD',1
GO
EXECUTE master.dbo.xp_regwrite
N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Providers\MSDASQL',N'Dis
allowAdhocAccess',N'REG_DWORD',1
GO
--
--Remove the pubs and northwind sample databases since they represent known
targets with minimal
--permissions for potential attackers.
USE master
DROP DATABASE northwind
DROP DATABASE pubs
GO
--
--Tighten permissions on jobs procedures in case the SQL Agent service is ever
activated to prevent low
--privilege users from submitting or managing jobs.
USE msdb
REVOKE execute on sp_add_job to public
REVOKE execute on sp_add_jobstep to public
REVOKE execute on sp_add_jobserver to public
REVOKE execute on sp_start_job to public
GO
--
--Tighten permissions on web tasks table to keep malicious users from creating
or altering tasks.
USE msdb
REVOKE update on mswebtasks to public
REVOKE insert on mswebtasks to public
GO
--
--Tighten permissions on DTS package connection table so that malicious users
cannot affect DTS packages.
USE msdb
REVOKE select on RTblDBMProps to public
REVOKE update on RTblDBMProps to public
REVOKE insert on RTblDBMProps to public
REVOKE delete on RTblDBMProps to public
GO
--
--Tighten permissions on extended procedures that require heavy use but should
not be allowed public access.
USE master
REVOKE execute on sp_runwebtask to public
REVOKE execute on sp_readwebtask to public
REVOKE execute on sp_MSSetServerProperties to public
REVOKE execute on sp_MScopyscriptfile to public
REVOKE execute on sp_MSsetalertinfo to public
REVOKE execute on xp_regread to public
REVOKE execute on xp_instance_regread to public
GO
--
--Revoke guest access to msdb in order to keep any non system administrators
from accessing the database without explicit permissions.
USE msdb
EXECUTE sp_revokedbaccess guest
GO
--
--Turn off allow remote access to keep other SQL Servers from connecting to
this server via RPC.
EXECUTE sp_configure 'remote access', '0'
GO
RECONFIGURE WITH OVERRIDE
GO
--
--Verify that the capability to allow access to system tables is disabled.
EXECUTE sp_configure 'allow updates', '0'
GO
RECONFIGURE WITH OVERRIDE
GO
--
--Increase SQL Server log history threshold in order to maintain logs for a
longer amount of time.
IF (charindex('\',@@SERVERNAME)=0)
EXECUTE master.dbo.xp_regwrite
N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'NumErrorL
ogs',N'REG_DWORD',365
ELSE
BEGIN
DECLARE @RegistryPath varchar(200)
SET @RegistryPath = 'Software\Microsoft\Microsoft SQL Server\'
+ RIGHT(@@SERVERNAME,LEN(@@SERVERNAME)-CHARINDEX('\',@@SERVERNAME))
+ '\MSSQLServer'
EXECUTE
master..xp_regwrite 'HKEY_LOCAL_MACHINE', at RegistryPath,N'NumErrorLogs',N'REG_DW
ORD',365
END
GO
--
--Remove any residual setup files (\sqldir\setup.iss - \winnt\setup.iss -
\winnt\sqlstp.log) that may be
--lingering on the file system. These scripts now include system variables
thanks to Carlos Moran
EXECUTE master.dbo.xp_cmdshell 'if exist %windir%\setup.iss del %Windir%
\setup.iss'
GO
EXECUTE master.dbo.xp_cmdshell 'if exist %windir%\sqlstp.log del %Windir%
\sqlstp.log'
GO
EXECUTE master.dbo.xp_cmdshell 'if exist "%ProgramFiles%\microsoft sql
server\mssql\install\setup.iss" del "%ProgramFiles%\microsoft sql
server\mssql\install\setup.iss"'
GO
--
--Remove any un-used network libraries. Since this is a hardened server, all
netlibs can be removed until
--external connectivity requirements are identified. Connections to the local
server are still possible
--using the Shared Memeory netlib which is always in effect by
specifiying ‘(local)’ or (a period) as
--the server name. The other netlibs can easily be restored using the Server
Network Utility.
--(You must stop and restart the SQL Server for this change to go into effect)
--This step effectively blocks all network access to this SQL Server. Please
be advised of this before
--applying this step.
--NOTE: This step has been removed by default. Feel free to add it when
applying to local-only installations
--such as MSDE applications or Visual Studio.NET/.NET SDK installs
--
--EXECUTE master.dbo.xp_regwrite
N'HKEY_LOCAL_MACHINE',N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketN
etLib', N'ProtocolList',N'REG_SZ',''
--GO
--
--
PRINT '*** Please Stop and Start the SQL Server Service Instance in order to
implement changes ***'
PRINT ''
PRINT '*** End SQL Server 2000 Lockdown Script ***'