Desencadenadores de inicio de sesión y correo de base de datos

Por: Pablo Dueñas | Servicios Profesionales Danysoft

Introducción
Uno de los usos de los desencadenadores de inicio de sesión, «logon triggers», es que nos avise mediante correo electrónico cuando se producen determinado condiciones. Sin embargo, para llegar a buen puerto se necesitan unos pasos especiales que vamos a analizar.

Correo de base de datos
Una sorpresa que se produce cuando tratamos de ser informados de condiciones de inicio de sesión, es que empezamos a recibir decenas o cientos de correos. Esto en principio es una sorpresa, pero tiene su lógica. Para ello vamos a seguir el proceso de inicio de una sesión de SSMS mediante la traza. Aquí la tenemos:

Como vemos en la columna EventClass, solo se ha pedido seguir los eventos de inicio de sesión.

En la siguiente columna vemos que hay tres inicios de SSMS –no vamos a incidir sobre esto pues está aclarado en otros artículos– y una entrada de correo de base de datos. En realidad debería haber 3 entradas de correo de base de datos, pero en el ejemplo que nos ocupa solo se permitió la primera. Esto nos permite entender que, sin esta parada al envío de correo electrónico, se habría producido un efecto en cascada responsable de que recibamos los cientos de correos.

Es decir, el primer inicio de sesión –y con SSMS se producen 3 por los distintos componentes del entorno– produce otro inicio de sesión para enviar el correo. Este inicio de sesión para enviar el correo, obviamente dispara su desencadenador que genera otro envío de correo y por tanto otro inicio de sesión que dispara otro desencadenador y así hasta que lo paramos.

Si vemos las siguientes columnas, comprobamos que el inicio de sesión procede del servicio SQL Server por el nombre del inicio de sesión –LoginName– y por el identificador de proceso –ClientProcessID–, que cada inicio de sesión tiene su propia conexión –lógico, aunque es oportuno señalarlo– y que SSMS usa «pool» de conexiones.

También vemos por qué recibimos tantos correos: si nos fijamos en la hora de los dos primeros inicios de sesión, vemos que es muy rápido, en la misma milésima de segundo. Obviamente, según vayan encolándose correos, aumenta la actividad del servidor y se vuelve más lento, pero es un proceso rápido, pues los correos se encolan y luego ya se irán enviando. Por esto, además, minutos después de parar el desencadenador se siguen recibiendo correos, porque SQL Server, en una forma muy inteligente, separa el proceso de encolar un correo del proceso de enviarlo que es más costoso.

Pasos desencadenador
Según hemos explicado, por tanto, uno de los primeros pasos del desencadenador debe ser verificar que no es una consecuencia de nuestro correo. Hemos visto que una forma de hacerlo es por el nombre de aplicación, que una vez más viene en nuestra ayuda para determinar el origen de las conexiones.

IF APP_NAME() LIKE ‘DatabaseMail%’

RETURN;

En este punto tenemos que volver a aclarar que, si hacemos las pruebas en SSMS y ponemos este filtro, vamos a recibir tres correos siempre, por las tres conexiones que realiza el entorno. En la imagen que he puesto, se cortó el envío por otro procedimiento para el estudio.

A partir de aquí ya podemos hacer el resto de verificaciones. Por ejemplo, si queremos tener un aviso inmediato de cuándo se usa un inicio de sesión por un seguimiento especial, podemos filtrar exclusivamente por dicho inicio:

IF ORIGINAL_LOGIN() <> ‘login_test’

RETURN;

Por poner otro ejemplo, si buscamos un origen especial, podemos mirar también el nombre de aplicación o el nombre del cliente –que no siempre se obtiene del sistema operativo, sino que la conexión puede ponerlo–:

IF HOST_NAME() NOT IN (‘<NombreCliente>’)

RETURN;

Datos del evento
En el caso de que queramos usar la función EVENTDATA(), si se va a rechazar la conexión, hay que guardar antes los datos en una variable. Es decir, obtener los datos cuando el contexto del inicio de sesión sigue existiendo:

DECLARE @ED XML;
SET @ED = EVENTDATA();

ROLLBACK;

Estos son los datos que devuelve la función para un evento de inicio de sesión:

<EVENT_INSTANCE>

<EventType>LOGON</EventType>

<PostTime>2014-08-07T19:46:20.503</PostTime>

<SPID>53</SPID>

<ServerName>SERVERINSTANCE</ServerName>

<LoginName>DOMAINUSER</LoginName>

<LoginType>Windows (NT) Login</LoginType>

<SID>AQUAAAAAAAUVAAAAVBmDfa8Y/Tg0IudbbK0AAA==</SID>

<ClientHost>&lt;local machine&gt;</ClientHost>

<IsPooled>0</IsPooled>

</EVENT_INSTANCE>

Aunque muchos de estos datos los podemos obtener de funciones de SQL Server, los tenemos aquí agrupados en nuestra variable XML. Para separarlos usamos la función value(), pidiendo la ruta de cada etiqueta y, aunque solo haya una etiqueta por evento, especificando que se quiere la primera para que no falle la función, pues asume que se van a devolver varias. Por ejemplo:

SET @Cuerpo = N'<h3>Conexión detectada:</h3>’

+ N'<ul>’

+ N'<li>Hora: ‘ + @ED.value(‘(/EVENT_INSTANCE/PostTime)[1]’, ‘NVARCHAR(100)’) + N'</li>

+ N'<li>Servidor: ‘ + @ED.value(‘(/EVENT_INSTANCE/ServerName)[1]’, ‘NVARCHAR(100)’) + N'</li>’

…

+ N'</ul>’;

De esta manera recibiremos un correo bonitamente formateado cada vez que se cumplan las condiciones de inicio de sesión:

Resumen
Los desencadenadores LOGON son muy útiles cuando queremos documentar o seguir determinadas condiciones de inicio de sesión. Para que recibamos un correo se necesita seguir unos pasos que hemos documentado. El proceso es rápido y no añade apenas tiempo a los múltiples inicios de sesión, ni siquiera si tenemos que enviar correo pues el inicio de sesión en realidad lo único que hace es encolar los datos del correo.