SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Other SQL Server Topics (2005)
 remote connectivity
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

tkizer
Almighty SQL Goddess

USA
37167 Posts

Posted - 10/12/2007 :  13:38:49  Show Profile  Visit tkizer's Homepage  Reply with Quote
Glad I was able to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

ZenProfit
Starting Member

11 Posts

Posted - 11/23/2007 :  15:59:16  Show Profile  Reply with Quote
Tara: I am having the same issues you helped AndrewB with last month. 2 new Win03 Servers, SQL2005 on both. Serv1 is in domain, Serv2 is workgroup. They are IP-mapped to each other and each can ping the other via IP address. I have tried identical setup you described in Configuration using Alias: Serv1/IP address of LOCAL machine or Serv1/IP of Remote machine and same with Serv2. I can Register each on the other in Studio in Registered Servers and get GREEN arrow using SQL Auth. However, cannot CONNECT to either remotely in Object Explorer. Have spent 2 weeks searching for a solution. What am I missing? Hope you had a Happy Thanksgiving.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37167 Posts

Posted - 11/23/2007 :  17:31:56  Show Profile  Visit tkizer's Homepage  Reply with Quote
What error are you getting?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

ZenProfit
Starting Member

11 Posts

Posted - 11/24/2007 :  08:30:08  Show Profile  Reply with Quote
There are 2 different "errors".

The first one (I think is an error) is that on either server, the Remote database shows with an empty circle (not green arrow) in the Object Explorer, even though in Registered Servers both Databases have green arrows (using IP address as the name of the Remote one). I do not get an error message when making the "connection" to the Remote database in Object Explorer. The Remote database can only "connect" with the IP address as the name, not the name of the Database.

The 2nd one is when I attempt to create a Subscriber for a Replication. On Server1 I have created a Publication of its local Database. I then go into the New Subscription wizard, set the Publication, make the Local Agent at the Distributor, and on the Subscribers window, click Add SQL Server Subscriber. If I try to use the Database name I get error: "Connections through a server alias, IP address, or any other alternative name are not supported. Specify the actual server name, '[displays the name of the Remote Database in the error message]' (Replication.Utilities.)

When I select the Database name from the Server Name pulldown, select SQL Auth, use the correct Login/Password, on Connection Properties select TCP/IP Network Protocol, and click OK, I get this error message:

"Failed to connect to [the selected database] (Microsoft.SqlServer.ConnectionInfo). 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: TCP Provider, error: 0 - No such host is known.) (SQL Error: 11001)"

On another SQL Forum, someone wrote that since Server1 is in its own Domain, and Server2 is in a workgroup, they will never "see" the other correctly. They wrote: "I'm guessing you don't have name resolution to the remote site. First you need to set up name resolution. If you can't use the current DNS zone, set up a new dummy DNS zone put in a host record for the remote server and an alias dns record (forgot the right name). You will also need to have static IP's or the right firewall policies set up for this."

There are static IPs at both servers and I opened Port 1433 at the firewall. I used Surface Area Configuration and Server Configuration Manager to set TCP/IP and Named Pipes, set 1433 as the port, tried setting the Alias pointing to either the Local or the Remote IP address at either Server. No matter what I tried, I get the same bad result: no apparent "connection" and inability to set Replication Subscriber as the Remote database.

Is it the DNS issue which is causing the problem? I sent my private email address to your blog Contact page. If you want, we can continue off-line from this forum. Thank you for your swift response.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37167 Posts

Posted - 11/24/2007 :  22:25:12  Show Profile  Visit tkizer's Homepage  Reply with Quote
The first issue with the green arrow is not a problem. Don't worry about that one. The client probably can't figure out the state of the service in time or you don't have sufficient privileges to see it. I've got plenty of servers where the state can not be determined, yet I can connect fine to them.

Your second issue about only being able to connect via an IP address means you've got a DNS issue. But please note that you can not connect via the database name. What we connect with is the instance name or whatever we setup as an alias. If you don't know who to contact to fix your DNS issue, then just setup an alias on your client. Steps on how to do this are detailed earlier in this thread.

Your third issue about replication can be fixed most likely by adding aliases to both servers pointing at the other server. I can't help you with the information provided by the other person at another SQL forum as I'm not familiar enough with network stuff to be able to help.

It is always easiest to start troubleshooting connectivity issues by attempting to telnet to the SQL port. If you can successfully do this from the client to the server, then we can easily fix the issue with aliases. If you can't successfully telnet to the SQL port, then you've got a network issue that needs to be corrected by a network admin.

I do not respond to emails sent via here or my blog for forum type stuff. I don't get paid to help out here, so discussing things offline is not something that most of us do here.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

ZenProfit
Starting Member

11 Posts

Posted - 11/25/2007 :  07:47:08  Show Profile  Reply with Quote
Tara:

Thank you for the detailed answer.

Since either Server can Telnet the other successfully, it must be the Alias issue. Perhaps I was not creating the Alias correctly: I has used Alias: Server1 with IP address of (either) itself or the other Server, ON Server1. I will try creating an Alias for Server1 on Server2 and visa versa.

Perhaps the 33rd try will be the charm!
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37167 Posts

Posted - 11/25/2007 :  15:37:16  Show Profile  Visit tkizer's Homepage  Reply with Quote
The alias can not point to itself. The alias gets setup on the client and points to the server.

If you can telnet successfully, then let's try connecting with ServerName,PortNumber in Management Studio. So what do you get when you connect via ServerName,1433 (or whatever your data elements are)? If you get an error, try this too:

tcp:ServerName,1433

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

ZenProfit
Starting Member

11 Posts

Posted - 11/25/2007 :  19:32:41  Show Profile  Reply with Quote
What exactly is "Server Name": the name of the Server as shown in Computer | Properties | Computer Name?

Here is how I set the Alias on Server 2:

Alias: Server2, Pipe Name: \\192.168.xxx.xxx\pipe\sql\query, Protocol: Named Pipes, Server: 192.168.xxx.xxx

where 192.168.xxx.xxx is the IP address of Server 1

Is this correct?

What should the Alias on Server 2 be?
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 11/25/2007 :  19:39:03  Show Profile  Reply with Quote
Do you have server2 on your network? Shouldn't use server2 as alias name to server 1 if so.
Go to Top of Page

ZenProfit
Starting Member

11 Posts

Posted - 11/25/2007 :  20:00:23  Show Profile  Reply with Quote
Ah, "Do you have server2 on your network?"

That is the issue. The IT dept setup Server 1 in its own Domain with a Static IP address.

Server 2 is "connected" to Server 1 by a mapped drive letter using the Static IP address of Server 2. And Server 1 is "connected" to Server 2 by a mapped drive letter using the Static IP address of Server 1. Server 2 is NOT in the Domain of Server 1 or in any domain. It controls a Workgroup, not a domain.

The primary SQL data (SQL1) will reside on Server 1. The idea is to Publish a Snapshot of SQL1 and Replicate it to Server 2 on which SQL2 resides AND, to Publish a Snapshot of SQL2 to Server 1, so SQL2 contains all of the data in SQL1 and SQL1 is complete with the local additions made on SQL2.

Is this the correct method?
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 11/25/2007 :  20:25:36  Show Profile  Reply with Quote
>> Server 2 is "connected" to Server 1 by a mapped drive letter using the Static IP address of Server 2. And Server 1 is "connected" to Server 2 by a mapped drive letter using the Static IP address of Server 1.

Don't understand that. How can you connect server 2 to server 1 with serverr 2's ip address?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37167 Posts

Posted - 11/25/2007 :  20:53:12  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by ZenProfit

What exactly is "Server Name": the name of the Server as shown in Computer | Properties | Computer Name?

Here is how I set the Alias on Server 2:

Alias: Server2, Pipe Name: \\192.168.xxx.xxx\pipe\sql\query, Protocol: Named Pipes, Server: 192.168.xxx.xxx

where 192.168.xxx.xxx is the IP address of Server 1

Is this correct?

What should the Alias on Server 2 be?



Please use TCP/IP instead of named pipes for the aliases. I'm pretty sure I went into great detail on how to do this earlier in the thread.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37167 Posts

Posted - 11/25/2007 :  20:56:15  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by ZenProfit

Ah, "Do you have server2 on your network?"

That is the issue. The IT dept setup Server 1 in its own Domain with a Static IP address.

Server 2 is "connected" to Server 1 by a mapped drive letter using the Static IP address of Server 2. And Server 1 is "connected" to Server 2 by a mapped drive letter using the Static IP address of Server 1. Server 2 is NOT in the Domain of Server 1 or in any domain. It controls a Workgroup, not a domain.

The primary SQL data (SQL1) will reside on Server 1. The idea is to Publish a Snapshot of SQL1 and Replicate it to Server 2 on which SQL2 resides AND, to Publish a Snapshot of SQL2 to Server 1, so SQL2 contains all of the data in SQL1 and SQL1 is complete with the local additions made on SQL2.

Is this the correct method?



The fact that one server is in a domain and another is in a workgroup just means that you won't be able to use Window Authentication for the connection. This setup does not impact connectivity though.

Drive mappings do not come into play here.

Since you can telnet in both directions, connectivity through the network is there. We just have to get the correct alias setup on each server.

Registry way of creating aliases:
On Server1, create Server2 alias. Data value would be DBMSSOCN,IPAddressOfServer2,1433

On Server2, create Server1 alias. Data value would be DBMSSOCN,IPAddressOfServer1,1433


Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Edited by - tkizer on 11/25/2007 20:57:51
Go to Top of Page

ZenProfit
Starting Member

11 Posts

Posted - 11/26/2007 :  07:25:04  Show Profile  Reply with Quote
Tara:

Yes, you did cover using TCP/IP instead of Named Pipes. That didn't work the way I had set it up before.

I will create the Alias for SQL 2 on Server 1 and visa versa. If that doesn't work I will try using the Registry method you describe.

You have at least cleared up my concerns about the "mapping" and "connection" and DNS issues.

Thank you.
Go to Top of Page

ZenProfit
Starting Member

11 Posts

Posted - 11/26/2007 :  12:30:54  Show Profile  Reply with Quote
Tara:

You are truly the Ultimate SQL Goddess (who needs Angelina Jolie, anyway?)

Yes. Yes. Yes. We have a winner.

Creating on Server1 the Alias of Server2 with the Server2 IP address did it.

Please post the name of your favorite charity I would be honored to make a donation on your behalf.

Go to Top of Page

amol1997
Starting Member

India
2 Posts

Posted - 04/06/2013 :  05:57:19  Show Profile  Reply with Quote
An error has occured 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: 2)-----Amol Bairagi

Amol Bairagi
Go to Top of Page

amol1997
Starting Member

India
2 Posts

Posted - 04/06/2013 :  06:00:30  Show Profile  Reply with Quote
An error has occured 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: 2) please mail me on sapmgr@cenzer.com

Amol Bairagi
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000