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
633 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 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 69

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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

New Windows 7 Installations take days for Windows-Updates to show up and install. This can easily be fixed. I have finally decided to write an article because this seems to get asked several times a day lately. This Article and the Links apply to…
A safe way to clean winsxs folder from your windows server 2008 R2 editions
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…

679 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