Imagínenos que hemos creados una base de datos con un nombre de usuario y contraseña para que nuestra aplicación ASP.Net (VB o C#) se conecte pero no queremos que con estos datos de acceso un usuario pueda a través de Microsoft SQL Management Studio ingresar a manipular la base de datos, como tampoco queremos que vea la tabla Master.
Para esto debemos crear un trigger para el acceso de la siguiente forma:

CREATE TRIGGER [TR_LOGON_APP]
ON ALL SERVER
FOR LOGON
AS
BEGIN

DECLARE @program_name nvarchar(128)
DECLARE @host_name nvarchar(128)

SELECT @program_name = program_name,
@host_name = host_name
FROM sys.dm_exec_sessions AS c
WHERE c.session_id = @@spid

IF ORIGINAL_LOGIN() IN(‘EL_NOMBRE_DE_LA_APP’)
AND @program_name LIKE ‘%Management%Studio%’
BEGIN
RAISERROR(‘Este nombre de usuario es solo para aplicaciones.’,16,1)
ROLLBACK;
END
END;

Pero debemos tener en cuenta que el inicio de sesión con Trigger, no es 100% fiable ya que solo funciona en el caso de que el usuario no sepa cambiar su cadena de conexión para un nombre de aplicación; por lo que es conveniente en casos de requerir extrema seguridad.
En estos casos es recomendable permitir conexiones desde el servidor web solamente, solo para el nombre de usuario y sesión.

Nota: Hay que tener mucho cuidado en la creación de Triggers para inicio de sesión, ya que cualquier error puede bloquearlo y no dejarlo conectarse nuevamente. Si esto le llega ocurrir debe ingresar con DAC (Conexión de administración dedicada) y poder deshabilitar/eliminar el Trigger. DAC puede habilitar desde sp_configure

EXEC sp_configure ‘remote admin connections’, 1;
GO
RECONFIGURE;

Aquí tenemos un Segundo Trigger que se puede utilizar.

IF EXISTS(
SELECT * FROM master.sys.server_triggers
WHERE parent_class_desc = ‘SERVER’ AND name = N’Allow_only_Application_Login_Trigger’)
DROP TRIGGER [Allow_only_Application_Login_Trigger] ON ALL SERVER
GO

CREATE TRIGGER Allow_only_Application_Login_Trigger
ON ALL SERVER WITH EXECUTE AS ‘sa’
FOR LOGON
AS
BEGIN

DECLARE @data XML
SET @data = EVENTDATA()

DECLARE @AppName sysname
,@LoginName sysname
,@LoginType sysname
,@HostName sysname

SELECT @AppName = [program_name]
FROM sys.dm_exec_sessions
WHERE session_id = @data.value(‘(/EVENT_INSTANCE/SPID)[1]’, ‘int’)

SELECT @LoginName = @data.value(‘(/EVENT_INSTANCE/LoginName)[1]’, ‘sysname’)
,@LoginType = @data.value(‘(/EVENT_INSTANCE/LoginType)[1]’, ‘sysname’)
,@HostName = @data.value(‘(/EVENT_INSTANCE/ClientHost)[1]’, ‘sysname’)

IF @AppName not in (‘MyProductionApplicationName1’, ‘MyReportingApplicationName2’, ‘Microsoft SQL Server Management Studio’, ‘Microsoft SQL Server Management Studio – Query’)
BEGIN
ROLLBACK; –Cerrar la sesión

–Log the exception to our Auditing table
INSERT INTO master.dbo.loginAuditTable(data, program_name)
VALUES(@data, @AppName)
END
END;

Ejemplos de cadenas de conexión:

OLEDB

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=ProdServer1;Application Name=MiProduccionAppliaccion1
SQL Native Client

Integrated Security=SSPI;Persist Security Info=False;User ID=»»;Initial Catalog=master;Data Source=ProdServer1;Application Name=MiAplicaciondeReporte2

Entradas relacionadas:

(Visitado 1.894 veces, 1 visitas hoy)