locked
Login failed setting up linked server. RRS feed

  • Question

  • We are trying to set up a linked server to be able to query "Prod" from "Dev".  Both SQL Server are 2008  ( I know, we need to upgrade).  The user that needs to Query Prod from Dev, has read only permissions on the Prod server currently.

    Here is the propertires of the linked server being set up on the Dev server.

    General:Server type = "SQL server"

    Security:Local Login'= domain\devUser, Impersonate unchecked, remote User = domain\SQLX, PW = :*** connections will be made using the login's current security context.

    RPC and RPC out set to true.

    When I test connection, Error message 'Login failed for domain\SQLX'

    What does permission does domain/devUser need on Prod?  What permissions does domain/SQLX need on Prod or Dev if it matters.

    Also, it seems serurity and permission is always a headache for me and for many of my peers...  what is a could book on permissions, Kerberos, logins and security..  I want to master this area...

    Thanks,

    Mike

    Tuesday, October 9, 2018 3:04 PM

All replies

  • Please try the below, It may help you.

    https://dba.stackexchange.com/questions/43350/why-am-i-getting-a-login-failed-when-creating-this-linked-server

    Tuesday, October 9, 2018 3:07 PM
  • I followed the directions and got this error..

    Msg 18456, Level 14, State 1, Line 1
    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
    OLE DB provider "SQLNCLI10" for linked server "Prod" returned message "Invalid connection string attribute".

    I am running this code... and have public permissions as Server Roles, on "Prod" and currently the catalog I am trying to read from is "Standby / Read-Only"...   but yesterday it was up and I was getting similar issues...   Ill have to try again when its up

    Tuesday, October 9, 2018 3:40 PM
  • In your first case, it appears that you tried to set up a login mapping with an Windows login on the other side. That cannot work out. To log in to SQL Server with Windows authentication, you must first log in to Windows which validates your username and password. A linked server is no different from logging in from SSMS.

    There are two options:

    1) Users log in as them self, that is, self-mapping. This requires that users have a login on the prod server. And that Kerberos and domains have been set up to permit this.  (Judging from the error message in your second post, this is not the case.)

    2) Use login-mapping with an SQL login.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, October 9, 2018 9:41 PM
  • Hi Mike,

    The service account under which the SQL instance is running must be “trusted for delegation”, configured in Active Directory. If the service is running as “NT AUTHORITYSYSTEM” or “NT AUTHORITYNETWORK SERVICE”, the computer must be “trusted for delegation”. The user domain account must have permission to access both SQL instances from the client. Please refer to the documents from SQL Linked Server Query failed with “Login failed for user …”.

    Best Regards
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, October 12, 2018 9:01 AM