Auditando cambios de seguridad en SQL Server

Cuando se trata de seguridad de SQL Server, es importante notar que hay niveles de seguridad de servidor y de base de datos. Todo trabajo hecho por un usuario es realizado en la base de datos, pero para acceder a la base de datos y hacer el trabajo, el usuario primero necesita acceder al servidor, y después a la base de datos – el nivel de seguridad del servidor afecta al nivel de seguridad de la base de datos.

Para acceder al servidor, una entidad de inicio de sesión apropiada debe ser configurada para el usuario. Una entidad de inicio de sesión de SQL Server determina las credenciales de usuario para acceder a SQL Server. La seguridad en SQ Server inicialmente depende de inicios de sesión apropiadamente configurados, dado que ellos realmente representan una entrada a SQL Server y sus bases de datos. También, los inicios de sesión son un campo importante de regulaciones – la confidencialidad, consistencia y exactitud de los datos puede ser fácilmente arriesgada si un usuario tiene suficientes permisos en su inicio de sesión.

Hay dos propiedades de inicios de sesión de SQL Server relacionados con la seguridad:

• Roles de Servidor – “SQL Server provee roles a nivel de servidor para ayudarle a administrar los permisos en un servidor. Estos roles son principales de seguridad que agrupan otros principales. Los roles a nivel de servidor abarcan todo el servidor en alcance de sus permisos. (Los roles son como grupos en el sistema operativo Windows)” [1]
• Elementos protegibles – los recursos cuyo acceso es regulado por el sistema de autorización del Motor de Base de Datos de SQL Server (por ejemplo, Ver los permisos de cualquier base de datos).

Estas propiedades pueden ser cambiadas usando ya sea SQL Server Management Studio o T-SQL. EN ambos casos, las operaciones GRANT, REVOKE o DENY son realmente aplicadas en la entidad de inicio de sesión de SQL Server que está siendo cambiada.

Para cumplir con las regulaciones y mantener SQL Server seguro, se requiere auditar los cambios de seguridad, ya sea que los cambios fueron intencionales o accidentales.

Auditar los cambios de seguridad de SQL Server usando desencadenadores DDL

Los cambios aplicados a los inicios de sesión de SQL Server relacionados a las propiedades de los Elementos Protegibles y los Roles de Servidor pueden ser auditados usando desencadenadores DDL a nivel del servidor. En el siguiente ejemplo, usaremos los eventos de seguridad de SQL Server ADD_SERVER_ROLE_MEMBER, DDL_GDR_SERVER_EVENTS y DROP_SERVER_ROLE_MEMBER en el desencadenador DDL, el cual activará y capturará las acciones de alteración de las entidades de inicio de sesión.

CREATE TRIGGER DDL_AUDIT_Logins ON ALL SERVER
FOR ADD_SERVER_ROLE_MEMBER
	,DDL_GDR_SERVER_EVENTS
	,DROP_SERVER_ROLE_MEMBER AS

SET NOCOUNT ON;

DECLARE @EventsTable TABLE (
	EType NVARCHAR(max)
	,EObject VARCHAR(100)
	,EDate DATETIME
	,EUser VARCHAR(100)
	,ECommand NVARCHAR(max)
	);
DECLARE @EType NVARCHAR(max);
DECLARE @ESchema NVARCHAR(max);
DECLARE @DBName VARCHAR(100);
DECLARE @Subject VARCHAR(200);
DECLARE @EObject VARCHAR(100);
DECLARE @EObjectType VARCHAR(100);
DECLARE @EMessage NVARCHAR(max);
DECLARE @ETSQL NVARCHAR(max);

SELECT @EType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]',
 'nvarchar(max)')
,@ESchema = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]',
 'nvarchar(max)')
,@EObject = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]',
 'nvarchar(max)')
,@EObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]',
'nvarchar(max)')
,@DBName = EVENTDATA().value('
(/EVENT_INSTANCE/DatabaseName)[1]',
 'nvarchar(max)')
,@ETSQL = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 
'nvarchar(max)');

INSERT INTO @EventsTable
SELECT @EType
	,@EObject
	,GETDATE()
	,SUSER_SNAME()
	,@ETSQL;

SET @EMessage = 'Login_Event: ' + @EType + CHAR(10) + 'Event Occured at: '
 + Convert(VARCHAR, GETDATE()) + CHAR(10) + 'Changed Login: ' + @EObject + 
CHAR(10) + 'Changed by: ' + SUSER_SNAME() + CHAR(10) + 'Executed T-SQL: ' + 
@ETSQL

SELECT @Subject = 'SQL Server Login changed on ' + @@servername;

EXEC msdb.dbo.sp_send_dbmail @recipients = 'DDL_Alert@companydomain.com'
	,@body = @EMessage
	,@subject = @Subject
	,@body_format = 'HTML';

SET NOCOUNT OFF;
GO

La auditoría de los desencadenadores provee un rastreo viable de los cambios en los inicios de sesión, utilizando la tecnología Eventos Extendidos. Esta característica puede ser configurada usando T-SQL o SQL Server Management Studio. Nosotros usaremos SQL Server Management Studio en este artículo. Los scripts T-SQL correspondientes pueden ser fácilmente generados usando la opción CREATE To

Para rastrear y documentar cambios en las entidades de inicio de sesión en una instancia específica de SQL Server:

1. Expanda la carpeta Security
2. Seleccione New Audit y, usando el diálogo Create Audit, establezca el Audit name ((por ejemplo, AuditLoginChangesSpecification) y File path (un archivo sqlaudit con un nombre correspondiente será creado en la carpeta y usado como un repositorio de auditoría)

   

3. Haga clic en OK para confirmar la creación del objeto de auditoría SQL Server.
4. Haga clic derecho en AuditLoginChangesSpecification y seleccione la opción Enable Audit

   Usando los siguientes pasosnosotrs crearemos una especificación de auditoría del servidor, lo cual requiere un objeto de auditoría de SQL Server previamente creado (AuditLoginChangesSpecification). La especificación de auditoría del servidor será usado para afinar y especificar exactamente qué será rastreado – cambios en entidades de inicio de sesión.

5. Haga clic derecho en la carpeta Server Audit Specification y seleccione New Server Audit Specification
6. Escriba el nombre de la nueva especificación de auditoría de servidor (por ejemplo, AuditLoginChangesServerSpecification) y seleccione el objeto de auditoría previamente creado usando el menú desplegable Audit. Para afinar la auditoría, establezca lo siguiente:
1. Audit Action Type: SERVER_ROLE_MEMBER_CHANGE_GROUP – “Este evento es lanzado cuando sea que un inicio de sesión es añadido o removido de un rol fijo de servidor. Este evento es lanzado para los procedimientos almacenados sp_addsrvrolemember y sp_dropsrvrolemember.” [2]
2. Audit Action Type: SERVER_PERMISSION_CHANGE_GROUP – “Este evento es lanzado cuando GRANT, REVOKE o DENY son emitidos por permisos en el alcance del servidor, como cuando creando un inicio de sesión.” [2]

   

7. Haga clic en OK para confirmar la creación de la especificación de auditoría de servidor.
8. Para completar el proceso de configuración de la característica de auditoría de SQL Server, haga clic derecho en la auditoría creada y seleccione la opción Enable Server Audit Specification. /li>

Después de que el objeto de auditoría y la especificación de auditoría de servidor están configurados y habilitados, los cambios relacionados conseguridad en SQL Server ejecutados en entidades de inicio de sesión serán rastreados y documentado en el archivo .sqlaudit.

Para ver los cambios auditados de inicio de sesión en SQL Server:

1. Para iniciar el diálogo de vista del registro de auditoría, haga clic derecho en la auditoría AuditLoginChangesSpecification y seleccione la opción View Audit Logs.
2. El diálogo mostrará los datos capturados acerca de los cambios de inicio de sesión con sentencias ejecutadas en las entidades de inicio de sesión.

La otra manera de exportar y proveer datos capturados es vía la función fn_get_audit_file. La función lee los archivos de repositorio *.sqlaudit creados por la característica SQL Server Audit.

El siguiente script consulta la información capturada relacionada a cambios de inicio de sesión en SQL Server:

SELECT event_time
	,session_server_principal_name AS Changed_by
	,target_server_principal_name AS LoginName
	,server_instance_name
	,statement
FROM sys.fn_get_audit_file('C:\AUDITs\*.sqlaudit', DEFAULT, DEFAULT)
WHERE action_id = 'G'
	OR action_id = 'APRL';

A diferencia de la auditoría a nivel de base de datos, la característica nativa SQL Server Audit a nivel del servidor es soportada en todas las ediciones de SQL Server, comenzando de SQL Server 2008. De todas maneras, para reportes y consultas viables de información capturada, es necesario un conocimiento de T-SQL. Y de nuevo, un usuario con suficientes permisos puede deshabilitar la especificación de auditoría, aplicar los cambios de inicio de sesión y habilitar de vuelta la especificación de auditoría sin dejar rastros.

Auditar cambios de inicio de sesión SQL Server – la solución por defecto

Cualquier cambio relacionado a seguridad aplicado a entidades de inicio de sesión de SQL Server es un problema de seguridad potencial. Además, cualquier regulación de normas requiere que los cambios de inicio de sesión sean capturados, apropiadamente documentados y hechos disponibles a solicitud.

ApexSQL Audit es una SQL Server auditing tool, con un rango de reportes integrados y personalizados, la cual monitorea cambios y accede a múltiples instancias de SQL Server y sus objetos (bases de datos, tablas, procedimientos almacenados, funciones y vistas).

Para auditar cambios en una instancia SQL Server particular:

1. Inicie la Interfaz Gráfica de ApexSQL Audit.
2. Seleccione la instancia de SQL Server que desea auditar y seleccione las opciones DDL y Securitydebajo de Operations en el filtro Simple.

   

   O usando el filtro Advanced:

   

3. Confirme el cambio de configuraciones seleccionando la opción Apply en la cinta emergente.

   

Para auditar cambios de seguridad en múltiples instancias SQL Server, repita los primero dos pasos en cada instancia SQL Server.

Los datos capturados acerca de cambios en entidades de inicio de sesión son reportados dentro del reporte Security configuration history. El reporte provee todos los cambios en las entidades de seguridad (inicios de sesión, usuarios y roles) incluyendo información acerca de inicios de sesión siendo creados o eliminados, los permisos otorgados, revocados o denegados. Cada evento es documentado con un tiempo de ejecución, el nombre de la instancia SQL Server, el nombre del inicio de sesión, la aplicación (por ejemplo, Microsoft SQL Server Management Studio), el nombre de la computadora cliente, la operación, el T-SQL que fue ejecutado, la clase (por ejemplo, Audit add member to DB role event) y la sub clase del evento.

Para rastrear y documentar cambios en las entidades de inicio de sesión de SQL Server, ApexSQL Audit provee:

• Configuración centralizada basada en clics para múltiples instancias de SQL Server.
• Proceso de monitoreo y reporte sin usar T-SQL.
• Auditoría automática de eventos relacionados con la seguridad de SQL Server sin excluir acciones administrativas, e identificación de riegos potenciales de seguridad.
• Una base de datos repositorio central con características de archivar.
• Reportes exhaustivos, exactos y a prueba de modificaciones para revisión.

Para auditar cambios de inicios de sesión en SQL Server usted puede usar ya sea las características y métodos de SQL Server o productos como ApexSQL Audit. Incluso los métodos que están integrados no ofrecen una solución por defecto, y requieren conocimiento avanzado para configurar y reportar la información capturada. Por otra parte, ApexSQL Audit provee características de valor añadido como un diseño centralizado y a prueba de modificaciones, una solución por defecto para el cumplimiento de requerimientos, auditoría de múltiples instancias SQL Server y más vía una interfaz de usuario amigable.

Referencias

[1] Server-Level Roles
[2] SQL Server Audit Action Groups and Actions

Recursos útiles

Microsoft SQL Server 2012 Security Cookbook
Audit Login Change Property Event Class
Login Auditing (SQL Server Management Studio)
DDL_SERVER_SECURITY_EVENTS

Traductor: Daniel Calbimonte