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
595 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
  • 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

828 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