Post reply

Problem with connecting 2 SQl 2005 Servers

  • February 22, 2013 at 9:02 am

    #274469

    Hi

    I am running SQL 2005 Standard edition under Windows Small Business Server 2003.

    Everything, that I need runs just fine from local LAN clients. The problem is, i am installing another sql 2005 in another location i am trying to replicated data from server 1 go server 2 which i have connected via a vpn, I can ping and browse server 1 but I can't connect to the SQL server with Server Management studio. Everytime I try to connect, the connection ends up in error message:

    TITLE: Connect to Server

    ------------------------------

    Cannot connect to sql.domain.local

    ------------------------------

    ADDITIONAL INFORMATION:

    An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476

    server 1

    windows server 2003

    Sql 2005 standard all service packs etc

    192.168.10.1 vpn connected

    server 2

    windows server 2003

    Sql 2005 standard all service packs etc

    192.168.10.5 vpn connected

    I have google searched this problem and tried all user recommendations I've found.

    - I have correctly forwarded ports on my router (e.g. I can access the remote desktop from remote places without problem)

    - The Windows firewall is well configured, I have added ports 1433, 1434 and I've tried it even with windows firewall turned off.

    - I have enabled all protocols needed and revised all setting concerning remote connections within configuration manager and surface configuration

    Nothing has helped.

    Please guys if you have any recommendations for me i would be very grateful

    Any kind of help will be highly appreciated.

    http://www.go-train.co.uk

  • February 25, 2013 at 3:35 am

    #1590730

    SQL Browser service running on server 2?

    'Only he who wanders finds new paths'

  • February 25, 2013 at 5:12 am

    #1590759

    Hi Thanks for the reply

    yes it was off but I have enabled it now and when i go to setup replication i am getting the following error message

    SQL Server Replication requires the actual server name to make a connection to the server.

    connections through a server alias,ip address or any alternate name are not supported.

    Specify the actual server name, "servername" replication utilities

    I am using servername.domain.local to connect, i can browse the server fine by going to start run servername.domain.local.

    so i am alittle confused as to why i would be recieving this message when i am using the right server name etc

    by the way as you can tell i am very new to sql server

    thanks again

    http://www.go-train.co.uk

  • February 25, 2013 at 7:38 am

    #1590804

    May I ask, I suppose in a step by step way, what you have done so far to set up replication ?

    'Only he who wanders finds new paths'

  • February 25, 2013 at 10:12 am

    #1590867

    Hi David

    thanks very much for your help so far

    well i have tested all replication from two sql 2005 servers internally and all works fine

    so all i have done is replicated this to two servers that are in two external locations

    Server 1 being the publisher and server 2 taking the subscriptions

    what i need to add is they are connected to two different domains and connected by a vpn

    I haven't got a script to authenticate is this what I am missing?

    I have added the ip and name to the host file for both servers enabling me to browse files with a username and password etc

    the new error i am getting now when i try and setup replication points to a updated server name but this is not the case for me and the server name has never changed.

    I can also connect to the remote server from Sql management consol but just cant setup replication

    i hope this helps

    http://www.go-train.co.uk

  • February 25, 2013 at 3:19 pm

    #1590956

    May I ask, are you connecting to server 2 with windows or sql authentication? Can you set up a linked server on server 1 to the subscription server with the same credentials?

    'Only he who wanders finds new paths'

  • February 26, 2013 at 1:18 am

    #1591054

    Hi David

    I am doing it with SQL authentification as to the linked server like i have said I am very new to sql so I have only setup replication and that worked just perfect for me internally this is why i have gone down this route.

    I was recommended to go down the replication route but please tell me if you think i would be better to go down the linked route.

    the thing that i dont get is in sql management Studio i can connect to the remote sql server and access all tables etc its just getting the replication to work.

    thanks

    http://www.go-train.co.uk

  • February 26, 2013 at 2:02 am

    #1591061

    Well of course that depends what you are doing!

    I was just double checking that server A can connect to server B directly, I know you can access server B through SSMS but can you set up server b as a linked server with the sql authentication on server A (apologies if you've told me you can already btw). Does the sql user have the adequate rights to use the replication objects?

    I did read about a potential fix where you can have a server alias whereby the alias name is exactly the same as the server name but that was some late reading and I would perhaps rule out the potential simpler issues first.

    'Only he who wanders finds new paths'

  • February 26, 2013 at 2:06 am

    #1591063

    Just out of interest, from management studio on server B what do you get when you type

    SELECT @@SERVERNAME is it the expected?

    'Only he who wanders finds new paths'

  • February 26, 2013 at 2:13 am

    #1591064

    well i did go down this route last night as i did read somewhere that it could be down to the server name but its was as expected

    server 1 is called servsql

    server 2 is called chatsql

    i have created alias for both on server one but still no joy

    I have also tried connecting with servsql.domain.local

    but still get an error message

    http://www.go-train.co.uk

  • February 26, 2013 at 2:15 am

    #1591065

    I was reading this thread http://www.sqlservercentral.com/Forums/Topic759989-291-1.aspx and thought it was my answer but still nothing.

    http://www.go-train.co.uk

  • February 26, 2013 at 2:41 am

    #1591073

    sorry forgot to add i am using the sql SA username as I thought this would have appropriate access rights etc

    i used this when setting up internally

    http://www.go-train.co.uk

  • February 26, 2013 at 2:43 am

    #1591074

    sorry thought i should add I am using the SA sql user account as I thought this has appropriate access rights

    I used this account when setting up internally

    http://www.go-train.co.uk

  • February 26, 2013 at 2:45 am

    #1591075

    sorry thought i should add I am using the SA sql user account as I thought this has appropriate access rights

    I used this account when setting up internally

    http://www.go-train.co.uk

  • February 26, 2013 at 2:46 am

    #1591076

    sorry thought i should add I am using the SA sql user account as I thought this has appropriate access rights

    I used this account when setting up internally

    http://www.go-train.co.uk

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply