Freigeben über

Replication over firewall

  • Artikel

Most of the times you would run into scenario where you have a firewall between publisher/ distributor & subscriber

 

                                     |

                                     | F

                                     | I

PUBLISHER | R

                                     | E SUBSCRIBER

                                     | W

DISTRIBUTOR | A

                                     | L

                                     | L

                                     |

 

 

Most people usually only allow traffic through one TCP/IP port on which the respective SQL instance is listening & in most of the cases even SQL Browser service is stopped & Named Pipe protocol is disabled.

 

But if the subscriber is a named instance listening on either static or dynamic port then you can only connect to it using either an alias or specfying the port number along with SERVER\INSTANCE NAME.

 

In such scenario if you try to create a subscription & specify SERVER\INSTANCE NAME, <PORT> for the sbscriber server either in the New Subscription wizard or in T-SQL then it failes with below error for example

 

SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, 'HIMALAYA\KILLER'. (Replication.Utilities)

 

You will not be able to create subscription because the doesn't allow server with port or ip address alone or with port. However, there is a special case with aliases. Though the above error message tells you not to use aliases but as long as the alias name is same as the subscriber server name & you have specified correct port number in the alias the subscription will be created & replication will successfully work. Create a TCP/IP alias on publisher server with same name as susbcriber server\ instance with correct port.

 

Make sure that when you run select @@servername on subscriber it returns you the correct server name

 

 

NOTE

=====

Alias names can be case sensitive, make sure to use the same case while specifying alias in SQL Server Management Studio or any replication wizard

Comments

  • Anonymous
    January 01, 2003
    Hello Rishi, Can you explain your scenario in detail ? Thanks, Anurag

  • Anonymous
    January 01, 2003
    hello daniel, if only port 4010 is open in firewall then SQL Server must listen on port 4010. All the clients outside firewall trying to connect to SQL Server will use port 4010 in connection string/ alias and will pass through the firewall as well but will fail if SQL Server is not listening on that port. Though you can configure SQL Server to listen on multiple ports but replication is oblivious to any port setting. Replication subsystem just operates over linked servers and as long as normal connectivity over firewall is working fine, replication will also work fine. Replication clients outside firewall will anyways only be able to communicate over 4010 because, A) they only know port 4010, B) only 4010 is allowed through firewall. Clients inside firewall may be able to leverage other ports on which SQL is listening. Please create TCP alias on subscriber with port 4010 and test normal connectivity to publisher before configuring replication. Refer "How to: Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager)" [http://msdn.microsoft.com/en-us/library/ms177440.aspx] and "How to configure SQL Server 2005 to allow remote connections" [http://support.microsoft.com/kb/914277]

  • Anonymous
    January 01, 2003
    Great post, explained really well and I could really understand. Thank you.

  • Anonymous
    January 01, 2003
    hi tahar, workaround is simple. Create an alias of the subscriber with the same name of the subscriber for e.g. if SQLGUYYUKON is listening on port 4532 then create an alias with name SQLGUYYUKON & in the alias configuration you can specify the port number & the  serverinstance name. Let me know if it helps

  • Anonymous
    December 23, 2008
    If you can give more detail please. on how creating TCP/IP alias of the subscriber thanks ++

  • Anonymous
    July 22, 2009
    Dear anurag i am facing same problem but solution is not find at yet.please help Rishi

  • Anonymous
    August 01, 2009
    hi anurag, imagine this. 2 sql server 2008 the scenario is the same as you described but the servers are listening to port 1433 and do not have any connections to each other (publisher=distributor and cannot reach subscriber) now i added port 4010 and wanted to create a replication. only port 4010 is open (firewall rule). how can i do that? how do i have to configure sql server so that the replication works only using port 4010? i have tried quite a few constellations but as soon as i close 1433 nothing works anymore... thanks in advance!! daniel