Hace poco se me presentó un problema que luego de buscarlo por varias páginas sin resultados buenos decidí implementar una solución desde cero. El equipo de seguridad informática necesitaba un producto para llevar una auditoría de cambios de AD y controlar que usuarios han sido modificados o se les han asignado nuevos permisos.
El primero intento (y el que abunda en la red) de llevar esto a SQL 2008 tiene sus problemas. Si la empresa está compuesta por pocos usuarios no hay inconveniente, se hace un open query de SQL a AD utilizando LDAP y se controla de a un usuario por vez si cambio algo. El problema es que el SQL no permite traer TODOS los usuarios si estos son muchos, así que hay que buscar otra solución.
La respuesta que plantee consiste de combinar varias tecnologías. Para poder hacer la auditoría primero cargaremos todos los datos de AD en un conjunto de tablas intermedias en SQL. La forma de hacer esto será mediante Power Shell. El Power Shell puede conectarse sin problemas al AD y cada resultado obtenido en el iterador lo volcaremos en la tabla SQL. Hay que tener cuidado con la normalización de las tablas visto que algunos de los campos de AD son multivaluados y debemos guardarlos como tablas independientes (el ejemplo mas importante es Member o MemberOf).
Una vez cargados los datos en las tablas intermedia SQL lo volcamos en la tabla final. Este paso intermedio lo hacemos para poder saber que cosas han cambiado sin tener que volcar una gran lógica en el Power Shell. En lugar de hacer complejas consultas actualizaremos la tabla final (Ej. T_AD_USERS) con la tabla intermedia (Ej. T_AD_USERS_TEMP). La forma mas rápida podría ser hacer un truncate y volcar todo el contenido, pero cambiaremos solamente lo que sea realmente necesario y ya veremos por que. El SQL 2008 nos brinda para esto el comando MERGE (http://technet.microsoft.com/en-us/library/bb510625.aspx) que nos permite de forma rápida incorporar los registros faltantes, modificar los existentes y borrar los dados de baja.
El primero intento (y el que abunda en la red) de llevar esto a SQL 2008 tiene sus problemas. Si la empresa está compuesta por pocos usuarios no hay inconveniente, se hace un open query de SQL a AD utilizando LDAP y se controla de a un usuario por vez si cambio algo. El problema es que el SQL no permite traer TODOS los usuarios si estos son muchos, así que hay que buscar otra solución.
La respuesta que plantee consiste de combinar varias tecnologías. Para poder hacer la auditoría primero cargaremos todos los datos de AD en un conjunto de tablas intermedias en SQL. La forma de hacer esto será mediante Power Shell. El Power Shell puede conectarse sin problemas al AD y cada resultado obtenido en el iterador lo volcaremos en la tabla SQL. Hay que tener cuidado con la normalización de las tablas visto que algunos de los campos de AD son multivaluados y debemos guardarlos como tablas independientes (el ejemplo mas importante es Member o MemberOf).
Una vez cargados los datos en las tablas intermedia SQL lo volcamos en la tabla final. Este paso intermedio lo hacemos para poder saber que cosas han cambiado sin tener que volcar una gran lógica en el Power Shell. En lugar de hacer complejas consultas actualizaremos la tabla final (Ej. T_AD_USERS) con la tabla intermedia (Ej. T_AD_USERS_TEMP). La forma mas rápida podría ser hacer un truncate y volcar todo el contenido, pero cambiaremos solamente lo que sea realmente necesario y ya veremos por que. El SQL 2008 nos brinda para esto el comando MERGE (http://technet.microsoft.com/en-us/library/bb510625.aspx) que nos permite de forma rápida incorporar los registros faltantes, modificar los existentes y borrar los dados de baja.
Con esto ya tenemos una tabla con la información de AD actualizada cuando querramos (podría ser un job que ejecute cada una hora). Lo que nos falta es llevar la auditoría en si misma, y para eso activaremos el CDC (change data capture) sobre la tabla en cuestión. Como nos encargamos de no borrar todo cada vez sino solamente actualizar según los cambios, el CDC llevará un registro de todos los usuarios que hayan sido actualizados. La única salvedad es que nuestra auditoría así como está planteada mostrará las altas, las bajas, y por cada vez que se ejecute el job todos los campos figurarán como actualizado, así que debemos hacer dos MERGE, uno de altas y bajas (WHEN NOT MATCHED BY TARGET y WHEN NOT MATCHED BY SOURCE) y otro para actualizar que contenga en el join que coincida la PK y alguno de los campos haya cambiado.
Con esto ya tendrémos una tabla de auditoría de AD (cdc.dbo_T_AD_USERS_CT), ahora lo único que nos falta es darle a la gente de seguridad informática una interfaz amigable para acceder a ésta. Recordemos que el CDC trabaja con marcas de agua y esto no es muy amigable para la gente de otras áreas, así que lo que recomiendo como mínimo es hacer un stored procedure que tome como parametros dos DATETIME (desde, hasta) y retorne todas las entradas del CDC que se encuentren en dicho período pero cambiando la marca de agua por su fecha correspondiente y mostrar esto desde reporting services u otra interfaz amigable.
Si alguien quiere el código de todo esto implementado no dude en ponerse en contacto conmigo.
Saludos!
Si alguien quiere el código de todo esto implementado no dude en ponerse en contacto conmigo.
Saludos!
