SQL to SQL Connectivity Mystery

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

SQL to SQL Connectivity Mystery

Author

Topic

dbthj
Posting Yak Master

143 Posts

Posted - 2007-09-25 : 16:24:20

I am having problems with SQL to SQL connectivity. SQL 2000.

I am trying to connect using Query Analyzer (or Enterprise Manager) and getting mysterious behavior.

Consider 3 servers, Server A , Server B and Server C.

All are mixed mode authentication.
All are configured to allow SQL connectivity using RPC.
All use port 1433 for tcpip.
All are on Windows 2003.
All SQL Authentication connections were using sa account. Password is the same on all.

This chart describes some tests done.
Attempted Connectivity........SQL Authentication.......Windows Authentication
=======================================================
A to B..................................... nope................................... nope
A to C..................................... nope................................... nope
---------------------------------------------------------------------------------------------
B to A......................................nope.................................. works
C to A..................................... nope..................................works
-------------------------------------------------------------------------------------------
B to C ......................................works ................................ works
C to B ..................................... works .................................works

So the problem seems to have something to do with Server A.
All 3 servers are on the same domain.
I don’t THINK that there are any firewalls involved (not sure).

Creating entries in the Client Network Utility seems to have no affect.

Error is
Server: Msg 17, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.

Any ideas what to look for? I’m out of ideas.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-25 : 16:26:50

Post exactly what you have for the alias in Client Network Utility on ServerA. If it's easier, get to regedit on ServerA and post the string value for your alias from: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

It might be a good idea to also post these from the other two servers as well in case we need them to help you out.

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

dbthj
Posting Yak Master

143 Posts

Posted - 2007-09-26 : 12:21:18

Tara,

Since the aliases did not help I deleted them. So today I recreated the whole set
and retested. The odd thing is that the problem is worse. I don't know of
anything else that changed, but it is hard to believe that the aliases would
actually inhibit connectivity. I will post new test results at the bottom.

============= on Server C (ni73dbs2)============================
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo]
"DSQUERY"="DBNETLIB"
"prmbn057"="DBMSSOCN,prmbn057,1433"...........(Server A)
"prmbp001"="DBMSSOCN,prmbp001,1433"...........(Server B)

============= on Server B (prmbp001)============================
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo]
"DSQUERY"="DBNETLIB"
"prmbn057"="DBMSSOCN,prmbn057,1433"...........(Server A)
"ni73dbs2"="DBMSSOCN,ni73dbs2,1433"...........(Server C)

============= on Server A (prmbn057)============================
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo]
"DSQUERY"="DBNETLIB"
"ni73dbs2"="DBMSSOCN,ni73dbs2,1433"...........(Server C)
"prmbp001"="DBMSSOCN,prmbp001,1433" ...........(Server B)

Here are the current test results:

Attempted Connectivity........SQL Authentication.......Windows Authentication
=======================================================
A to B..................................... nope................................... nope
A to C..................................... nope................................... nope
-------------------------------------------------------------------------
B to A......................................nope................................... nope
C to A..................................... nope................................... nope
-------------------------------------------------------------------------
B to C ......................................works ................................ works
C to B ..................................... works .................................works

Is there a quick connectivity test for SQL Server similar to tnsping for oracle?
That would make testing go much quicker.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-26 : 12:37:32

One way to test is to try telneting to port 1433 from the client machines to the servers.

The alias would not have made things worse if the settings are correct.

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

dbthj
Posting Yak Master

143 Posts

Posted - 2007-10-11 : 09:47:00

I was wrong. The servers were behind a firewall.
Port is open. Problem solved

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources