Cannot connect via Linked Server

We have two servers, each with named instances of SQL Server 2000 SP3a installed. Server1\Instance1 has a linked server to Server2\Instance2. The linked server is using the SQLOLEODB provider. This linked server is setup with remote login and a correct
password. We get an error #17: SQL Server does not exist or access denied. We also get the same behaviour while trying an OPENROWSET query from the Server1\Instance1 Query Analyzer. All other SQL Server instances we have installed (on other machines)
can connect to Server2\Instance2 with a SQLOLEDB linked server, no problems.
As far as I can tell, this error is only happening when using the SQL Server. I can connect to the remote (Server2\Instance2) server using osql from Server1, and I can even connect to Server2\Instance2 by using a SQLOLEDB connection from a desktop app (f
or example, I can test the connection successfully in a UDL using SQLOLEDB to the remote server from Server1).
We are only using named pipes. I have verified that all client and all server network utilities are set exclusively to named pipes. This is true. I have also performed a network trace on the Server1 server. It does not even attempt to contact the Serv
er2 machine. I have also network traced a different SQL Server instance on another machine that can connect to Server2\Instance2 and it's network trace shows a very clear, immediate connection to the Server2.
What other steps can I take to troubleshoot this problem? I think that I have hit all the common trouble points in setting up a linked server.
Thanks,
Frank
Update: We noticed that there was an additional NIC card on the Server1 server that had an APIPA IP address. We have disabled this NIC and now the network trace is showing some communication between Server1 and Server2.
Basically, the trace shows Server1 attempts to connect using SMB to set the session up on Server2. Server2 responds with an NT error 22, STATUS_MORE_PROCESSING_REQUIRED, then a few frames later, it tries to open the IPC$ pipe on Server2. Server2 respond
s with an NT error 34, STATUS_ACCESS_DENIED. So why is SMB not allowing connections to the IPC$ pipe? I can log in to the Server1 machine under the same account as SQL Server is running as and issue a successful "net use \\Server2\IPC$" without a proble
m. The network trace requests from Server1 for the net use look similar to those generated by SQL Server linked server. Why does one get an access denied and the other does not?
Thanks,
Frank
|||Hi Frank,
I learned you are failed to create linked Server between Server1\Instance1
and Server2\Instance2. The error "SQL Server does not exist or access
denied." occurred.
1. Please check the Server Network Utility for named pipes and make sure it
is the default value.
\\.\pipe\MSSQL$ Instance2\sql\query
2. Please check to see if UDP port 1434 is enabled.
3. Please try to add the linked Server using sp_addlinkedserver in QA
(Query Analyzer). What's the result? If there is any error occurring,
please provide the detailed error message.
For more information regarding sp_addlinkedserver, please refer to the
article on SQL Server Books Online.
Topic: "sp_addlinkedserver"
4. Please check the application/system log on both Servers and see if there
is any error information when the "linked Server" error occurred.
5. Please try to create an alias for Server2\Instance2 and then create the
linked Server. Does the same problem still persist?
Also, such issues tend to be complex and take up extensive research time.
I'd like to set your expectations that it may take a while for us to help
you narrow down the problem and we may eventually redirect you to PSS to
continue working with a dedicated Support Professional. If this is
critical, I'd recommend contacting PSS and opening a Support incident
troubleshoot this further. If you need any help in this regard, please let
me know.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
|||Hi Yuan, I have completed the following steps for troubleshooting from your post. My comments are indented below each of your suggestions:
1. Please check the Server Network Utility for named pipes and make sure it
is the default value.
\\.\pipe\MSSQL$ Instance2\sql\query
I have checked the Server & Client network utilities: named pipes is default and the only protocol. That was stated in my original post.
2. Please check to see if UDP port 1434 is enabled.
Yes, port 1434 UDP is listening on the Server2 machine.
3. Please try to add the linked Server using sp_addlinkedserver in QA
(Query Analyzer). What's the result? If there is any error occurring,
please provide the detailed error message.
For more information regarding sp_addlinkedserver, please refer to the
article on SQL Server Books Online.
Topic: "sp_addlinkedserver"
Adding the linked server is not the problem. I can do it either through Enterprise Manager or the stored procedure. It is when I try to connect to the linked server (for example, when EM queries for table names from the linked server) that I get
Error #17 SQL Server does not exist or access denied, as stated in my original post.
4. Please check the application/system log on both Servers and see if there
is any error information when the "linked Server" error occurred.
I have been monitoring the event log (all application, security and system) for any errors on either system and there is nothing.
5. Please try to create an alias for Server2\Instance2 and then create the
linked Server. Does the same problem still persist?
I have tried to create an alias with the same results. No connectivity; access denied.
I have hit most of these normal troubleshooting steps. I don't usually jump to a network trace before trying things like this. The SMB network trace for opening the named pipe shows an access denied error from the Server2. What other issues might cause
this?
Thanks,
Frank
|||Hi Frank,
This issue is very strange. Please try the following steps to see if they
are helpful.
1. Try to create a new linked server using SQL Server directly but not OLE
DB provider for SQL Server. In the meantime, please specify a SQL account
in the general tab to connect to Server2\instance2 server. After creating
this linked server, please try to perform the following sql statements. It
is best to grant the admin permission of server2\instance2 to this test SQL
account.
Select * from OPENQUERY(<Linked Server Name>, 'SQL statements')
Select * from <Linked server Name>X<Table Name>
Does the same error message "SQL Server does not exist or access denied"
still persist? Is there any other error message occurring? It is
appreciated you can provide the completely error message.
2. Please use MDAC checker on your Server1 and provide the result.
HOW TO: Check for MDAC Version
http://support.microsoft.com/default...b;EN-US;301202
I am looking forward to hearing from you soon.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
|||Yuan, we have tried creating a SQL Server without using the OLEDB provider and this failed in the same manner as the prior failures. Both the openquery and the four part fully qualified name queries failed with Error #17, SQL Server does not exist or acc
ess denied.
The MDAC is 2.7SP1 on Server1, 2.8 on Server2...
Any help at this point would be greatly appreciated...
Thanks,
Frank
-- "Yuan Shao" wrote: --
Hi Frank,
This issue is very strange. Please try the following steps to see if they
are helpful.
1. Try to create a new linked server using SQL Server directly but not OLE
DB provider for SQL Server. In the meantime, please specify a SQL account
in the general tab to connect to Server2\instance2 server. After creating
this linked server, please try to perform the following sql statements. It
is best to grant the admin permission of server2\instance2 to this test SQL
account.
Select * from OPENQUERY(<Linked Server Name>, 'SQL statements')
Select * from <Linked server Name>?X<Table Name>
Does the same error message "SQL Server does not exist or access denied"
still persist? Is there any other error message occurring? It is
appreciated you can provide the completely error message.
2. Please use MDAC checker on your Server1 and provide the result.
HOW TO: Check for MDAC Version
http://support.microsoft.com/default...b;EN-US;301202
I am looking forward to hearing from you soon.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
|||Hi Frank,
Thanks for your feedback. After reviewing our discussion in detail, I
noticed you test the connection successfully in a UDL using SQLOLEDB to the
remote server (server2/instance2) from Server1. However, you still got the
error "SQL Server does not exist or access denied." using linked server
I would like you to check if this is a permission problem. Please try to
create a linked server with sa login and check to see if the same error
persists. I have attached a linkedserver.zip file with my notification to
help describe the configuration of linked server using sa login.
I am looking forward to hearing from you soon.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
|||Yuan, we have tried connecting to the linked server using the sa password and it still results in the original failure: Error 17, SQL Server does not exist or access denied. It seems like it is the named pipes connectivity shutting the door down rather t
han SQL Server, although I cannot verify this as neither SQL Server nor the operating system are logging this (despite having all failure logging turned on both the OS local security policy and in SQL Server). Any other ideas?
Thanks,
Frank
|||Have you tried a connection method other than named pipes. Try making TcpIP and secondary connection method and setting up an alias using IP.
Are both servers on the same segment?
Sometimes netbios names are not passed across segments.
Have you tried rebooting the server1 since disabling the NIC. Sometimes it needs to rebroadcast its existance on the NIC.
Thanks
Jeff
|||Hello Frank,
Looking at the nature of this issue, it would require intensive
troubleshooting which would be done quickly and effectively with direct
assistance from a Microsoft Support Professional through Microsoft Product
Support Services. You can contact Microsoft Product Support directly to
discuss additional support options you may have available, by contacting us
at 1-(800)936-5800 or by choosing one of the options listed at
http://support.microsoft.com/default...=sz;en-us;top.
If this is not an urgent issue and your would like us to create an incident
for you and have Microsoft Customer Service Representative contact you
directly, please send email to (remove "online." from this no Spam email
address): mailto:dscommhf@.online.microsoft.com with the following
information,
*Include "Followup: <Tomcat IssueID>" in the email Subject.
*Location of the post
*Subject Line
*First Name, Last Name
*MSDN Subscriber ID
*Company name (if any)
*Phone number
*e-mail address
Thanks for using MSDN Newsgroups.
Vikrant Dalwale
Microsoft SQL Server Support Professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Get secure !! For info, please visit http://www.microsoft.com/security.
Please reply to Newsgroups only.
| Thread-Topic: Cannot connect via Linked Server
| thread-index: AcQh0Df+wj3plzMnQ3+eAqcmfTrgkg==
| X-WN-Post: microsoft.public.sqlserver.connect
| From: "=?Utf-8?B?RnJhbmsgSm9uZXM=?=" <frank@.bogusfiosincfake.com>
| References: <99E987D2-0636-4917-8627-8E1A87789198@.microsoft.com>
<4B4AE818-15A4-4C50-97DC-7370D8DC8D49@.microsoft.com>
<3tFzedKHEHA.612@.cpmsftngxa06.phx.gbl>
| Subject: RE: Cannot connect via Linked Server
| Date: Tue, 13 Apr 2004 20:26:04 -0700
| Lines: 4
| Message-ID: <50E78236-79B1-4D3F-9BFE-CB02692DB966@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.connect
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.connect:40978
| NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
| X-Tomcat-NG: microsoft.public.sqlserver.connect
|
| Yuan, we have tried connecting to the linked server using the sa password
and it still results in the original failure: Error 17, SQL Server does not
exist or access denied. It seems like it is the named pipes connectivity
shutting the door down rather than SQL Server, although I cannot verify
this as neither SQL Server nor the operating system are logging this
(despite having all failure logging turned on both the OS local security
policy and in SQL Server). Any other ideas?
Thanks,
Frank
|