Post reply

Trusted connection across 2 SQL servers

  • August 3, 2005 at 2:27 am

    #86982

    Hi all,

    Stupid question:

    I want to select data from another SQL Server (for now, I'll be happy to do this through Query Analyzer), so I have something like:

    select * from [server].[database].[dbo].

    Everytime, I get an error message:

    Server: Msg 18452, Level 14, State 1, Line 1

    Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

    However, when I manually connect to the other server using Windows authentication (for the same user), and I run:

    select * from [database].[dbo].

    it works.

    I saw similar posts on here before, but nothing I tried seemed to fix my problem.

    Any idea what might be wrong?

    Thanks,

    Calli.

     

    [and I just realised I posted this to the wrong forum, apologies for that!]

  • August 3, 2005 at 3:48 am

    #579069

    Stupid question back

    Do you have a linked server connection between the servers?

  • August 3, 2005 at 3:53 am

    #579071

    Yes, I do.

    Sorry, I meant to say that...   

     

    When I log in as "sa" (or another user which I've set up on both databases) and do the same query, it works perfectly.

    I just can't seem to get it to work with NT authentication.

  • August 4, 2005 at 2:57 am

    #579465

    Have you got your Windows account registered on both servers and does it have permissions on the databases you are querying?

    Just a thought.

    Cheers,

    Sim Lever

  • August 4, 2005 at 3:02 am

    #579467

    Hi there,

    Yes, it's a domain account...and both servers are on the same domain.

    Also, yes, this domain account has got access to both server - and the relevant tables on both servers.  If I open up a connection to the second database (for the same user), I can retrieve the info.  I just can't seem to do it from a query from the first database...

    Thanks,

    Calli.

  • August 4, 2005 at 8:11 am

    #579588

    Calli,

    I do not think you will get it to work using windows authentication on the source and target server.  On the target server, setup a SQL login and give it select accesss to the table(s) in your query.  On the source server you have to configure your server link as Not be made, setup a mapping with local login (put the windows account), and the remote user and remote password.  This will work.  I have not been able to get a server link to work using the windows account on source and target server.  What do you think and does it work?  There are other ways to configure server links, but this is pretty secure considering the options.  This only allows that 1 user to use the server link.  If you can get it to work with windows accounts on source and target, I would sure like to know how you did it.

    Good luck,

    John

  • August 4, 2005 at 8:38 am

    #579603

    Calli,

    Have you tried OpenRowset?

    select *

    from openrowset('SQLOLEDB','SERVER=ServerName;database=DatabaseName;UID=***;PWD=***', 'select * from TableName')

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply