Linked server very very strange behavior RRS feed

  • Question

  • Hi, I am experiencing something very strange. I have setup a linked server between a SQL 2005 on my laptop and another on a virtual machine. I have a job that calls a SP which executes some TSQL on the remote server. Everything was working fine until yesterday, security configured "Be made using the current login's security". I have both logons with same password on both machine with  sysadmin role.
    Yesterday, on the virtual machine, in computer management, I added a new user for doing some tests on a .net application which has nothing to do with SQL. 2 hours later, switched to my laptop to do some coding in sql, I noticed that the job is failing. Read the history, I saw that it was failing with the following message :
    Executed as user: laptop\Salam. Login failed for user VM\Saam'. [SQLSTATE 28000] (Error 18456).  The step failed.
    So I am logged on my laptop as "laptop\Salam" which exists on the other one with the same password. I can not understand why SQL is mapping to the user "VM\Saam'", what made sql look for this logon? why he is aware of it.
    Thanks in advance
    Monday, February 15, 2010 10:39 PM

All replies

  • You need to have same user name and password. In this case in laptop you have created the user as SALAM and in VM it's as Saam, so drop and re-create the user with same username and password.
    Vidhya Sagar. Mark as Answer if it helps!
    Tuesday, February 16, 2010 11:22 PM
  • Sagar, you didn't get the idea.
    Ihave my laptop with user Salam
    I have the destination server VM which has also user "Salam" with exact password.
    My job and SPs were working fine
    The user Saam is cretaed on VM and not intendedto be used in SQL and I don't want to use it. I created this user on VM for completly different purpose for testing a .Net application which doesn't need toaccess SQL.

    The problem is that whenever I try to log FROM MY LAPTOP (session always opened with user "salam" inside SQL SSME using, the destination sql, maps by itself my login "Salam" to Saam.

    So nobody has told or configured anything for user Saam on VM why SQL is using it. 
    Wednesday, February 17, 2010 8:10 AM
  • Just to add another thing that might help understand better, Saam user on VM is never declared in SQL logon, session on VM is opened with Salam user and not Saam
    Wednesday, February 17, 2010 8:18 AM
  • Oh ok, i might took it in another way.
    Have you tried to drop and recreat the user again ? User mapping is done based on the SID and not by the name, not sure why this is happening to you

    Vidhya Sagar. Mark as Answer if it helps!
    Thursday, February 18, 2010 6:55 PM