Remote connections for sql server 2005 to one or two ip address.

Question

Remote connections for sql server 2005 to one or two ip address.

zameen555

Grasshopper

Points: 19
More actions
November 15, 2010 at 8:11 pm

#241237

data base engine at a server should only be accessed from one or two ip addresses.
it should not be accessed from any other ip addresses except the selected one.
could it be possible using windows firewall settings and ipsec in windows server 2008.
please anyone provide me with the solution it's urgent.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 738693 More actions · Answer 1

You can set the IPs in the SQL Server Configuration Manager that SQL Server listens on. In the network protocol section.

Lowell SSC Guru Points: 323547 More actions · Answer 2

Steve Jones - SSC Editor (11/16/2010)
You can set the IPs in the SQL Server Configuration Manager that SQL Server listens on. In the network protocol section.

I *think* the OP is asking how to prevent anyone from connecting unless they come from a select list of IP addresses;

I'm sure you can do it witht eh firewall better, so it ignores all traffic except certain IP's but that could have to be done with a logon trigger also, right?

something like this?:(untested...don't lock yourself out copying and pasting this code)

CREATE TRIGGER trigLogon_CheckForIPAddress

ON ALL SERVER

FOR LOGON

AS

BEGIN

IF NOT EXISTS (

SELECT

client_net_address AS ipaddress

FROM sys.dm_exec_connections

WHERE session_id = @@spid

AND session_id IN('<localhost>','192.168.0.1','192.168.0.2','192.168.0.40') )

BEGIN

RAISERROR('Unauthorized use of login from inpermissible machine IP.', 16, 1)

--prevent connection

ROLLBACK

END

END;

GO

ENABLE TRIGGER trigLogon_CheckForIPAddress ON ALL SERVER

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

zameen555 Grasshopper Points: 19 More actions · Answer 3

could it be possible through windows firewall settings in windows server 2008