SQL SERVER-ile ligipääsu piiramine IP aadressi põhiselt EVENTDATA() XML andmeid kasutades

Kui on ASP.NET veebirakendus istub serveril koos teiste SQL SERVER baasi kasutavate rakendustega ja SQL SERVER-i port on väljast lahti kuid enda rakendusel pöördumine andmebaasi käib ainult läbi veebirakenduse võib kasutada SQL SERVER-i LOGON trigerit, et piirata mujalt rakenduse andmebaasile ligipääsu. DDL LOGON trigeris uurib, kust ühenduti ja seejärel otsustab. Mõjub ainult nendele SQL LOGIN-ite mille vaikimisi andmebaas langeb kokku trigeris määratuga.
Nõuab SQL SERVER 2005 SP2 vähemalt. RAISERROR veateated lähevad ainult Serveri Application Event logisse. Kui IP aadress ei sobi saab sellise veateate:
Logon failed for login 'USERNAME' due to trigger execution


ALTER TRIGGER [KONTROLL_LOGON_CheckIP]
ON ALL SERVER
WITH EXECUTE AS 'sa' --kasutaja peab olema sysadmin fixed server role või ALTER TRACE õigusega kuna RAISERROR WITH LOG kasutame
FOR LOGON
AS
BEGIN
DECLARE @ev XML, @usr NVARCHAR(50), @ip NVARCHAR(35), @lubat INT, @defdb NVARCHAR(128)
SET @defdb='ABAASINIMI' --andmebaasi nimi, kus meil rakendus serveril sees on
SET @ev=(SELECT EVENTDATA())
SELECT @ip = @ev.value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(35)') --kliendi IP aadress
,@usr=@ev.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(50)') --kasutajanimi

IF @ip IS NULL --kui ühendutakse mingi muu protokolliga
RETURN



-- --uurime et on tegu SQL Loginiga ja on tegu LOGON tüüpi sündmusega
IF (SELECT @ev.value('(/EVENT_INSTANCE/LoginType)[1]', 'NVARCHAR(15)')) = 'SQL Login' --piirame ainult SQL Loginite ligipääsu
AND (SELECT @ev.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(15)')) = 'LOGON'
BEGIN
--kontrollime, kas on üldse ligipääs lubatud määratud andmebaasi, loginil peab olema @defdb baas määratud vaikimisi andmebaasiks
SET @lubat = (SELECT is_disabled FROM sys.sql_logins WITH (NOLOCK) WHERE name=@usr AND default_database_name=@defdb)
IF @lubat = 0 --kui antud SQL SERVER loginil on vaikimisi andmebaasile ligipääs lubatud
BEGIN
IF @ip NOT IN ('10.0.40.8') --Siia sulgudesse need IP aadressid, kust on lubatud ligipääs andmebaasile
BEGIN
RAISERROR('Ligipääs andmebaasile %s IP aadressilt %s on kasutajale %s keelatud',10,1,@defdb,@ip,@usr) WITH LOG --ka infona ka serveri Event Aplication Logi
ROLLBACK
--kui vaja kuskilegi kirja panna, et ligi ei saadud
--INSERT INTO [test].[dbo].[LOGI] (IPA, [EVENT]) VALUES (@ip,@ev)
RETURN

END
END
ELSE --kasutajal puudub vaikimisi juurdepääs määratud andmebaasile, mingi muu kasutaja
BEGIN
RAISERROR('Ligipääs vaikimisi andemebaasile %s IP addressilt %s on kasutajale %s keelatud',10,1,@defdb,@ip,@usr) WITH LOG --ka infona ka serveri Event Aplication Logi
ROLLBACK
RETURN
END
END

END

Triger ei rakendu, kui ühendutakse Dedicated Administrator Connection-i kaudu

Päring teada saamaks, kes ja kuidas sul SQL SERVER-i küljes on

SELECT s.session_id
, c.connect_time
, s.login_time
, s.login_name, s.original_login_name
, c.protocol_type
, c.client_net_address
, c.client_tcp_port
, c.auth_scheme
, s.HOST_NAME
, s.program_name, s.client_interface_name
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id

Blogged with the Flock Browser