Solved

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

Posted on 2014-07-25
7
622 Views
Last Modified: 2014-07-27
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
Comment
Question by:lianne143
  • 4
  • 2
7 Comments
 
LVL 35

Accepted Solution

by:
Mahesh earned 400 total points
ID: 40221037
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
 
LVL 68

Assisted Solution

by:Qlemo
Qlemo earned 100 total points
ID: 40221204
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
 
LVL 35

Assisted Solution

by:Mahesh
Mahesh earned 400 total points
ID: 40221239
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:lianne143
ID: 40221264
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
 

Author Comment

by:lianne143
ID: 40221270
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
 

Author Comment

by:lianne143
ID: 40221303
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
 

Author Comment

by:lianne143
ID: 40222240
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Sometimes drives fill up and we don't know why.  If you don't understand the best way to use the tools available, you may end up being stumped as to why your drive says it's not full when you have no space left!  Here's how you can find out...
OfficeMate Freezes on login or does not load after login credentials are input.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now