Linked server security: ogin failed for user ''sqllinked''. Reason: Not associated with a trusted SQL Server connection

• laker_42

  SSCarpal Tunnel

  Points: 4785

  More actions

  October 11, 2005 at 6:06 am

  #187968

  Hey everyone,

  I know this same problem has been posted a couple of times & I read all of the threads but it doesn't seem that any of them were resolved.  At least the fix wasn't posted back.  Here is my setup.

  server 1 win 2k3, SQL Server 2000 SP 4  MSDTC network connections enabled

  server 2 win 2k, SQL Server 2000 SP 4

  Both have mixed mode authentication enabled.  Both have a SqlLinked SQL Server login that has Sysadmin privileges.  Both are in the same domain.

  I created a linked server on server 2 to server 1 using sp_Addlinkedserver.  I then mapped my windows NT login to the SqlLinked login using

  sp_addlinkedsrvlogin 'Server1', false, 'windowsNt\User', 'sqllinked', 'userpassword'

  When I run sp_helplinkedsrvlogin 'Server1' I get the following:

  Server2 NULL 0 defaultuser

  Server2 windowsnt\user 0 sqllinked

  When I run any queries from Server1 to Server2 I get the error:

  Msg 18452, Level 14, State 1, Line 1

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

   

  What am I missing???  Any ideas???

  Thanks!

  John

• Jo Pattyn

  SSC-Dedicated

  Points: 32439

  More actions

  October 11, 2005 at 12:13 pm

  #596619

  Sure your security mapping is set right?

  if you login , what does

  SELECT 'The current user is: '+ convert(char(30), CURRENT_USER)return?

• laker_42

  SSCarpal Tunnel

  Points: 4785

  More actions

  October 11, 2005 at 12:17 pm

  #596621

  No, I didn't try that because I never received any error messages issuing the statement the way I had it.  I would think that if there was an issue, it wouldn't have completed successfully, correct?

  Thanks

  John

• Rudyx - the Doctor

  SSC-Forever

  Points: 43695

  More actions

  October 12, 2005 at 12:43 pm

  #596912

  Are you using domain user accounts for the SQL Server Service and SQL Agent Service ? Are the accounts the sameon each server ? Are the service accounts set up with LocalAdministrator authority on their own respective SQL Servers ?

  RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

• laker_42

  SSCarpal Tunnel

  Points: 4785

  More actions

  October 12, 2005 at 2:01 pm

  #596929

  Yes, I am using a domain account for the SQL Service & the SQL Agent Service.  The SQL Service account is a local administrator on both SQL Servers.  The SQL Agent Service is running with an domain admin account.

  John