Deny remote access from one IP to all users but one

Question

I have 2 sql server 2005 separated by firewall: ServerA and ServerB.

I allowed traffic on non-standard tcp port between ServerA IP and ServerB IP; I can connect from one server to the other server using correct remote credentials.

What I would like to do is to permit only one user to connect from serverA to serverB (using a sql account located on server B). No matter how many user I have in server B, I would like that all but one can connect.

Is it possible ? How can I do it ?

Thanks!

Answer 1

I'm not sure I understand your situation, but I think you want to use the firewall to allow only a single user to connect from server A. The Windows Firewall with Advanced Services has an option to filter by users. The Windows Firewall with Advanced Services is available on Windows Server 2008 (and R2) and Windows Vista, and Windows 7. It's can be configured from Windows Server 2003 by using netsh.exe which is a little harder. So filter by IP address and port, and by user. Does that get what you want?

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 2

Hi Rick, thanks for answering.

We have an hardware firewall and we don't use Windows firewall. Unfortunately, our firewall does not permit user filtering.

The other problem is that ServerA and ServerB are NOT in the same domain; they are win2003 server machines running under their own workgroup.

Do you think that, using netsh.exe, I could tell ServerB to accept incoming connections from ServerA (and its IP) only if they are originated by user X ? Do you have some example to start from ?

Oh, and just to finish :) , all other machines (different ip of course) that try to connect to ServerB have to work as usual.

Am I asking the moon ??? :-P

Thanks.

Answer 3

Unfortunatly, I've only done some basic work with netsh when I wrote the examples in http://msdn.microsoft.com/en-us/library/cc646023.aspx I can see the option in the Windows 7 UI.  But that doesn't mean you'll find it in Windows Server 2003.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 4

You can try using Logon Trigger for this.

On Trigger part, you can place the business logic / validation

---

Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!