Open SQL Port for specific IP by ASP.NET Website

We run a public SQL Server on Windows Server 2008 and had a lot of dictionary attacks in the past. To prevent that, do following

1) disable sa user

2) enable Windows Firewall, set a inbound rule ( here named sql) and lock the port 1433, then add the external IP’s ( scope tab) which needs access from extern

Now you are save. New problem is, what happens when you have dynamic IP address on client and need access by SQL Manager or Visual Studio Server Manager. Take really long time to solve that problem. My goal was to establish a website, where you click a button and your IP is in the firewall.

1) create a user account on server which have exactly the fitting rights for netsh and firewall. Group User should be enough

2) create in IIS7 a application pool which this account. Impersonation doesn't work. Even start a process which a specific user account. This comes later.

3) create a Web application in IIS7 (also subdirectory possible) with app pool from 2)

4) create a command file name sqlip.cmd to set the ips in the firewall and test it on server if it works

netsh advfirewall firewall set rule name="sql" new remoteip=194.x.x.x,195.x.x.x,%1

5) create aspx page where you call cmd.exe with processtart. This was complicated and I have to do some workarounds eg. can not call the cmd directly. Have to open cmd.exe and send input to the process.

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim ps As System.Diagnostics.ProcessStartInfo = New System.Diagnostics.ProcessStartInfo("cmd.exe")
        ps.UseShellExecute = False
        ps.RedirectStandardOutput = True
        ps.RedirectStandardInput = True
        ps.RedirectStandardError = True
        Dim proc As System.Diagnostics.Process = System.Diagnostics.Process.Start(ps)
        Dim strm As System.IO.StreamReader = System.IO.File.OpenText(Server.MapPath("sqlip.cmd"))
        Dim sout As System.IO.StreamReader = proc.StandardOutput
        Dim sIn As System.IO.StreamWriter = proc.StandardInput
        Dim zeile As String
        While (strm.Peek() > 0)
            zeile = Replace(strm.ReadLine(), "%1", Request.UserHostAddress)
            sIn.WriteLine(zeile)
        End While
        sIn.WriteLine("EXIT")
        strm.Close()
End Sub

That sample only works for one dynamic ip. If you want more make some list where the Ip’s stored and add the list with netsh.