عموما محدود كردن دسترسي بر اساس 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
SELECT TOP 100 * FROM [master].[dbo].[Logging] ORDER BY id desc
محدود كردن كاربران بر اساس 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>')"
اين نوع تريگرها در قسمت server objects در management studio ظاهر مي‌شوند.
