Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 648
  • Last Modified:

Do i need to add SQLBrowser & SQLServer on the outbound policy on the SQL server firewall . If Connection failed for login(user), reason:0 ,

Hi
I have migrated the old SQL database from old server to our new SQL 2012  which is installed on windows 2012 Server. I was told to add SQLBrowser & SQLServer Services to the list of exceptions on the SQL server Firewall.

So executed the following from the CMD prompt.

echo Enabling SQL Server Browser Service port 2382
netsh advfirewall firewall add rule name="SQL Browser" dir=in action=allow protocol=TCP localport=2382

@echo Enabling SQLServer default instance port 1433
netsh advfirewall firewall add rule name="SQL Server" dir=in action=allow protocol=TCP localport=1433

Now when I connect to the SQL server from the win 7 client PC  I get error "connection failed for login reason:0"
I tried disabling the firewall on the SQL server and tried  again from the win7 PC and it successfully connect to the database server, so it looks like I have to make changes on the SQL server firewall.

So do I need to configure a outbound policy on the SQL  firewall for SQLBrowser & SQLServer and If so see below the syntax.Is this a right command that I need to execute on the CMD prompt on the SQL server.

echo Enabling SQL Server Browser Service port 2382
netsh advfirewall firewall add rule name="SQL Browser" dir=out action=allow protocol=TCP localport=2382

@echo Enabling SQLServer default instance port 1433
netsh advfirewall firewall add rule name="SQL Server" dir=out action=allow protocol=TCP localport=1433

Please suggest any help much appreciated.


Thanks in advance.
0
lianne143
Asked:
lianne143
  • 4
  • 2
3 Solutions
 
MaheshArchitectCommented:
To communicate with the SQL Server Browser service on a server over a firewall, open inbound UDP port 1434, and TCP port used by SQL Server (e.g., 1433) from clients to SQL server

If SQL server is configured to work on dynamic ports, then you need to open all dynamic range on firewall (1024-65535 in case 2003 servers OR 49152-65535 in case of 2008 and above) from clients to SQL
In that case it is better to change SQL port to default TCP 1433 OR you can simply turn off firewall
Check below article
http://msdn.microsoft.com/en-in/library/cc646023.aspx
0
 
QlemoC++ DeveloperCommented:
There is no harm in opening all ports (ingress) for specific applications. It does not make a difference in security, as only open ports can be reached, but allows for dynamic ports.
If your SQL Server port really is 1433, you don't need SQL Browser, but if you want to use it, udp/1434 is correct (and fixed), as stated above.
0
 
MaheshArchitectCommented:
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
lianne143Author Commented:
On our DC 2012 we have a GPO setup  to keep all the servers and PC with Firewall on. It doesn't give me an option to disable the firewall on the SQL server ( options are greyed). Only for testing purpose  I disabled the firewall on the GPO and did a gpupdate/force
0
 
lianne143Author Commented:
Previously I  opened port 2382 for SQL Server Browser  through the following command , so do I need to remove the port that I opened previously and change the port to 1433 for SQL Server Browser
 
echo Enabling SQL Server Browser Service port 2382
 netsh advfirewall firewall add rule name="SQL Browser" dir=in action=allow protocol=TCP localport=2382

Thanks
0
 
lianne143Author Commented:
Sorry  , I was not wearing my goggles, I will open inbound UDP port 1434 for SQL Server Browser on the server and post you the results.

thanks
0
 
lianne143Author Commented:
Hi Mahesh

I don't know how much thanks to say to you.
Thanks you so much and now the client connects successfully to our new DB server :)

As you said I opened inbound UDP port 1434 for SQL browser and TCP port  1433 for SQL Server
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now