SQL 2005 sql login ip restriction

Question

Is there a way to restrict a specific sql 2005 login on a Microsoft SQL Server 2005, standard version (sql is in mixed mode) to specific IP adresses, while other logins, windows authentated ones, are unaffected?

Answer 1

I have restricted logins on my system to a single IP address, just use the firewall.

Answer 2

SQL Server can be mapped to serveral port/IP configurations by the Server Manager, but afaik there isn't a way to bind a login method to a port/ip.

The only thing I can think of is to create a trigger FOR LOGON and inside that trigger test the Event Data like:

SELECT EVENTDATA().value ('(/EVENT_INSTANCE/LoginType)','nvarchar(max)'), EVENTDATA().value ('(/EVENT_INSTANCE/ClientHost)','nvarchar(max)')

...and maybe calling RAISEERROR or ROLLBACK if you don't like what you find might terminate the connection?

Hope this helps

Answer 3

Why do you need to restrict by IP address? If all your users are authenticated, just set up group permissions on SQL Server, and allow or deny the groups you want.

If the problem is various users using applications with the same SQL login (you mentioned you're using mixed-mode), then the question is why do you want to allow some users using the applications to access SQL Server, and not allow others? Implement the security in the applications, don't bounce them at the database level.

Answer 4

Yes, you can do this. You'll need to write a logon trigger as described here:

http://technet.microsoft.com/en-us/library/bb326598.aspx

I futzed around with it for the last fifteen minutes and had limited success, but maybe there's somebody else on here who's done a DDL logon trigger filtering by IP address that can show the source code.

Answer 5

I use the following trigger to restrict access to specific login - IPs combo.

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(EXISTS(SELECT * FROM master.dbo.IP_RESTRICTION 
  WHERE UserName = SYSTEM_USER))
 BEGIN
  IF(NOT EXISTS(SELECT * FROM master.dbo.IP_RESTRICTION 
   WHERE UserName = SYSTEM_USER AND ValidIP = @host))
  BEGIN
   ROLLBACK;
  END
 END
END;

DDL for table IP_RESTRICTION:

CREATE TABLE [dbo].[IP_RESTRICTION](
 [UserName] [varchar](255) NOT NULL,
 [ValidIP] [varchar](15) NOT NULL,
 [Comment] [nvarchar](255) NULL,
 CONSTRAINT [PK_IP_RESTRICTION] PRIMARY KEY CLUSTERED 
([UserName] ASC, [ValidIP] ASC) ON [PRIMARY]
) ON [PRIMARY]