locked
Allowing SQL Connections from only 2 specific machine (IP Based connection) RRS feed

  • Question

  • Hi,

    In my project we are using SQL server 2008 R2 on Windows 2008 R2. We have separate servers for UI, WCF, Jobs and Database.

    When the deployment and Sanity test gets complete, developer connects to this DB instance from the local desktop for debugging the local WCF and job.

    But the problem is developers leaves the job running after their debug. So when the scm build happens the job gets picked up in  more than one server (Local desktop & the actual job server). This is not desired. 

    I planned to setup windows firewall on the sql box to allow connection only from the desired WCF machine to allow connection on  1433 port durign the deployment & testing time and enable the port back for debugging only when it is required. 

    When i checked the windows firewall, it gives the option Domain, public & private option in the Profile tab. My requirement is to allow connection only from my WCF and Jobs machine during the testing. 

    Please let me know if firewall is the right solution or is there any other way in SQL Server to allow connection only from this 2 machines.

    With Regards,

    S.Sudharsan

    Friday, February 17, 2012 5:54 AM

Answers

  • Hi,

    You have more options, but I'd suggest checking logon triggers first.
    If you would need more help with it, let us know.

    Regards,
    Zoli

    • Proposed as answer by Janos BerkeMVP Friday, February 17, 2012 9:22 AM
    • Marked as answer by amber zhang Thursday, February 23, 2012 8:49 AM
    Friday, February 17, 2012 6:07 AM
  • I'm fairly sure that you can set up Windows firewall to permit connection from only specific machines. However, I don't know much about the details for Windows Firewall in Windows 2008 R2. I think you are better off asking about firewall configuration in a Windows Security forum.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by amber zhang Thursday, February 23, 2012 8:49 AM
    Friday, February 17, 2012 11:11 PM

All replies

  • Hi,

    You have more options, but I'd suggest checking logon triggers first.
    If you would need more help with it, let us know.

    Regards,
    Zoli

    • Proposed as answer by Janos BerkeMVP Friday, February 17, 2012 9:22 AM
    • Marked as answer by amber zhang Thursday, February 23, 2012 8:49 AM
    Friday, February 17, 2012 6:07 AM
  • Hi Zoli,

    Thank you for the response. Your solution was very helpful.

    But it would be really helpful to have a sort of setting in the sql management studio or any other means. The management wants  a  cleaner method of performing this without getting into the code. 

    With Regards,

    S.Sudharsan

    Friday, February 17, 2012 5:21 PM
  • I'm fairly sure that you can set up Windows firewall to permit connection from only specific machines. However, I don't know much about the details for Windows Firewall in Windows 2008 R2. I think you are better off asking about firewall configuration in a Windows Security forum.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by amber zhang Thursday, February 23, 2012 8:49 AM
    Friday, February 17, 2012 11:11 PM