Linked server fails with login error

Anonymous
2020-09-02T19:55:45.907+00:00

am trying to create a linked server From SQL server to remote sql server. I have read access to the remote sql server. But the linked server is failing with login error. Please review the error and images attached and advise what else needs to be checked. " ------------------------------ The linked server has been updated but failed a connection test. Do you want to edit the linked server properties? ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ Login failed for user 'CS\Z*****'. (Microsoft SQL Server, Error: 18456) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=10.50.4042&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476 "![22305-linkedserver1.png][1]![22306-linkedserver2.png][2]![22234-linkedserver3.png][3] [1]: /api/attachments/22305-linkedserver1.png?platform=QnA [2]: /api/attachments/22306-linkedserver2.png?platform=QnA [3]: /api/attachments/22234-linkedserver3.png?platform=QnA

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,765 questions
0 comments
Accepted answer
  1. Anonymous
    2020-09-03T13:55:25.453+00:00

    I changed the security context to us "made using the logins security context" and it works

    1 person found this answer helpful.

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 101.4K Reputation points MVP
    2020-09-02T21:28:14.62+00:00

    Since you have been so ambitious to hide everything, it is difficult to draw correction conclusions. But the error message suggests that the name of the remote login includes a backslash. This indicates that you may be trying to use Windows user for the remote login. That is not possible. It must be an SQL login. (And the remote instance must be configured to permit SQL authentication.)

    0 comments
  2. AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor
    2020-09-03T03:27:53.223+00:00

    Hi DeepKaur-3078,
    Could you please share us more information? The screenshot you posted is too vague to analyze your issue.
    Is the login failed user ‘CS\Z*’ the remote user of the linked server?
    Please make sure the remote user you used can connect to the remote SQL Server.
    In addition, here is an article which might help you to create a linked server.

    Best Regards,
    Amelia

    0 comments
  3. Anonymous
    2020-09-03T13:40:43.623+00:00

    I am using SQL server, CNNDP004GA,2486 as Linked server.
    I tried both CS\Z1234 and Z1234 as login.
    I can access all databases on CNNDP004GA,2486 with windows authentication using CS\Z1234 .

    Now I get the following error:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    TCP Provider: The requested name is valid, but no data of the requested type was found.

    OLE DB provider "SQLNCLI10" for linked server "CNNDP004GA,2486" returned message "Login timeout expired".
    OLE DB provider "SQLNCLI10" for linked server "CNNDP004GA,2486" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". (Microsoft SQL Server, Error: 11004)

    0 comments
  4. Erland Sommarskog 101.4K Reputation points MVP
    2020-09-03T21:21:38.967+00:00

    The error "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". means that the remote server was not reachable. Since it apparent was reachable earlier (since you got a login error), this seems like a transient problem. Maybe the remote server is down for maintenance. Maybe someone tripped on a cable in the server room.

    If you set up login mapping, there must be an SQL login on the remote server for it work. Whatever Windows login you can use does not matter in this case.

    On the other hand, now you have selected self-mapping and that is lot better for security. This means that if any users that logs in to the local server and accesses the linked server needs to have a valid login on the remote server as well.

    0 comments