.NET Tips: محدود كردن دسترسي به اس كيوال سرور بر اساس IP

عموما محدود كردن دسترسي بر اساس IP بهتر است بر اساس راه حل‌هايي مانند فايروال، IPSec و يا RRAS IP Filter صورت گيرد كه جزو بهينه‌ترين و امن‌ترين راه حل‌هاي ممكن هستند.
در ادامه قصد داريم اين محدوديت را با استفاده از امكانات خود اس كيوال سرور انجام دهيم (بلاك كردن كاربران بر اساس IP هاي غيرمجاز). مواردي كه در ادامه ذكر خواهند شد در مورد اس كيوال سرور 2005 ، سرويس پك 2 به بعد و يا اس كيوال سرور 2008 صادق است.
اس كيوال سرور اين قابليت را دارد كه مي‌توان بر روي كليه لاگين‌هاي صورت گرفته در سطح سرور تريگر تعريف كرد. به اين صورت مي‌توان تمامي لاگين‌ها را براي مثال لاگ كرد (جهت بررسي مسايل امنيتي) و يا مي‌توان هر لاگيني را كه صلاح ندانستيم rollback نمائيم (ايجاد محدوديت روي لاگين در سطح سرور).

لاگ كردن كليه لاگين‌هاي صورت گرفته به سرور

ايجاد جدولي براي ذخيره سازي اطلاعات لاگين‌ها:


USE [master]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Logging](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [LogonTime] [datetime] NULL,
 [LoginName] [nvarchar](max) NULL,
 [ClientHost] [varchar](50) NULL,
 [LoginType] [varchar](100) NULL,
 [AppName] [nvarchar](500) NULL,
 [FullLog] [xml] NULL,
CONSTRAINT [PK_IP_Log] PRIMARY KEY CLUSTERED
(
 [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Logging] ADD  CONSTRAINT [DF_IP_Log_LogonTime]  DEFAULT (getdate()) FOR [LogonTime]
GO

در ادامه يك تريگر لاگين را جهت ذخيره سازي اطلاعات كليه لاگين‌ها به سرور ايجاد مي‌نمائيم:

USE [master]
GO

CREATE TRIGGER LogonTrigger
ON ALL SERVER
FOR LOGON
AS
BEGIN
   DECLARE @data XML   
   SET @data = EVENTDATA()
  
   INSERT INTO [Logging]
     (
       [LoginName],
       [ClientHost],
       [LoginType],
       [AppName],
       [FullLog]
     )
   VALUES
     (
       @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(max)'),
       @data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)'),
       @data.value('(/EVENT_INSTANCE/LoginType)[1]', 'varchar(100)'),
       APP_NAME(),
       @data
     )
END

اكنون براي مثال از آخرين 100 لاگين انجام شده، به صورت زير مي‌توان گزارشگيري كرد:


SELECT TOP 100 *  FROM [master].[dbo].[Logging] ORDER BY id desc

و بديهي است در تريگر فوق مي‌توان روي هر كدام از آيتم‌هاي دريافتي مانند ClientHost و غيره فيلتر ايجاد كرد و تنها موارد مورد نظر را ثبت نمود.

محدود كردن كاربران بر اساس IP

ClientHost ايي كه در رخ‌داد لاگين فوق بازگشت داده مي‌شود همان IP كاربر راه دور است. براي فيلتر كردن IP هاي غيرمجاز، ابتدا در ديتابيس مستر يك جدول براي ذخيره سازي IP هاي مجاز ايجاد مي‌كنيم و IP هاي كليه كلاينت‌هاي معتبر خود را در آن وارد مي‌كنيم:


USE [master]
GO
CREATE TABLE [IP_RESTRICTION](
 [ValidIP] [varchar](15) NOT NULL,
CONSTRAINT [PK_IP_RESTRICTION] PRIMARY KEY CLUSTERED
(
 [ValidIP] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

سپس تريگر لاگين ما براي منع كاربران غيرمجاز بر اساس IP ، به صورت زير خواهد بود:


USE [master]
GO

CREATE TRIGGER [LOGIN_IP_RESTRICTION]
     
ON ALL SERVER
FOR LOGON
AS
BEGIN           
   DECLARE @host NVARCHAR(255);        
   SET @host = EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(max)');
                         
   IF (
          NOT EXISTS(
              SELECT *
              FROM   MASTER.dbo.IP_RESTRICTION                       
              WHERE  ValidIP = @host
          )
      )               
   BEGIN
          ROLLBACK;               
   END
END;

اخطار مهم!
تريگر فوق خطرناك است! ممكن است خودتان هم ديگر نتوانيد لاگين كنيد!! (حتي با اكانت ادمين)
بنابراين قبل از لاگين حتما IP لوكال و يا ClientHost لوكال را هم وارد كنيد.
اگر گير افتاديد به صورت زير مي‌شود رفع مشكل كرد:
تنها حالتي كه تريگر لاگين را فعال نمي‌كند Dedicated Administrator Connection است يا DAC هم به آن گفته مي‌شود. به صورت پيش فرض براي ايجاد اين اتصال اختصاصي بايد به كامپيوتري كه اس كيوال سرور بر روي آن نصب است به صورت لوكال لاگين كرد و سپس در خط فرمان دستور زير را صادر كنيد (حرف A آن بايد بزرگ باشد):


C:\>sqlcmd -A -d master -q "insert into IP_RESTRICTION(validip) values('<local machine>')"

به اين صورت local machine به جدول IP هاي مجاز اضافه شده و مي‌توانيد لاگين كنيد!
اين نوع تريگرها در قسمت server objects در management studio ظاهر مي‌شوند.

۱۳۸۸/۰۲/۱۰

محدود كردن دسترسي به اس كيوال سرور بر اساس IP