Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Problem with connecting 2 SQl 2005 Servers Expand / Collapse
Author
Message
Posted Friday, February 22, 2013 9:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 28, 2013 3:46 AM
Points: 17, Visits: 44
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
Post #1423113
Posted Monday, February 25, 2013 3:35 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 217, Visits: 872
SQL Browser service running on server 2?

'Only he who wanders finds new paths'
Post #1423523
Posted Monday, February 25, 2013 5:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 28, 2013 3:46 AM
Points: 17, Visits: 44
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
Post #1423557
Posted Monday, February 25, 2013 7:38 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 217, Visits: 872
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'
Post #1423610
Posted Monday, February 25, 2013 10:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 28, 2013 3:46 AM
Points: 17, Visits: 44
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
Post #1423683
Posted Monday, February 25, 2013 3:19 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 217, Visits: 872
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'
Post #1423793
Posted Tuesday, February 26, 2013 1:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 28, 2013 3:46 AM
Points: 17, Visits: 44
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
Post #1423920
Posted Tuesday, February 26, 2013 2:02 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 217, Visits: 872
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'
Post #1423928
Posted Tuesday, February 26, 2013 2:06 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 217, Visits: 872
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'
Post #1423930
Posted Tuesday, February 26, 2013 2:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 28, 2013 3:46 AM
Points: 17, Visits: 44
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
Post #1423931
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse