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
591 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
 
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

757 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now