Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
601 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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
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 1500 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

670 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