locked
Linked Server Login Error RRS feed

  • Question

  • All,

    We have two SQLServer Default Instances with SQLServer 2008 on two different physical machines - SrvApps and SrvSSIS. On SrvApps, the SQLServer Service runs with the Local System Account but on SrvSSIS, the SQLServer Service runs with a DomainAccount - "SQLDom\SQLSrvLogin". Both the machines are in the same Domain SQLDom and the login exists in both the servers.

    I tried establishing a linked server on SrvSSIS against SrvApps and I tried various ways and each of them keep failing. I want to use the Windows Authenticated Login "SQLDom\SQLSrvLogin" since it exists on both the servers with relevent permissions. So, in the Security screen, I entered "SQLDom\SQLSrvLogin" for the local login and checked the Impersonate box. Listed are the details with the relevent errors:

     

    Any ideas?

    -- Selected "Not to be Made" Radio button
    USE [master]
    GO
    EXEC master.dbo.sp_addlinkedserver @server = N'SrvApps', @srvproduct=N'SQL Server'
    GO
    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SrvApps', @locallogin = N'SQLDom\SQLSrvLogin', @useself = N'True'
    GO
    Access to the remote server is denied because no login-mapping exists. (.Net SqlClient Data Provider)


    -- Selected "Be Made without using a Security Context" Radio button

    USE [master]
    GO
    EXEC master.dbo.sp_addlinkedserver @server = N'SrvApps', @srvproduct=N'SQL Server'
    GO
    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SrvApps', @locallogin = N'SQLDom\SQLSrvLogin', @useself = N'True'
    GO
    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SrvApps', @locallogin = NULL , @useself = N'False'
    GO

    The OLE DB provider "SQLNCLI10" for linked server 'SrvApps' reported an error. Authentication failed.
    Cannot initialize the data source object of OLE DB provider "SQLNCLI10" for linked server 'SrvApps'.
    OLE DB provider "SQLNCLI10" for linked server 'SrvApps' returned message "Invalid authorization specification". (.Net SqlClient Data Provider)


    -- Selected "Be Made using the login's current Security Context" Radio button

    USE [master]
    GO
    EXEC master.dbo.sp_addlinkedserver @server = N'SrvApps', @srvproduct=N'SQL Server'
    GO
    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SrvApps', @locallogin = N'SQLDom\SQLSrvLogin', @useself = N'True'
    GO
    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SrvApps', @locallogin = NULL , @useself = N'True'
    GO

    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (.Net SqlClient Data Provider)


    Also, in the last option, why is it using 'NT AUTHORITY\ANONYMOUS LOGON' since the SQLServer service on SrvSSIS runs off
    of "SQLDom\SQLSrvLogin" Login?

    Thanks,
    MS

    Friday, May 4, 2012 5:07 PM

Answers

  • Hi MSSQLServer,


    You could use SQL Server Management Studio or T-SQL sentences to create linked server. In the process of create linked server, You would need to map a local server login to a remote server login. On the right side of the Security page, click the Add button. Under Local Login, select a local login account to connect to the remote server. Check Impersonate if the local login also exists on the remote server. Alternatively, if the local login will be mapped to a remote SQL Server login you must supply the Remote User name and Remote Password for the remote server login.

    Please follow below link to create linked server:
    http://msdn.microsoft.com/en-us/library/ff772782(v=sql.105).aspx
    http://msdn.microsoft.com/en-us/library/aa560998(v=BTS.10).aspx.


    Thanks,
    Maggie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    • Marked as answer by Maggie Luo Sunday, May 13, 2012 3:25 PM
    Tuesday, May 8, 2012 2:38 PM

All replies

  • The third option is the only that seems valid here, but the issue is probably on the AD side, seems that this account is not properly registered in it (SPN not registered in AD).

    Both machines need to trust each other, Kerberos at times need to set for this purpose.

    See if this blog post helps http://sql-articles.com/articles/dba/linked-server-part-2/


    Arthur My Blog

    Tuesday, May 8, 2012 1:58 PM
  • Hi MSSQLServer,


    You could use SQL Server Management Studio or T-SQL sentences to create linked server. In the process of create linked server, You would need to map a local server login to a remote server login. On the right side of the Security page, click the Add button. Under Local Login, select a local login account to connect to the remote server. Check Impersonate if the local login also exists on the remote server. Alternatively, if the local login will be mapped to a remote SQL Server login you must supply the Remote User name and Remote Password for the remote server login.

    Please follow below link to create linked server:
    http://msdn.microsoft.com/en-us/library/ff772782(v=sql.105).aspx
    http://msdn.microsoft.com/en-us/library/aa560998(v=BTS.10).aspx.


    Thanks,
    Maggie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    • Marked as answer by Maggie Luo Sunday, May 13, 2012 3:25 PM
    Tuesday, May 8, 2012 2:38 PM