The OLE DB provider "XXXX" for linked server "XXXXX" reported an error. Authentication failed.

Question

Hello Team,

I have some issue with link server object .

I got two servers SQLSERVER-0 and SQLSERVER-1. a linked server was created on each machine . When i tried on the SQLSERVER-0 to connect to the link server with an Read only sql account or windows account ( same account is available on the SQLSERVER-1 as sysadmin) , the following error appear :

Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "ALARMVIEW:Select * FROM AlgViewEnu WHERE DateTime > '2022-01-01 00:00:00.000' and DateTime<='2022-05-01 00:00:00.000'" against OLE DB provider "WinCCOLEDBProvider" for linked server "LnkRtDb_WinCCOLEDB".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "WinCCOLEDBProvider" for linked server "LnkRtDb_WinCCOLEDB" reported an error. Authentication failed.

Below the SQL code i tried :
select * from openquery([xxxxxx\WINCC],'select
MsgNr as MsgNr,
State as State,
CAST(DateTime AS datetime) as DateTime,
Ms as Ms,
Instance collate SQL_Latin1_General_CP1_CI_AS as Instance,
Flags1 as Flags1,
PValueUsed as PValueUsed,
PValue1 as PValue1,
PValue2 as PValue2,
PValue3 as PValue3,
PValue4 as PValue4,
PValue5 as PValue5,
PValue6 as PValue6,
PValue7 as PValue7,
PValue8 as PValue8,
PValue9 as PValue9,
PValue10 as PValue10,
PText1 collate SQL_Latin1_General_CP1_CI_AS as PText1,
PText2 collate SQL_Latin1_General_CP1_CI_AS as PText2,
PText3 collate SQL_Latin1_General_CP1_CI_AS as PText3,
PText4 collate SQL_Latin1_General_CP1_CI_AS as PText4,
PText5 collate SQL_Latin1_General_CP1_CI_AS as PText5,
PText6 collate SQL_Latin1_General_CP1_CI_AS as PText6,
PText7 collate SQL_Latin1_General_CP1_CI_AS as PText7,
PText8 collate SQL_Latin1_General_CP1_CI_AS as PText8,
PText9 collate SQL_Latin1_General_CP1_CI_AS as PText9,
PText10 collate SQL_Latin1_General_CP1_CI_AS as PText10,
Computername collate SQL_Latin1_General_CP1_CI_AS as Computername,
Application collate SQL_Latin1_General_CP1_CI_AS as Application,
Comment collate SQL_Latin1_General_CP1_CI_AS as Comment,
Username collate SQL_Latin1_General_CP1_CI_AS as Username,
Counter as Counter,
TimeDiff as TimeDiff,
Classname collate SQL_Latin1_General_CP1_CI_AS as Classname,
Typename collate SQL_Latin1_General_CP1_CI_AS as Typename,
Class as Class,
Type as Type,
Text1 collate SQL_Latin1_General_CP1_CI_AS as Text1,
Text2 collate SQL_Latin1_General_CP1_CI_AS as Text2,
Text3 collate SQL_Latin1_General_CP1_CI_AS as Text3,
Text4 collate SQL_Latin1_General_CP1_CI_AS as Text4,
Text5 collate SQL_Latin1_General_CP1_CI_AS as Text5,
Text6 collate SQL_Latin1_General_CP1_CI_AS as Text6,
Text7 collate SQL_Latin1_General_CP1_CI_AS as Text7,
Text8 collate SQL_Latin1_General_CP1_CI_AS as Text8,
Text9 collate SQL_Latin1_General_CP1_CI_AS as Text9,
Text10 collate SQL_Latin1_General_CP1_CI_AS as Text10,
AG_NR as AG_NR,
CPU_NR as CPU_NR,
CrComeBack as CrComeBack,
CrGoBack as CrGoBack,
CrAckBack as CrAckBack,
LocalID as LocalID,
Priority as Priority,
AP_type as AP_type,
AP_name collate SQL_Latin1_General_CP1_CI_AS as AP_name,
AP_par collate SQL_Latin1_General_CP1_CI_AS as AP_par,
InfoText collate SQL_Latin1_General_CP1_CI_AS as InfoText,
TxtCame collate SQL_Latin1_General_CP1_CI_AS as TxtCame,
TxtWent collate SQL_Latin1_General_CP1_CI_AS as TxtWent,
TxtCameNWent collate SQL_Latin1_General_CP1_CI_AS as TxtCameNWent,
TxtAck collate SQL_Latin1_General_CP1_CI_AS as TxtAck,
AlarmTag as AlarmTag,
AckType as AckType,
Params as Params,
Servername collate SQL_Latin1_General_CP1_CI_AS as Servername
from OPENQUERY(LnkRtDb_WinCCOLEDB,''ALARMVIEW:Select * FROM AlgViewEnu WHERE DateTime > ''''2022-01-01 00:00:00.000'''' and DateTime<=''''2022-05-01 00:00:00.000'''''')') WHERE DateTime>'2022-01-01 00:00:00.000' and DateTime<='2022-05-01 00:00:00.000' ;

i tried to check "Be made using the login’s current security context" and "Be made using this security context" but same issue .

Any idea about this issue?

Thanks for the support

James

Answer 1

Hi @AKIHOLA James ,

Welcome to Microsoft Q&A!

i tried to check "Be made using the login’s current security context" and "Be made using this security context" but same issue .

Agree with Erland. Here is a related thread: https://www.sqlteam.com/forums/topic.asp?TOPIC_ID=112640
Hope this could give you some ideas.

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Notice that the authentication error is not with SQL Server it is with that other nested data source LnkRtDb_WinCCOLEDB which apparently is not an SQL Server instance, but which you access through OLE DB provider WinCCOLEDBProvider, that I have never heard of before.

To answer this question, one needs to have knowledge with this provider, and you would need to talk with the vendor/developer of this provider. If I am to make a guess, it is a double-hop problem.