[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 606
  • Last Modified:

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

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
TFHDIT
Asked:
TFHDIT
  • 3
  • 3
  • 2
1 Solution
 
David ToddSenior DBACommented:
Hi,

Can you script out the linked server please?

Many thanks
  David
0
 
TFHDITAuthor Commented:
/****** 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
 
David ToddSenior DBACommented:
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
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
JulianvaCommented:
You are probably using the SQL2005 driver to create the linked server to SQL 2000, please post the error message
0
 
TFHDITAuthor Commented:
"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
 
David ToddSenior DBACommented:
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
 
TFHDITAuthor Commented:
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
 
JulianvaCommented:
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now