SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Login failed for Linked Server - trusted conn?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ssg31415926
Starting Member

United Kingdom
4 Posts

Posted - 10/22/2008 :  10:54:23  Show Profile  Reply with Quote
My main system runs a SQL Server 2005 DB on a Server 2003 machine in my domain. I need to query a SQL 2000 DB on a Windows 2000 Server machine that's in a workgroup. I've tried to set up a Linked Server by running this (I've changed the strings for security):

sp_addlinkedserver @server= 'Server01', @srvproduct= N'SQL Server'

GO

sp_addlinkedsrvlogin @rmtsrvname = 'Server01'
, @useself = 'FALSE'
, @locallogin = NULL
, @rmtuser = 'AppLogin'
, @rmtpassword = 'Password'
and got back "Command(s) completed successfully".

The server has appeared under Linked Servers in Mgmt Studio. When I try to open it up, I get:

Microsoft SQL Server Management Studio
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
Additional information:
----> An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
---->TCP Provider: An existing connection was forcibly closed by the remote host.
Login failed for user 'AppLogin'. Reason: Not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 10054)

I'm not sure where to start. I'm no SQL expert and our DBA hasn't done Linked Servers before.

Edited by - ssg31415926 on 10/22/2008 16:11:08

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 10/22/2008 :  11:34:38  Show Profile  Reply with Quote
You need to have Security account delegation setup for windows login to work in Linked Server. Try with SQL Login. Make sure it exists in both server with necessary privileges.
Go to Top of Page

ssg31415926
Starting Member

United Kingdom
4 Posts

Posted - 10/22/2008 :  16:24:09  Show Profile  Reply with Quote
Following your advice, I've created a SQL Login on my 2005 server with the same name and password as the SQL login on the SQL 2000 server (the id I used to set up the link). I'm now getting a different error message which suggests that I'm getting a bit further:

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
Additional information:
----> An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
----> The EXECUTE permission was denied on the object 'xp_prop_oledb_provider', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)

Would I be right in thinking I need this permission set on the SQL 2005 server? Or is it the SQL 2000 server? I'm trying to set up the link from the 2005 svr to the 2000 svr
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 10/22/2008 :  17:43:35  Show Profile  Reply with Quote
GRANT EXECUTE ON sys.xp_prop_oledb_provider TO [SQLLOGIN]
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000