December 7, 2010 at 2:24 pm
Can I configure SQL Server to allow or deny certain connections depending on IP address? I think MySQL does that. Where can I look to see what machines have been making connections to a SQL Server instance or to a particular database?
Thanks.
December 7, 2010 at 2:33 pm
You can Use
1. Activity Monitor
2. SQL Server Profiler
Thank You,
Best Regards,
SQLBuddy
December 7, 2010 at 2:36 pm
You can block the IP address uisng the Firewall not explicitly by the SQL Server.
Please check the following link
http://blogs.msdn.com/b/sql_protocols/archive/2006/04/10/572605.aspx
Thank You,
Best Regards,
SQLBuddy
December 7, 2010 at 2:43 pm
the windows firewall is the best place to do the blocking; it's also possible that you could create a logon trigger and block by Ip/hostname inside that trigger, if you really had to do it by SQL and not the OS.
Lowell
December 7, 2010 at 8:07 pm
Oh thanks Lowell and S.Buddy. Those are great suggestions. I'm going to look into that trigger. It ought to work nicely with SQL Server authentication. When I use the profiler, I can probably filter on just a couple of event types to limit the size of the trace.
December 8, 2010 at 5:22 am
here's a prototype to start testing with; i don't know what happens when people connect with named pipes; i assume the ip address is null, but you'll want to check that out.
be careful with this...you might lock yourself out of the server you are testing, or really doink things up....test it first!
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 ISNULL(client_net_address ,'Named Pipes?') IN('<localhost>','Named Pipes?','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
December 8, 2010 at 10:30 pm
Isn't this a bit like cracking a nut with a freight train.... Hard coded bits like this, unless extremely well documented, become lost in the fullness of time. Further IP's, like phone numbers, can be changed. This is a development and DBA nighmare doing it this way. Just my opinion, but MS have done an amazing job of security without this type of action.
CodeOn
😛
December 9, 2010 at 2:13 am
And I would agree that the Windows Firewall is the place to be putting rules like this anyway, rather than hacking SQL to do the job...
December 10, 2010 at 4:04 am
you might just want to use Sql Server as your firewall
why not
December 10, 2010 at 7:04 pm
Uhhh. Because it's a SQL server NOT a firewall. What Idiot would expose a SQL server to the wilds of the internet for it to act as a firewall.. I would like to say this could work BUT IT WON'T..
December 11, 2010 at 6:57 am
Malcolm Daughtree (12/10/2010)
Uhhh. Because it's a SQL server NOT a firewall. What Idiot would expose a SQL server to the wilds of the internet for it to act as a firewall.. I would like to say this could work BUT IT WON'T..
not the internet; the intranet/internal network.
I've seen other posts like this where they wanted to prevent developers who had access to the username/password to be prevented from accessing the db;i one way to do that is to allow, say only the web server's IP address, along with, say, a couple of administrative machines to allow access.
it's not an unusual request when you put it into scope;
Lowell
December 11, 2010 at 1:59 pm
It's really a matter of preventing someone from accidentally using the connection string for the production database. A dba or sysadmin should have sole access to the login credentials but the company has no dedicated dba. So the credentials are out there in various web or app config files. Accidents happen and they can require years of therapy.
December 12, 2010 at 3:56 pm
And then someone says this
"the company has no dedicated dba. So the credentials are out there in various web or app config files." And you wonder why hackers are just LTAO (Laughing their Asses off) !
There is a reason MS makes things like ISA and ISS and there is a network thingy called a DMZ. IP hacks are the worst, most easily intercepted, easliy circumvented security protocol any one can use. And if you are running ISA, IIS and SQL server on the same machine, see me your hardware, for surely somebody out there has already lined you up for a DOS or an Injection attack.
December 12, 2010 at 9:37 pm
Lowell (12/11/2010)
Malcolm Daughtree (12/10/2010)
Uhhh. Because it's a SQL server NOT a firewall. What Idiot would expose a SQL server to the wilds of the internet for it to act as a firewall.. I would like to say this could work BUT IT WON'T..not the internet; the intranet/internal network.
I've seen other posts like this where they wanted to prevent developers who had access to the username/password to be prevented from accessing the db;i one way to do that is to allow, say only the web server's IP address, along with, say, a couple of administrative machines to allow access.
it's not an unusual request when you put it into scope;
And I would stIll use windows firewall/windows security to do this. You don't need to do it from inside of the Application, just do it on the server that is hosting it.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 14, 2010 at 5:29 am
perhaps somebody who knows what they are talking about can explain which specific security failings of SQL Server make it unsuitable for use as a firewall
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply