MSSQL Server’da Login Trigger ile Kullanıcı Kısıtlama

Bu makalede mssql server üzerinde herhangi bir kullanıcının veritabanı üzerinde açtığı connection sayısınını sınırlayacağız. Öncele bu işlemi Resource Governor ile yapılabilirmi. SQL Server Resource Governor ile doğrudan bir kullanıcıya maksimum bağlantı (connection) sayısı limiti veremezsiniz. Resource Governor şu kaynakları kontrol işlemi yapmaktadır. Cpu kullanımı, Memory kullanımı, I/O bant genişliği ve sorgulardaki paralellik yapısına bakmaktadır.

Kullanıcıya bağlantı limiti vermenin yolu genelde LOGON TRIGGER ile çözülür.
1. Kullanıcı giriş yaparken tetiklenir
2. O kullanıcıya ait aktif oturum sayısı sayılır.
3. Limit aşılmışsa bağlantı reddedilir.

Burada dikkat edilmesi gereken hayalet oturumların olması trigger işlemi sonucu bağlantı hatası almanıza sebep verecektir. Aşağıdaki komutla tüm sessionları görebiliriz.

SELECT COUNT(*) FROM sys.dm_exec_sessions 
WHERE is_user_process = 1 AND  original_login_name = 'YUNUS'

Connection Pooling: Eğer bir uygulama üzerinden bağlanıyorsanız, uygulama bağlantıyı kapatsa bile SQL Server bağlantıyı bir süre “uyur” (dormant) halde tutabilir. Bu da belirlediğiniz sayı üzerinde görünmesine neden olur.

Aşağıdaki resimde Yunus login’inde bağlantı sayısı 5 üzeriyse Trigger tetiklenmesine sebep olacaktır. 5 üzeri olan login giriş denemelerinde hata mesajıyla karşılaşmış oluruz.

CREATE TRIGGER trg_LimitConnections
ON ALL SERVER
FOR LOGON
AS
BEGIN
DECLARE @LoginName SYSNAME
SET @LoginName = ORIGINAL_LOGIN()
    IF @LoginName = 'YUNUS'
  BEGIN
    IF (
        SELECT COUNT(*)
        FROM sys.dm_exec_sessions
        WHERE is_user_process = 1
          AND original_login_name = 'YUNUS'
    ) > 5-- Değer burada belirtilir.
    BEGIN
        ROLLBACK;
    END
END
END

Oluşturulan Trigger server seviyesinde olduğu için SSMS üzerinde Server Object kısmında bulunan Triggers kısmından server seviyesinde oluşturulan trigger görülebilir.

Aşağıdaki komut ile Server seviyesinde Trigger’ları görebiliriz.

SELECT * FROM sys.server_triggers
WHERE type_desc = 'SQL_TRIGGER';

Eğer trigger hatalı çalışıyor ve bağlantıları engelliyorsa (veya üzerinde değişiklik yapmak istiyorsanız) şu komutları kullanabilirsiniz:

Trigger’ı Geçici Olarak Devre Dışı Bırakmak:

   DISABLE TRIGGER trg_LimitConnections ON ALL SERVER;

Trigger’ı Tekrar Aktif Etmek:

   ENABLE TRIGGER trg_LimitConnections ON ALL SERVER;

Trigger’ı Tamamen Silmek:

   DROP TRIGGER trg_LimitConnections ON ALL SERVER;

Eğer trigger yüzünden sunucuya hiç bağlanamıyorsanız, SQL Server’ı “Minimal Configuration Mode” (-f parametresiyle) başlatarak trigger’ları devre dışı bırakıp sisteme girebilirsiniz. Aynı işlemi DAC ile de yapabiliriz.

Önemli Uyarı: Logon Trigger’lar tehlikelidir. Eğer trigger içinde bir hata oluşursa, ilgili kullanıcının (hatta bazen tüm kullanıcıların) server’a girmesini tamamen engelleyebilirsiniz.

En doğru yaklaşım Application-level connection pool limiti koymaktır. Firewall / Proxy → IP bazlı sınırlama yapılabilir

Bu makalede Login trigger kavramını görmüş olduk. Başka makalede görüşmek dileğiyle…

Boş konuşmalardan kaçının Müminin-3