Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6632
  • Last Modified:

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
0
running32
Asked:
running32
  • 7
  • 5
  • 3
1 Solution
 
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
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now