Linked Server Problems HELP HELP HELP !

having a LOT of problems with SQL server linked servers.
I have a server running Windows Server 2003. (Server A)
I want to run a query from a different server (Server B) using query
analyser.
E.g SELECT * FROM [SERVER A].DBName.dbo.tablename
keep getting the following message
Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection.
looked through various pages/groups etc.
my authentication mode is set to mixed.
still getting the message.
starting to annoy me - a LOT.!!
Help please!!!
THanks in advance.
Paul
Can you post a script for set up linked server?
<paul_30s@.hotmail.com> wrote in message
news:1140521634.869568.131030@.g14g2000cwa.googlegr oups.com...
> having a LOT of problems with SQL server linked servers.
> I have a server running Windows Server 2003. (Server A)
> I want to run a query from a different server (Server B) using query
> analyser.
> E.g SELECT * FROM [SERVER A].DBName.dbo.tablename
> keep getting the following message
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection.
> looked through various pages/groups etc.
> my authentication mode is set to mixed.
> still getting the message.
> starting to annoy me - a LOT.!!
> Help please!!!
> THanks in advance.
>
|||how?
|||This example of set up linekd server to Access (Lookup these SP in the BOL)
USE master
GO
EXEC sp_addlinkedserver
@.server = 'test_Access1',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = 'C:\WINDOWS\Desktop\uritennis.mdb'
GO
sp_linkedservers
sp_dropserver 'test_Access1', 'droplogins'
USE master
GO
EXEC sp_addlinkedsrvlogin
@.rmtsrvname = 'test_Access1',
@.useself = false,
@.locallogin = null,
@.rmtuser ='admin',
@.rmtpassword =null
USE master
GO
SELECT * FROM OPENQUERY( test_Access1, 'select * from table1')
<paul_30s@.hotmail.com> wrote in message
news:1140522241.310199.137970@.g47g2000cwa.googlegr oups.com...
> how?
>
|||done all this stuff. still same problem.
is there anything on the server that i need to check ?
|||Have you ran it on linked server?
exec sp_serveroption 'SERVER','data access','true'
<paul_30s@.hotmail.com> wrote in message
news:1140523365.016551.291200@.g44g2000cwa.googlegr oups.com...
> done all this stuff. still same problem.
> is there anything on the server that i need to check ?
>
|||still the same.
|||It seems like YOUR server is using mixed authorization but LINKED server is
not (trusted connections only)
<paul_30s@.hotmail.com> wrote in message
news:1140521634.869568.131030@.g14g2000cwa.googlegr oups.com...
> having a LOT of problems with SQL server linked servers.
> I have a server running Windows Server 2003. (Server A)
> I want to run a query from a different server (Server B) using query
> analyser.
> E.g SELECT * FROM [SERVER A].DBName.dbo.tablename
> keep getting the following message
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection.
> looked through various pages/groups etc.
> my authentication mode is set to mixed.
> still getting the message.
> starting to annoy me - a LOT.!!
> Help please!!!
> THanks in advance.
>
|||both servers are using mixed authentication.
|||paul_30s@.hotmail.com wrote:
> still the same.
>
Have you tried using a SQL user instead of an Windows/NT user account?
If you want to use integrated security (windows user) you have to ensure
that security account delegation is enabled (and that's something that
has to be set up in your Active Directory).
You can read more about security account delegation in Books On Line -
look for "Security Account Delegation".
Regards
Steen