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
641 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 37

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 70

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 37

Assisted Solution

by:Mahesh
Mahesh earned 400 total points
ID: 40221239
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The following article is comprised of the pearls we have garnered deploying virtualization solutions since Virtual Server 2005 and subsequent 2008 RTM+ Hyper-V in standalone and clustered environments.
Resolving an irritating Remote Desktop connection that stops your saved credentials from being used.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

696 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