Audit SQL2008 + Change Data Capture (CDC) + ASP.NET + WCF + Silverlight 3

Alguna vez les han pedido los usuarios que querían saber quien modificó que dato y que día lo hizo?, cuales fueron los datos anteriores?, etc. Y tenían una aplicación web que tenia una autenticación vía Forms (que autenticaba por active directory un usuario windows) y usaba Silverlight, Ajax, ASPXs y Servicios (WCF + Silverlight) y el windows principal tenia que pasar por todas esas capas???? pues esa es mi historia 🙁

como empezó? pues primero no quería programar o al menos quería tener lo mínimo de programación y la mayor performance (mi sueño de programador :p) ….

Viendo las nuevas características que aparecieron con SQL2008 me tope con la auditoria (una forma de hacer trace), y eso porque estaba buscando una forma de saber quien modifico que dato y como estuvo antes de la modificación, además de las inserciones y modificaciones. La idea era evitar los TRIGGERS!!!:

Resultado Final que necesitaba tener:

Usuario 1 — Inserción tabla X1 — Datos(d1,d2,d3…)
Usuario 2 — Actualización tabla X1 — DatosIniciales(d1,d2,d3…)  DatosFinales(d1,d2,d3…)
… etc.

Parte I

Primero di alta mi Audit en SQL2008 (a nivel servidor)  (Audit es una nueva característica de SQL 2008 y me permite capturar eventos del servidor y almacenarlos en un archivo, log de windows, etc –> lo puedes revisar aquí ):

use master
go
CREATE SERVER
AUDIT [ServerAudit]
TO FILE
( FILEPATH = ‘C:’
,MAXSIZE = 100 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)WITH

(QUEUE_DELAY = 2000
,ON_FAILURE = CONTINUE)

go

ALTER SERVER AUDIT [ServerAudit] WITH(STATE=ON)

Luego tuve que configurar el conjunto de acciones que necesitaba capturar, y por cada una de las tablas:

go
use
MyBase
go
CREATE DATABASE AUDIT SPECIFICATION BDAudit
FOR SERVER AUDIT[ServerAudit]
ADD (INSERT , UPDATE, DELETE ON dbo.tabla1 BY PUBLIC)
,ADD (INSERT , UPDATE, DELETE ON dbo.tabla2 BY PUBLIC)         
,ADD (INSERT , UPDATE, DELETE ON dbo.tabla3 BY PUBLIC)         
WITH (STATE = ON)

Con esto pensaba que ya tenia todo listo y preparado para auditar mi BD, pero no contaba con que el audit no almacena los DATOS que se están modificando. No me crees?? (yo no me creería si lo leyera :p) así que prueba creando un stored procedure que grabe en una tabla y ejecútalo y mira si te almaceno los datos que insertaste.

para ver la información basta con ejecutar esto:

select * from fn_get_audit_file(‘C:*’, default, default)

Audit

Conclusión de Parte I: Ya pude capturar los eventos ejecutados en la BD, y sin programar 🙂 , además de la hora que se realiza y el usuario que lo ejecuta, el problema es que no me dice cuales son los datos que cambiaron.

Parte II

Seguí revisando en las características de SQL2008 y me encontré con un tema interesante: Change Data Capture (CDC), esto es realmente genial, porque internamente SQL crea tablas alternas a las reales y graba la información que va cambiando y lo mejor de todo es que es ASINCRONO, esto lo hace ser mas rápido.

para esto ejecutamos el siguiente script:

use MyBase
go
–habilitamos el cdc
exec sys.sp_cdc_enable_db
go
–Agregamos las tablas a auditar
EXEC sys.sp_cdc_enable_table ‘dbo’, ‘tabla1’, @role_name = NULL,@supports_net_changes =1,@captured_column_list = ‘campo1, campo2, campo3’;

Con esto habremos habilitado el cdc para la tabla “tabla1” y crea automáticamente las siguientes tablas:

cdc_tablas

 

Y en la tabla que termina en CT, podremos encontrar los datos que se han modificado. Tomar nota que el CDC crea 2 Jobs, uno que se estará ejecutando continuamente y otro que se ejecutara una vez al día para eliminar los registros (puedes cambiar el horario del job), como los elimina seria bueno que los almacenaran en otra tabla si es que fuera necesario tener un histórico de los cambios.

El problema con esto es que no almacena el usuario con el cual se hizo el cambio, solo almacena lo datos, por ello debemos combinar esta parte con la anterior, en el audit tengo el nombre del objeto, la hora (SYSUTCDATETIME()) y con ello podemos hacer el join con esta tabla y generar una auditoria mas robusta.

Una vez que termine esto pensé que ya tenia solucionado todo, pero me equivoque porque lo solucione de la parte de la BD pero no de la aplicación.

Conclusión de la Parte II: Ya puedo capturar cualquier evento transaccional en mi BD con los datos que necesito y sin haber programado muchas cosas. El problema ahora será mandar a la BD el usuario correcto. (Con autenticación Windows)

 

Parte III

Tenia una aplicación web que su medio de autenticación era Forms con la siguiente configuración:

<identity impersonate=”true/>

<authorization>
            <deny users=”?/>
        </authorization>
        <authentication mode=”Forms>
            <forms name=”.ASPXAUTHloginUrl=”~/SecurityLoginPage.aspxprotection=”Validationtimeout=”999999“/>
        </authentication>
        <membership defaultProvider=”SecurityMembershipProvideruserIsOnlineTimeWindow=”15“>
            <providers>
… y todo lo demás

La clase SecurityMembershipProvider me validaba el login con el Active Directory, le pasaba el token para que lo validara y dejara entrar al usuario. (es un ambiente intranet)

Para poder grabar el usuario en los casos anteriores debía de tener una conexión integrada con el usuario de Windows es decir:

<add name=”tmpConnectionStringconnectionString=”Data Source=MyServer;Initial Catalog=MyBase;Integrated Security=true;providerName=”System.Data.SqlClient“/>

Pero como la autenticación era vía Forms el usuario que iba a BD era el usuario de la maquina y no el usuario que valido el Active directory en mi pagina de login.

Significa que cualquier cambio en la paginas de Ajax y ASPXs capturaba el usuario de la maquina (realmente el que pasaba el usuario era el explorador de internet), para ello tuve que cambiar la identidad y el Windows principal del hilo de la aplicación con el siguiente código:

 

internal class BLSecurityPrincipal : IPrincipal
    {
       IIdentity m_User;
       IPrincipal m_OldPrincipal;
       static bool m_ThreadPolicySet = false;

       BLSecurityPrincipal(IIdentity user)
       {
          m_OldPrincipal = Thread.CurrentPrincipal;         
          m_User = user;         
          Thread.CurrentPrincipal = this;
       }

       static public void Attach(IIdentity user)
       {
           Attach(user, false);
       }

       static public void Attach(IIdentity user, bool cacheRoles)
       { 
          IPrincipal customPrincipal = new BLSecurityPrincipal(user);

          if(m_ThreadPolicySet == false)
          {
             AppDomain currentDomain = AppDomain.CurrentDomain;
             currentDomain.SetThreadPrincipal(customPrincipal);
             m_ThreadPolicySet = true;
          }         

       }

       public void Detach()
       {
          Thread.CurrentPrincipal = m_OldPrincipal;
       }

       public IIdentity Identity
       {
          get { return m_User; }
       }

       bool IPrincipal.IsInRole(string role)
       {
           throw new NotImplementedException();
       }

    }

Con el código de arriba sobrescribo el User.Identity que se tenga en la aplicación. Pero no es suficiente para que la conexión a la BD tome ese usuario, porque es necesario también reemplazar el System.Security.Principal.WindowsIdentity de la aplicación. Para poder solucionar este tema puse el siguiente código:

    internal class BLWindowsImpersonate
    {
        public const int LOGON32_LOGON_INTERACTIVE = 2;
        public const int LOGON32_PROVIDER_DEFAULT = 0;

        WindowsImpersonationContext impersonationContext;

        [DllImport(“advapi32.dll“)]
        public static extern int LogonUserA(String lpszUserName,
            String lpszDomain,
            String lpszPassword,
            int dwLogonType,
            int dwLogonProvider,
            ref IntPtr phToken);
        [DllImport(“advapi32.dll“, CharSet = CharSet.Auto, SetLastError = true)]
        public static extern int DuplicateToken(IntPtr hToken,
            int impersonationLevel,
            ref IntPtr hNewToken);

        [DllImport(“advapi32.dll“, CharSet = CharSet.Auto, SetLastError = true)]
        public static extern bool RevertToSelf();

        [DllImport(“kernel32.dll“, CharSet = CharSet.Auto)]
        public static extern bool CloseHandle(IntPtr handle);

        public bool impersonateValidUser(String userName, String domain, String password)
        {
            WindowsIdentity tempWindowsIdentity;
            IntPtr token = IntPtr.Zero;
            IntPtr tokenDuplicate = IntPtr.Zero;

            if (RevertToSelf())
            {
                if (LogonUserA(userName, domain, password, LOGON32_LOGON_INTERACTIVE,
                    LOGON32_PROVIDER_DEFAULT, ref token) != 0)
                {
                    if (DuplicateToken(token, 2, ref tokenDuplicate) != 0)
                    {
                        tempWindowsIdentity = new WindowsIdentity(tokenDuplicate);
                        impersonationContext = tempWindowsIdentity.Impersonate();
                        if (impersonationContext != null)
                        {
                            CloseHandle(token);
                            CloseHandle(tokenDuplicate);
                            return true;
                        }
                    }
                }
            }
            if (token != IntPtr.Zero)
                CloseHandle(token);
            if (tokenDuplicate != IntPtr.Zero)
                CloseHandle(tokenDuplicate);
            return false;
        }

        public void undoImpersonation()
        {
            impersonationContext.Undo();
        }

    }

Con esto ya podía suplantar el usuario que me daba el browser por el usuario que entro a la aplicación, es decir si abría un explorador de internet en una maquina X y tenia 10 usuarios entrando en distinto tiempo a la aplicación desde esa maquina no podía identificarlos (solo podía identificar al usuario que se había logeado en la maquina), pero con este código ya puedo identificar a los usuario uno por uno.

Conclusión de la Parte III: Ya puedo identificar al usuario que se loguea y mandarlo a SQL para que haga su trabajo (mas bien que haga lo que le configure en los primeros pasos), con esto pensé que ya había terminado pero no fue así 🙁 … no funciona para mis paginas de Silverlight.

 

Parte IV

Lo que parecía que iba a salir fácil, se complicó, la razón: Silverlight + WCF, el problema es que no se puede pasar la identidad a la aplicación de Silverlight, NO SE PUEDE, pueden verificarlo acá: http://msdn.microsoft.com/en-us/library/dd744835(VS.95).aspx

Específicamente en esta parte:

The Silverlight client-side ServiceReferences.ClientConfig configuration file (that is generated by the Add Service Reference tool or by using the Service Model Proxy Generation Tool (SLsvcutil.exe) for SOAP services that use Windows authentication) may specify the TransportCredentialOnly security mode. However, in Silverlight 3, this mode is exactly equivalent to the None security mode. In both modes, Windows credentials are managed by the browser and are not under your application’s control.

El problema es un poco mas grande, porque a parte de no poder tener la credencial en la aplicación de Silverlight, no se lo puedo pasar a mis servicios.

Y bueno hoy después de muchos apuros por fin termine de hacerlo, se que no es una solución optima, pero al menos salí del apuro, de todas maneras me avisan si por ahi uds tienen algo mejor.

Mi solución fue pasar por parámetros a Silverlight el usuario y password (Encriptado por supuesto) y pasarlo como parámetro a mi servicio, e impersonar mi cnx a la BD con ese usuario, o sea, un poco mas despacio:

Primero en Silverlight pasamos los parámetros:

silver

Segundo en el App.xaml.cs

if (e.InitParams[“userName“]!=null)
         UserName = e.InitParams[“userName“];

if (e.InitParams[“ClaveID“] != null)
         ClaveID = e.InitParams[“ClaveID“];

 

Tercero al método de Inserción u modificación le agrego los métodos de usuario y password. Y utilizo mi método de Impersonalizacion del Windows Principal.

service

 

Con esto ya tengo funcionando la captura de usuarios, eventos, datos modificados (el antes y después) y con un poco de código (hubiera esperado que no haya código…)

Aca les paso algunos recursos interesantes:

Audit SQl 2008 –> http://msdn.microsoft.com/en-us/library/dd392015.aspx
CDC –> http://www.databasejournal.com/features/mssql/article.php/3720361/Microsoft-SQL-Server-2008—-Change-Data-Capture–Part-I.htm  y  http://www.kodyaz.com/articles/change-data-capture.aspx
Porque no se puede obtener el usuario con el Change Data Capture? –> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=299164&wa=wsignin1.0

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *