Vuelven los Auditores I

Fieles a su cita vuelven los auditores a destrozar la tranquilidad y al paz del dba. Creo que ahora nos toca SOX . Así que toca exprimir el cerebro tratando de entender qué demonios piden los auditores y cómo puedes demostrarles que tus servidores cumplen sus normas. Una pesadilla
Aún no nos han dicho qué es exactamente lo que quieren este año. Algo que no suele faltar es lo que véis aquí abajo:

General Information
ProductName Microsoft SQL Server
ProductVersion 8.00.2040
Platform NT INTEL X86
FileVersion 2000.080.2040.00 Hotfix 2140
WindowsVersion 5.0 (2195)
ProcessorCount 4
ProcessorType PROCESSOR_INTEL_PENTIUM
PhysicalMemory 7679 (8052326400)



SERVICES
Service Account Status
MSSqlsrvr LocalSystem Autostart
SQLAgent domain\account Autostart



GENERAL SECURITY
Login Mode SQL and NT
Audit Level Failures
Default Domain domain
Default Login guest

Os dejo el script que genera este informe (No lo he probado aún con el 2005)
Para generarlo directamente en excel, recordad que hay que seleccionar la salida en archivo y luego poned el nombre que queráis con la extensión xls






set nocount on
declare @strHTML varchar (8000)
SET @strHTML = ''






create table #gral ([index] int, [name] varchar (40), internal_value varchar (10), character_value varchar(150))

insert into #gral exec master..xp_msver ProductNAme
insert into #gral exec master..xp_msver ProductVersion
insert into #gral exec master..xp_msver Platform
insert into #gral exec master..xp_msver fileVersion
insert into #gral exec master..xp_msver windowsVersion
insert into #gral exec master..xp_msver ProcessorCount
insert into #gral exec master..xp_msver ProcessorType
insert into #gral exec master..xp_msver PhysicalMemory




select '< DIV ALIGN="center" > < TABLE BORDER="1" CELLPADDING="2" CELLSPACING="0" BORDERCOLOUR="003366" WIDTH="60%" >
< TR BGCOLOR="EEEEEE" > < TD CLASS="Title" COLSPAN="2" ALIGN="center" > < A NAME="GENERAL INFORMATION" > < B > General Information< /B > < /A > < /TD > < /TR >
'
union all
select '< TR > < TD VALIGN= "top" > < I > '+ [name] + '< /I > < /TD > '+
'< TD VALIGN= "top" > '+character_value +' < /TD > '+
'< /TR > ' from #gral

union all

select '< /TABLE > '









drop table #gral
print '< BR > '


declare @ser varchar (40)
set @ser = (SELECT CONVERT(char(20),SERVERPROPERTY('servername')))

--SQLServer
declare @sqlsrv varchar (50)
declare @status varchar (20)


CREATE TABLE #srvr (value VARCHAR(50), data VARCHAR(50))
IF (charindex('\',@ser)=0) -- no es instancia

-- no instance
begin -- = 7 and 2k

insert #srvr exec master..xp_regread 'HKEY_LOCAL_MACHINE' ,'SYSTEM\CurrentControlSet\Services\MSSQLSERVER','ObjectName'
INSERT #srvr EXEC master..xp_regread 'HKEY_LOCAL_MACHINE' ,'SYSTEM\CurrentControlSet\Services\MSSQLSERVER','Start'

GOTO REP

end

else

begin

--instance

DECLARE @RegistryPath varchar(200)
SET @RegistryPath = 'SYSTEM\CurrentControlSet\Services\MSSQL$' + RIGHT(@@SERVERNAME,LEN(@@SERVERNAME)-CHARINDEX('\',@@SERVERNAME))
INSERT #srvr EXEC master..xp_regread 'HKEY_LOCAL_MACHINE' ,@RegistryPath,'ObjectName'
INSERT #srvr EXEC master..xp_regread 'HKEY_LOCAL_MACHINE' ,@RegistryPath,'Start'
GOTO REP
end
REP:

select @sqlsrv = data from #srvr where value = 'ObjectName'
select @status = case data when '2' then 'Autostart'
when '3' then 'Manual'
when '4' then 'Disabled'
end from #srvr
DROP TABLE #srvr


SELECT @strHTML = '< TABLE BORDER="1" CELLPADDING="2" CELLSPACING="0" BORDERCOLOUR="003366" WIDTH="60%" >
< TR BGCOLOR="EEEEEE" > < TD CLASS="Title" COLSPAN="3" ALIGN="center" VALIGN="top" > < B > < A > SERVICES< /B > < /A > < /TD > < /TR >
< TR BGCOLOR="EEEEEE" >
< TD ALIGN="left" WIDTH="25%" > < B > Service< /B > < /TD >
< TD ALIGN="left" WIDTH="50%" > < B > Account< /B > < /TD >
< TD ALIGN="left" WIDTH="25%" > < B > Status< /B > < /TD >
< /TR > '
print @strHTML



--sqlserveragent

CREATE TABLE #AGENT (value VARCHAR(50), data VARCHAR(50))
IF (charindex('\',@ser)=0)

begin
INSERT #AGENT EXEC master..xp_regread 'HKEY_LOCAL_MACHINE' ,'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT','ObjectName'
INSERT #AGENT EXEC master..xp_regread 'HKEY_LOCAL_MACHINE' ,'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT','Start'

GOTO REPT

end

else

begin

DECLARE @RegPath varchar(200)
SET @RegPath = 'SYSTEM\CurrentControlSet\Services\SQLAGENT$' + RIGHT(@@SERVERNAME,LEN(@@SERVERNAME)-CHARINDEX('\',@@SERVERNAME))
INSERT #AGENT EXEC master..xp_regread 'HKEY_LOCAL_MACHINE' ,@RegPath,'ObjectName'
INSERT #AGENT EXEC master..xp_regread 'HKEY_LOCAL_MACHINE' ,@RegPath,'Start'
GOTO REPT
end
REPT:
declare @sqlage varchar (50)
declare @statage varchar (20)
select @sqlage = data from #agent where value = 'ObjectName'
select @statage = case data when '2' then 'Autostart'
when '3' then 'Manual'
when '4' then 'Disabled'
end from #agent

DROP TABLE #AGENT



set @strHTML = '< TR > < TD VALIGN= "top" > MSSqlsrvr < /TD > < TD VALIGN= "top" > '+ @sqlsrv +
'< /TD > < TD VALIGN= "top" > '+@status+' < /TD > '+
'< TR > < TD VALIGN= "top" > SQLAgent < /TD > < TD VALIGN= "top" > '+ @sqlage +
'< /TD > < TD VALIGN= "top" > '+@statage+' < /TD > < /TR > < /TABLE > '



print @strHTML

print '< BR > '
---------------------------------


--general security


CREATE TABLE #sec (value VARCHAR(30), data VARCHAR(30))
IF (charindex('\',@ser)=0)

begin

INSERT #sec EXEC master..xp_regread 'HKEY_LOCAL_MACHINE' ,'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER','AuditLevel'
INSERT #sec EXEC master..xp_regread 'HKEY_LOCAL_MACHINE' ,'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER','DefaultDomain'
INSERT #sec EXEC master..xp_regread 'HKEY_LOCAL_MACHINE' ,'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER','DefaultLogin'
INSERT #sec EXEC master..xp_regread 'HKEY_LOCAL_MACHINE' ,'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER', 'LoginMode'

GOTO REPORT

end
else

begin
DECLARE @Path varchar(200)
DECLARE @Path2 varchar(200)
SET @Path = 'SOFTWARE\MICROSOFT\MICROSOFT SQL SERVER\' + RIGHT(@@SERVERNAME,LEN(@@SERVERNAME)-CHARINDEX('\',@@SERVERNAME))+'\MSSQLSERVER'
SET @Path2 ='SOFTWARE\MICROSOFT\MICROSOFT SQL SERVER\' + RIGHT(@@SERVERNAME,LEN(@@SERVERNAME)-CHARINDEX('\',@@SERVERNAME))+'\MSSQLSERVER\CURRENTVERSION'
INSERT #sec EXEC master..xp_regread 'HKEY_LOCAL_MACHINE' ,@Path,'AuditLevel'
INSERT #sec EXEC master..xp_regread 'HKEY_LOCAL_MACHINE' ,@Path,'DefaultDomain'
INSERT #sec EXEC master..xp_regread 'HKEY_LOCAL_MACHINE' ,@Path,'DefaultLogin'
INSERT #sec EXEC master..xp_regread 'HKEY_LOCAL_MACHINE' ,@Path,'LoginMode'


end
GOTO REPORT

REPORT:
declare @loginMode varchar (30)
declare @AuditLevel varchar (30)
declare @DefaultDomain varchar (30)
declare @DefaultLogin varchar (30)


select @LoginMode = case data when '1' then 'NT' when '2' then 'SQL and NT' end from #sec where value = 'LoginMode'

select @AuditLevel =case data when '0' then 'None'
when '1' then 'Success'
when '2' then 'Failures'
when '3' then 'All ' end from #sec where value = 'AuditLevel'

select @DefaultDomain = data from #sec where value = 'defaultDomain'

select @DefaultLogin = data from #sec where value = 'defaultLogin'



drop table #SEC

SELECT @strHTML = '< TABLE BORDER="1" CELLPADDING="2" CELLSPACING="0" BORDERCOLOUR="003366" WIDTH="60%" >
< TR BGCOLOR="EEEEEE" > < TD CLASS="Title" COLSPAN="2" ALIGN="center" VALIGN="top" > < B > < A > GENERAL SECURITY< /B > < /A > < /TD > < /TR >
'
print @strHTML


set @strHTML = '< TR > < TD VALIGN= "top" > < I > Login Mode < /I > < /TD > < TD VALIGN= "top" > ' +
+@LoginMode+' < /TD > '+
'< TR > < TD VALIGN= "top" > < I > Audit Level< /I > < /TD > < TD VALIGN= "top" > ' +
+@AuditLevel+' < /TD > < /TR > ' +
'< TR > < TD VALIGN= "top" > < I > Default Domain< /I > < /TD > < TD VALIGN= "top" > ' +
+@DefaultDomain+' < /TD > < /TR > '+
'< TR > < TD VALIGN= "top" > < I > Default Login< /I > < /TD > < TD VALIGN= "top" > ' +
+@DefaultLogin+' < /TD > < /TR > < /TABLE > < BR > '

print @strHTML