Linked Server

  • I am having error on my linked server. from x to y.

    OLE DB provider "SQLNCLI" for linked server "x" returned message "Communication link failure".

    Msg 10054, Level 16, State 1, Line 0

    TCP Provider: An existing connection was forcibly closed by the remote host.

    Msg 18456, Level 14, State 1, Line 0

    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

    when I am tryinig to test a linked server query by loging into server x it is working fine.

  • What syntax are you using to select from the linked server?

    What type of linked server is it (Is it SQL\Oracle etc?)

    Also, have you verified that you are authenticating using the correct username and password?

  • NicDX (10/28/2008)


    verified that you are authenticating using the correct username and password?

    You must ensure that the username you are connecting with ie service account on one server has sufficient rights on the other.

    I have see it before where for some reason i couldnt use the 2k5 gui to setup a ls to 2k (had the same error as you). Worked fine when i scripted it though.

    Adam Zacks-------------------------------------------Be Nice, Or Leave

  • Be more specific for your problem ...!?

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • hi

    Both my servers are sql servers.in linked servers I used the option 'connection to be made using the current security context.

    I lgged into the server with admin id then the query is working .But when i tried with a user id in ssms it is not working

  • binu.ma (10/28/2008)


    I lgged into the server with admin id then the query is working .But when i tried with a user id in ssms it is not working

    So it the user your trying to execute as. It obviously does not have some sort of required permission.

    From here you have two options. Either you investigate the permissions required to execute your query on the remote server (through the LS). Or you can set up your LS for ptoxy, ie configure it to connect as an admin account.

    Let me know if thats helps or if your not sure. 🙂

    Adam Zacks-------------------------------------------Be Nice, Or Leave

  • The NT Authority\Anonymous Logon is the clue here I think, as I've had this before.

    I believe you're using SSMS locally on PC A, to connect to Server A, and then run a query against Server B, yes? This will fail, because even though you've connected to server A with the correct credentials from PC A, these credentials get masked when running queries in that way against Server B, which is why the Anonymous Logon is used.. To get around this, you will have to run queries against Server B from the console on Server A (ie, logged on locally to Server A, not connecting through SSMS).. If you wrote the linked server query to use impersonation, that might work, althought I've not tried that.

  • liteswitch (10/30/2008)


    The NT Authority\Anonymous Logon is the clue here I think, as I've had this before.

    quote]

    Also, good practive is to run the SQL services as a domain account.

    Makes it easier too troubleshoot and problems like this wont occur.

    Adam Zacks-------------------------------------------Be Nice, Or Leave

  • can we coonect to the other sql server without using linked server? if yes, then some one tell me how it is possible

  • Use OPENROWSET

    http://msdn.microsoft.com/en-us/library/ms190312.aspx

  • SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY not working with Linked Server or Remote server ???

    Hello Friends,

    I have faced a problem using SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY with Linked server

    Let me explain with example as below

    Question:

    We have set of applications which are running on SQL 2000, due to some reason we need to migrate sub set of applications to SQL 2008.

    Now the few applications running on SQL 2008 and few on SQL 2000, applications running on SQL 2008 using some of databases which lie on SQL 2000 with Linked server

    Now the problem is the some triggers and stored procedures which need to insert the values generated from SQL 2008 to SQL 2000 databases using functions SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are not working in this scenario.

    We are fixing this problem by writing another select statement for selecting identity based on unique parameters in that scope.

    Answer:

    The scope of the @@IDENTITY function is current session on the local server on which it is executed. This function cannot be applied to remote or linked servers. To obtain an identity value on a different server, execute a stored procedure on that remote or linked server and have that stored procedure (which is executing in the context of the remote or linked server) gather the identity value and return it to the calling connection on the local server.

    Same for SCOPE_IDENTITY, IDENT_CURRENT also……

    If anybody has any idea, please share as comment…

    Varinder Sandhu,
    http://www.varindersandhu.in/

  • Hi,

    This is the hint that helped me!

    Login locally to server A open a SMSS there and create the linked server with impersonate to server B.

    THX.

  • HI Team,

    I am trying to read Excel file from SQL server using non- sysadmin  sql login .

    select * from OPENROWSET('MICROSOFT.ACE.OLEDB.12.0',
    'Excel 8.0;HDR=YES;DATABASE=L:\Linked Server\RT_CargaPolizas.xls',sheet$)

    Msg 7415, Level 16, State 1, Line 5
    Ad hoc access to OLE DB provider 'MICROSOFT.ACE.OLEDB.12.0' has been denied. You must access this provider through a linked server.

    While trying to access using linked server :

    SELECT * FROM OPENQUERY(ExcelServer2, 'SELECT * FROM [Sheet$]')

    Msg 7416, Level 16, State 2, Line 8
    Access to the remote server is denied because no login-mapping exists.

    Please hlep me I am in a big trouble . I have tried all possible option mention on google but there is no joy.

    Things I did on server : 

    ***************************************************
    EXEC sp_addlinkedserver
        @server = 'ExcelServer2',
        @srvproduct = 'Excel',
        @provider = 'Microsoft.ACE.OLEDB.12.0',
        @datasrc = 'L:\Linked Server\RT_CargaPolizas.xls',
        @provstr = 'Excel 8.0;IMEX=1;HDR=YES;User ID=domain\ysername'
           *************************************************************
                  EXEC sp_addlinkedsrvlogin 'ExcelServer2', 'false',
        'sqllogin- non-sysadmin', 'domain\ysername','EbA6at(@qwE'
    *****************************

    Regards,
    Megha Chandak

  • megha.chandak88 - Friday, February 17, 2017 9:42 AM

    HI Team,

    I am trying to read Excel file from SQL server using non- sysadmin  sql login .

    select * from OPENROWSET('MICROSOFT.ACE.OLEDB.12.0',
    'Excel 8.0;HDR=YES;DATABASE=L:\Linked Server\RT_CargaPolizas.xls',sheet$)

    Msg 7415, Level 16, State 1, Line 5
    Ad hoc access to OLE DB provider 'MICROSOFT.ACE.OLEDB.12.0' has been denied. You must access this provider through a linked server.

    While trying to access using linked server :

    SELECT * FROM OPENQUERY(ExcelServer2, 'SELECT * FROM [Sheet$]')

    Msg 7416, Level 16, State 2, Line 8
    Access to the remote server is denied because no login-mapping exists.

    Please hlep me I am in a big trouble . I have tried all possible option mention on google but there is no joy.

    Things I did on server : 

    ***************************************************
    EXEC sp_addlinkedserver
        @server = 'ExcelServer2',
        @srvproduct = 'Excel',
        @provider = 'Microsoft.ACE.OLEDB.12.0',
        @datasrc = 'L:\Linked Server\RT_CargaPolizas.xls',
        @provstr = 'Excel 8.0;IMEX=1;HDR=YES;User ID=domain\ysername'
           *************************************************************
                  EXEC sp_addlinkedsrvlogin 'ExcelServer2', 'false',
        'sqllogin- non-sysadmin', 'domain\ysername','EbA6at(@qwE'
    *****************************

    Regards,
    Megha Chandak

    You said you have tried all possible options but we don't know what all you have tried.
    Do you have Ad Hoc Distributed Queries enabled? That's usually the first thing to check for that error.
    And what are the settings for the provider?

    Sue

  • HI Sue,

    Sorry for delayed in revert.. 

    Steps I did on server : 
    1) Installed Microsoft .ACE.OLEDB.Provider.12.0 on server 
    2) Microsoft Office 365 proplus en-us

    3) Added the login to Linked server :
    EXEC sp_addlinkedsrvlogin 'ExcelServer2', 'false',
        'NON Sysadmin sql login', 'domain\service account','xfdgdg!@EbA6atE'

    4)  Provided 
    ADMINISTER BULK OPERATIONS at server level, permission to NON sysadmin SQL login and domain\service account.

         Refer this link :  http://stackoverflow.com/questions/4777906/sql-server-2008-openrowset-permission-issue
           
    ***************************************************************************************************
    5) Enable it on server 
       sp_configure 'show advanced options', 1
    GO
    RECONFIGURE
    GO
    sp_configure 'Ad Hoc Distributed Queries', 1
    GO
    RECONFIGURE
    **************************************************************************************************************
    http://dba.stackexchange.com/questions/61739/help-with-sql-server-error-ad-hoc-access-to-ole-db-provider-microsoft-ace-oled

    6) 
    Msg 7415, Level 16, State 1, Line 1 Ad hoc access to OLE DB provider 'Microsoft.ACE.OLEDB.12.0' has been denied. You must access this provider through a linked server.

    Determine if "Disallow adhoc access" is enabled for your provider. This can be found in SQL Management Studio via the following navigation path:
    Server Objects/Linked Servers/Providers/Microsoft.ACE.OLEDB.12.0
    Right click the "Microsoft.ACE.OLEDB.12.0" provider and select "Properties" from the context menu.
    In the pop-up window, make sure that the "Disallow adhoc access" checkbox is cleared.
    Alternatively, you can just set it to disabled by using the following SQL:

    sp_configure 'show advanced options', 1
    GO
    RECONFIGURE
    GO
    sp_configure 'Ad Hoc Distributed Queries', 1
    GO
    RECONFIGURE

    Next, verify that the Registry key is set. In Regedit, navigate as follows:

    Windows Registry Editor Version 5.00

    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQLServer\MSSQL<major version>_<minor version>.<instance name>\Providers\Microsoft.ACE.OLEDB.12.0]
    "DisallowAdhocAccess"=dword:00000000

    ***************************************************************************************
    Link I refer :
    http://dba.stackexchange.com/questions/61739/help-with-sql-server-error-ad-hoc-access-to-ole-db-provider-microsoft-ace-oled
    http://stackoverflow.com/questions/4777906/sql-server-2008-openrowset-permission-issue
    https://www.excel-sql-server.com/excel-import-to-sql-server-using-linked-servers.htm
    https://support.microsoft.com/en-us/help/814398/prb-error-7399-when-you-run-a-linked-server-query-that-uses-the-ole-db-provider-for-microsoft-jet

    ???B?D

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply