Solved

SQL 2005 to SQL 2000 Linked server. Test connection fails when running from standalone sql 2005 management studio.

Posted on 2013-01-16
8
596 Views
Last Modified: 2013-01-23
Hi Experts,

I added a Linked server from my SQL 2005 (Server2005.domain.com), Linked server is connected to (Server2000.domain.com).

---------------------------------------------------------------------------------------------------
Linked Server:     Server2000.domain.com
Server Type:        SQL server (SQL 2000)
Login name:        domain.com\TestAdmin

Local Login:         domain.com\TestAdmin "impersonate" enabled  (not impersonate is necessary ??)

For a login defined n the list above:  "Be made using the security context"
Remote Login:  domain.com\TestAdministrator (Domain Admin Account)
with Password:  *****************************

Server Roles:       Public, SysAdmin
-----------------------------------------------------------------------------------------------------------
I was able to run "Test Connection" Successful from (Server2005.domain.com) through SQL 2005 management studio.


Here is my dilemma:

I logon to (server2005.domain.com) from my PC/workstation using SQL 2005 managment studio, then I browe to Linked servers>Test Connection (failed).
By the way I am logged on as domain.com\TestAdmin on my PC as well.


Any ideas??

Thanks in advance!

Don
0
Comment
Question by:TFHDIT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 38785310
Hi,

Can you script out the linked server please?

Many thanks
  David
0
 

Author Comment

by:TFHDIT
ID: 38785355
/****** Object:  LinkedServer [SERVER2000.domain.com]    Script Date: 01/16/2013 16:49:05 ******/

EXEC master.dbo.sp_addlinkedserver @server = N'SERVER2000.domain.com', @srvproduct=N'SQL Server'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SERVER2000.domain.com',@useself=N'False',@locallogin=NULL,@rmtuser=N'domain.com\testadministrator',@rmtpassword='########'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SERVER2000.domain.com',@useself=N'True',@locallogin=N'sa',@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SERVER2000.domain.com',@useself=N'True',@locallogin=N'domain.com\TestAdministrator',@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SERVER2000.domain.com',@useself=N'True',@locallogin=N'domain.com\testadmin',@rmtuser=NULL,@rmtpassword=NULL

GO
EXEC master.dbo.sp_serveroption @server=N'SERVER2000.domain.com', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVER2000.domain.com, @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVER2000.domain.com, @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVER2000.domain.com', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVER2000.domain.com', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVER2000.domain.com', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVER2000.domain.com', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVER2000.domain.com', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVER2000.domain.com', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'SERVER2000.domain.com', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVER2000.domain.com', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVER2000.domain.com', @optname=N'use remote collation', @optvalue=N'true'


Thank you for taking a shot at this.

Don
0
 
LVL 35

Expert Comment

by:David Todd
ID: 38785362
Hi,

Try without these lines:
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SERVER2000.domain.com',@useself=N'True',@locallogin=N'sa',@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SERVER2000.domain.com',@useself=N'True',@locallogin=N'domain.com\TestAdministrator',@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SERVER2000.domain.com',@useself=N'True',@locallogin=N'domain.com\testadmin',@rmtuser=NULL,@rmtpassword=NULL

You may need to change this property from false to true
EXEC master.dbo.sp_serveroption @server=N'SERVER2000.domain.com', @optname=N'rpc', @optvalue=N'false'
GO


Regards
  David

PS And add a drop to the start of the script, and add the correct password <grin>
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 8

Expert Comment

by:Julianva
ID: 38786057
You are probably using the SQL2005 driver to create the linked server to SQL 2000, please post the error message
0
 

Author Comment

by:TFHDIT
ID: 38789810
"The test connection to the linked server failed"

>An exception occured whiled executing a Transact-SQL statement or batch.
  >(Microsoft .sqlserver.connectioninfo)
   
     >Named Pipes provider:  The pipe has been ended.

       Login failed for user '(null)'.  Reason.  Not associated with a trusted SQL server conenction.
       OLE DB provider "SQLNCLI" for linked server "Server2000" returned message
       "Communication link failure". (Microsoft SQL server, Error"109)

Thanks
0
 
LVL 35

Accepted Solution

by:
David Todd earned 500 total points
ID: 38789977
Hi,

With all the security mapping you've tried, to me it looks like you are trying to connect to the remote machine as null - see the three lines I suggested you delete in the script, add a drop to the top of the script and re-run. Have you done that yet?

HTH
  David
0
 

Author Comment

by:TFHDIT
ID: 38790152
Hi David,

Yes, I did remove these lines.  Unfortunately I am sill unable to connect to the linked server from my workstation.  

But if I rdp into to server2005.domain.com> launch 2005 SQL management studio> view server objcts\Linked servers.
Only then I am able to browse to linked servers without any errors.

Issue Summary:

>"server2005.domain.com" (sql2005)
>Linked server/database SOURCE "server2000.domain.com (sql2000)

**When connecting to sql management studio as domain.com\testadmin from server2005.domain.com......Test Connecton Succcessful

**When conneting to sql management studio as domain.com\testadmin to server2005.domain.com from my workstation......Test Connection Failed

Thanks,
Don
0
 
LVL 8

Expert Comment

by:Julianva
ID: 38791355
There might not be a trust relationship between your domains, to test create an odbc
connection from the sql 2005 server to connect to the sql 2000 server. the account that you are using to connect must also exist on the 2000 server,

another option is to use a sql account and not a windows account. sql accounts and password
must be the same on both servers.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question