Error 18452 Login Failed for user

I am trying to connect to a linked server and keep getting the error Login failed.  Not associated with a trusted SQL Server connection.

When I look at the server the user has permission.  There are two servers on this box and I am able to connect just fine to the first server, but the test server is not letting me connect even though it is a duplicate of the first.

Any ideas?

Thanks
running32Asked:
Who is Participating?
 
JesterTooCommented:
OK, when you created the linked server did you do similar to the following script?


------------------------------------------------------------------------------------------------------
-- Step1.  Link to SQLV1\Instance1 from SQLV1\Instance2  (run this on SQLV1\Instance2)
------------------------------------------------------------------------------------------------------

   exec sp_AddLinkedServer
           @server     = 'MyServer',
           @srvproduct = 'SQLServer OLEDB Provider',
           @provider   = 'SQLOLEDB',
           @datasrc    = 'SQLV1\Instance1'

   exec sp_AddLinkedSrvLogin
           @rmtsrvname = 'MyServer',
           @useself    = 'true'
 
0
 
rafranciscoCommented:
Make sure that the user that you are using to connect to the first server is also defined on the test server.  Go to your test server and look under the Security folder and see if the user is defined there.
0
 
JesterTooCommented:
I'm guessing you are trying to authenticate to the server using your Windows account and not a SQL Server user account.  That means most likely that you didn't configure SQL Server to use "mixed mode" authentication.  Follow these steps to check/change the configuration (or simply use a valid SQL Server user login instead of your Windows account).

    1  Open The SQL Server Enterprise Manager
               Start menu > Programs > Microsoft SQL Server > Enterprise Manager)
    2  Expand the tree-control until you can your server
    3  Right click on the server name and "SQL Server Properties (Configure)" window will open
    4  Select the Security tab
    5  For "Authentication", select "SQL Server and Windows"

HTH,
Lynn
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
running32Author Commented:
Authentication is set to SQL Server and Windows.

I am using a SQL Server Login and it is defined under the security tab.

thanks
0
 
running32Author Commented:
No I set the linked server up through enterprise manager.
0
 
rafranciscoCommented:
Can you add the test server in Enterprise Manager using your SQL Server login account?
0
 
running32Author Commented:
Did that. I'll have to get with our network guy and see why permissions are different.
0
 
rafranciscoCommented:
Do you still get the error when you connect through the linked server?
0
 
running32Author Commented:
Yes, I still got the error
0
 
rafranciscoCommented:
Can you post the properties of your linked server as defined in Enterprise Manager, most importantly the one defined under the Security tab.
0
 
JesterTooCommented:
When you created the linked server did you also create the required "linked server login"?  See my script examples for how to do it in QA.
0
 
running32Author Commented:
For User defined I check Be made using this security contact and put in the remote login with password.  It is the same way I set up the other sql server which is on the same server.   The persmssions on the users are identidal cao I'm comfused why it will connect to one of the sql servers and not the other?

Thanks
0
 
rafranciscoCommented:
Were you able to add the test server in Enterprise Manager?  What's the operating system of the test server?
0
 
running32Author Commented:
The operating system is window 2003.  Yes I could add the test server to Enterprise Manager.  
0
 
running32Author Commented:
Thanks it's working now.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.