Search code examples
sql-serversql-server-2008t-sqlsql-server-2005linked-server

SQL Server pulling data from different server

Possible Duplicate:
How can I connect to an external database from a sql statement or a stored procedure?

I have a database server 'A'(SQL Server 2008) and database server 'B' (SQL Server 2005).

I need to pull some data from a database on server 'B' when I am on server 'A'.

I tried this query on Server 'A':

SELECT *
FROM  [ServerName].[DataBaseName].[dbo].[TableName]

I am getting this error :

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

Is there some thing wrong with my query or do we need to change any setting on the server so that I will be able to access the database.

Solution

  • You might still need to add a login for the linked server...

    exec sp_addlinkedsrvlogin ‘LinkedServer’, 'true'

    This will cause your 'A' server to try to impersonate your login on server B.

    There's more detailed info / reference here: http://blogs.msdn.com/b/sql_protocols/archive/2006/08/10/694657.aspx