Login failed for Linked Server - trusted conn?

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Author

Topic

ssg31415926
Starting Member

4 Posts

Posted - 2008-10-22 : 10:54:23

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.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-22 : 11:34:38

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.

ssg31415926
Starting Member

4 Posts

Posted - 2008-10-22 : 16:24:09

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

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-22 : 17:43:35

GRANT EXECUTE ON sys.xp_prop_oledb_provider TO [SQLLOGIN]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources