SQL 2005 sql login ip restriction

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?

From stackoverflow

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

• 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

• 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.

  gerleim : I don't "want" to, but it's a mixture off applications, and I have no control over those. These apps are accessing the DB with an sql login, without possibility to configure. So I want to ensure that the apps are still accessing the db, while any other sources, like developer desktops, are denied.

  Rick : Are the apps PC or server based? If they are PC based, then the apps will come from the users' computers, and so you can't filter by IP. If they are server based (e.g. on a corporate intranet), then hopefully the users don't have the database username/password, so they have no access to the db.

• 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.

• 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]