November 16, 2011 at 8:31 am
I know this is an older thread but it is the BEST article I have found that somewhat covers what I'm attempting to do. Now I freely admit I am NOT a SQL expert so I am coming to those that are for some help. And if there is a book or something out there that would help me I am more than willing to purchase it.
Scenario: Platform with multiple web/app servers. Two SQL Servers that host multiple SQL instances (one per web/app server). Direct RDP access to the SQL Servers is restricted even within the network (so Admins have access but regular Support folks will use SSMS from the web/app servers if they need to get in to the application's databases).
What I want to do is restrict each instance so it is accessible only from its associated web/app server and from the localhost (ie. someone from one web/app server cannot use SSMS to connect to a SQL instance that doesn't belong to that web/app server). They will need to be able to use 3 accounts from each web/app server to reach the SQL Server instance - SA and two specific user accounts (although I could eliminate SA use - it would be really unnecessary in this scenario as they could use the two SQL user accounts).
Restricting at the vLAN firewall, between the web/app and SQL vLANs, is not an option (vCloud makes that too difficult) so it will need to be at the SQL level. I would like to use the whitelist option with an IP address table because it sounds like that will be the best option (not necessarily the easiest - but security is the goal not ease of use).
I'm hoping to get a specific example of how to do this as time is of the essence (the platform is almost ready for go-live and deployment and I want to have this in place before then if possible). Any assistance is VERY much appreciated!!
Chris
November 16, 2011 at 8:38 am
Don't do this from within SQL Server. Instead, use an IPSEC policy at the OS level.
How to block specific network protocols and ports by using IPSec
This talks about how to do it from the command-line. You can use the GUI to do so. In that case, execute mmc from Start | Run and then add the IPSEC policy add-in. Specifically restrict the TCP ports the SQL Servers are listening on to the IP addresses that should be able to connect.
K. Brian Kelley
@kbriankelley
November 16, 2011 at 8:53 am
Thanks! So I assume I would need to use the SQL Server Config Manager to specify what port each instance uses correct (looks like there is a how to article in the SQL 2008 Books Online library)? And if so then I should be able to use the server IP and port# with my app (it is a .NET-based app) instead of servername\instancename and can then turn the SQL Browser off (maybe)? Then use the policies to restrict inbound traffic per web/app server to each SQL instance port - correct?
Sorry if I sound like an idiot but at least I admit when I need to go to someone else for answers. Thanks for the help!!
November 16, 2011 at 9:06 am
christopher.metzger (11/16/2011)
Thanks! So I assume I would need to use the SQL Server Config Manager to specify what port each instance uses correct (looks like there is a how to article in the SQL 2008 Books Online library)? And if so then I should be able to use the server IP and port# with my app (it is a .NET-based app) instead of servername\instancename and can then turn the SQL Browser off (maybe)? Then use the policies to restrict inbound traffic per web/app server to each SQL instance port - correct?Sorry if I sound like an idiot but at least I admit when I need to go to someone else for answers. Thanks for the help!!
All correct.
K. Brian Kelley
@kbriankelley
November 8, 2013 at 3:14 am
K. Brian Kelley (4/15/2011)
jinlye (4/15/2011)
this seems like a lot of effort and increased overhead on your SQL database, for something that can be handled much more efficiently at the transport layer
If in your situation you can handle this much more efficiently at the transport layer, then you should. But if you can't (in a large organisation your empire as DBA may stop where your database ends, and you don't have control over the transport layer), then here is a cool idea for adding IP-based restrictions. YMMV.
Exactly. I wrote the article in response to a question I received where the DBA was not getting support from the networking folks. My typical recommendation (check my blog) is to try and do this with a firewall between SQL Server and everything else (not the OS, but a separate device).
Nice article, thanks.
Too bad sometimes we end up doing things in the "wrong" place because we don't get assistance from those who could do it in the "right" place.
I could accept this solution as a temporary workaround while the network guys do their homework, but I would definitely avoid it as permanent solution.
-- Gianluca Sartori
November 8, 2013 at 4:54 am
nice article, it's good to see those papers re-published.
can anyone help me on how to get the "hostname" from EVENTDATA() ?
ty
November 8, 2013 at 8:08 am
I think I'll use this to prevent my co-workers from using the web service login to connect to SQL instead of the login I provided them. I'll just block that login unless it comes from the primary or backup Web Servers. 😀
November 11, 2013 at 2:21 am
I am trying to run following query -
DECLARE @IP NVARCHAR(15);
SET @IP = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)'));
Select @IP
It is returning Null. Am I doing something wrong ?
November 12, 2013 at 7:44 am
Look in BOL:
"EVENTDATA returns data only when referenced directly inside of a DDL or logon trigger. EVENTDATA returns null if it is called by other routines, even if those routines are called by a DDL or logon trigger."
Lee
Viewing 9 posts - 46 through 53 (of 53 total)
You must be logged in to reply to this topic. Login to reply