October 28, 2008 at 12:04 am
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.
October 28, 2008 at 5:38 am
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?
October 28, 2008 at 6:25 am
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
October 28, 2008 at 6:30 am
October 28, 2008 at 9:36 pm
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
October 29, 2008 at 3:17 am
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
October 30, 2008 at 1:51 pm
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.
October 31, 2008 at 3:08 am
liteswitch (10/30/2008)
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
February 23, 2010 at 4:47 am
can we coonect to the other sql server without using linked server? if yes, then some one tell me how it is possible
February 23, 2010 at 2:24 pm
Use OPENROWSET
February 7, 2011 at 8:12 am
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/
January 20, 2014 at 3:18 am
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.
February 17, 2017 at 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
February 17, 2017 at 11:26 am
megha.chandak88 - Friday, February 17, 2017 9:42 AMHI 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
February 28, 2017 at 5:54 am
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