Hace un tiempo, publiqué un artículo sobre cómo crear una lista blanca para acceder a SQL Server. Desde entonces, he recibido comentarios de que no funcionaba como se esperaba. Mis disculpas por tardar tanto, pero creo que he encontrado una solución.

El problema principal con la implementación anterior era que el desencadenador bloqueaba algunos o incluso todos los accesos al servidor después de ser creado. Resulta que los problemas estaban relacionados con la denegación de permisos.

Para ver la solución en acción, creemos todo utilizando el código original del artículo anterior. Agregaremos una fila a la tabla de lista blanca que debería permitir a todos los usuarios de la estación de trabajo “ECHOBASE1” el acceso, independientemente de su dirección IP.

USE master;
GO

INSERT dbo.WhiteList(LoginName, HostName, HostIpAddress) 
VALUES ('*', 'ECHOBASE1', '*');
GO

A continuación, crearemos un inicio de sesión de SQL con solo permiso de conexión al servidor y nada más.

CREATE LOGIN LogonTriggerTest WITH PASSWORD = 'Password1';
GO

Finalmente, abriremos una nueva ventana de consulta utilizando ese inicio de sesión. Como puedes ver, se nos deniega el acceso al servidor debido al desencadenador de inicio de sesión. Si miramos en el Errorlog, podemos ver que nos falta el permiso VIEW SERVER STATE. Este fue mi primer error. Realicé mis pruebas iniciales utilizando un inicio de sesión administrativo. La mayoría de los usuarios no son administradores; por lo tanto, no tendrán el permiso necesario para ver sys.dm_exec_connections. Estaba utilizando esta DMV para obtener la dirección IP de la conexión del cliente, y requiere el permiso VIEW SERVER STATE.

Para solucionar esto, podemos usar la función CONNECTIONPROPERTY, ya que no requiere permisos adicionales.

SELECT CONNECTIONPROPERTY('client_net_address');
GO

Ahora intentemos conectarnos nuevamente. Una vez más, fallamos. Este sería mi segundo error. No otorgé acceso SELECT a la tabla de lista blanca. Por defecto, un usuario tendrá permiso público en la base de datos master, pero no tendrá permiso para la tabla. Para solucionar esto, podemos otorgar permiso al rol de base de datos público. Esto permitirá que cualquier usuario autenticado lea desde la tabla de lista blanca.

USE master;
GO

GRANT SELECT ON dbo.WhiteList TO public;
GO

Finalmente, nuestra conexión a SQL Server es exitosa. Usando el mismo código del desencadenador, podemos compararlo con lo que está en la tabla de lista blanca.

USE master;
GO

SELECT ORIGINAL_LOGIN() AS 'LoginName',
       HOST_NAME() AS 'HostName',
       CONNECTIONPROPERTY('client_net_address') AS 'HostIpAddress';
GO

SELECT * FROM dbo.WhiteList;
GO

Si hubiera seguido mis propias reglas, podría haber descubierto la mayoría de estos problemas antes de publicar el artículo original. El código completamente actualizado se proporciona a continuación. Por favor, avísame si encuentras algún otro problema con esta nueva versión. También he agregado otra columna a la tabla de lista blanca que se puede usar para agregar comentarios. La idea es proporcionar documentación sobre lo que el elemento de la lista blanca está intentando hacer.

USE master;
GO

IF OBJECT_ID('dbo.WhiteList') IS NOT NULL
    DROP TABLE dbo.WhiteList;
GO

CREATE TABLE dbo.WhiteList (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    LoginName VARCHAR(255),
    HostName VARCHAR(255),
    HostIpAddress VARCHAR(50),
    Comments VARCHAR(2000)
);
GO

GRANT SELECT ON dbo.WhiteList TO PUBLIC;
GO

INSERT dbo.WhiteList(LoginName, HostName, HostIpAddress, Comments)
VALUES ('*', 'ECHOBASE1', '*', 'Se permite que cualquier usuario de la estación de trabajo "ECHOBASE1" se conecte, independientemente de la dirección IP.'),
       ('WebSiteLogin', 'webserver1', '192.168.100.55', 'Solo se permite el acceso al WebSiteLogin desde webserver1 con una IP de 192.168.100.55.');
GO

CREATE TRIGGER WhiteListTrigger ON ALL SERVER FOR LOGON AS
BEGIN
    DECLARE @LoginName VARCHAR(255) = ORIGINAL_LOGIN(),
            @HostName VARCHAR(255) = HOST_NAME(),
            @HostIpAddress VARCHAR(50) = CONVERT(VARCHAR(50), CONNECTIONPROPERTY('client_net_address'));

    IF (
        SELECT COUNT(*)
        FROM dbo.WhiteList
        WHERE (
            (LoginName = @LoginName)
            OR (LoginName = '*')
        )
        AND (
            (HostName = @HostName)
            OR (HostName = '*')
        )
        AND (
            (HostIpAddress = @HostIpAddress)
            OR (HostIpAddress = '*')
        )
    ) = 0
    ROLLBACK;
END;
GO

Espero que esta versión actualizada de la implementación de la lista blanca resuelva los problemas que se encontraron. Recuerda probar exhaustivamente y otorgar los permisos necesarios para evitar cualquier problema de acceso. No dudes en comunicarte si tienes alguna otra pregunta o encuentras algún otro problema.