none
SQL 2017 issue: OPENROWSET with Microsoft.ACE.OLEDB.16.0 RRS feed

  • Question

  • Hello people,

    Weird issue here...

    3 new Microsoft SQL Server 2017 (RTM-CU10) (KB4342123) - 14.0.3037.1 (X64)   on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ).

    I want to access data across a shared folder on the network, and here is the issue happening with the 3 new servers:

    In SSRS I open a Query window and type these:

    1st Code
    
    SELECT *
    FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.16.0',
    'Excel 12.0;
    Database=\\ServerName\DATA\Excelfile.xls',
    'SELECT * FROM [data]')
    
    
    2nd Code:
    
    SELECT *
    FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.16.0',
    'Excel 12.0;
    Database=\\FQDN\DATA\Excelfile.xls',
    'SELECT * FROM [data]')
    
    3rd Code:
    
    SELECT *
    FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;
    Database=\\ServerName\DATA\Excelfile.xls',
    'SELECT * FROM [data]')
    
    3th Code:
    
    SELECT *
    FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;
    Database=\\FQDN\DATA\Excelfile.xls',
    'SELECT * FROM [data]')


    Now the problem:  Every code that doesn't have a FQDN will not work and give me this error:

    Msg 7399, Level 16, State 1, Line 1
    The OLE DB provider "Microsoft.ACE.OLEDB.16.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
    Msg 7303, Level 16, State 1, Line 1
    Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.16.0" for linked server "(null)".

    If there us a FQDN in the code, it works fine.

    I did try with my SQL 2008 with the same path, only with OLEDB 12.0, but it works fine with both type of request (FQDN and ServerName).

    Now in itself you'd say that is not a real big issue, just add the FQDN instead, but there is so many application to change that it makes no sense, there must be something to fix this.

    The File server is clustered, and I am just saying because if I tried both way on my computer it worked, but as soon as I get on a clustered shared folder it seems to fail...

    Any Ideas?


    • Edited by Dan123 jj Thursday, September 27, 2018 4:53 PM Typos and form
    Thursday, September 27, 2018 4:05 PM

Answers

  • Well all of you that said it was Kerberos... You were right, but probably not the way you thought.

    My SQL service account entries were messed up, like in the strangest way.
    All entries were there and accounted for.

    BUT

    Some were entered with a different character code somehow...  And that in turned made some of them not working right (never copy a command from an email and past it directly into power shell).

    What I ended up doing is delete every entries and recreated them, not it work like a charm...

    Regards,

    • Marked as answer by Dan123 jj Wednesday, November 7, 2018 4:26 PM
    Wednesday, November 7, 2018 4:26 PM

All replies

  • What about using IP Address instead? Does it work then?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, September 27, 2018 4:54 PM
  • With IP it doesn't work from any SQL I tried on (2017 and 2018 alike)

    Thursday, September 27, 2018 5:09 PM
  • With IP it doesn't work from any SQL I tried on (2017 and 2018 alike)

    WHat IP you used? Internal Ip or External IP? it should be internal IP within the network

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, September 27, 2018 5:20 PM
  • Yes it is Internal IP, no external acces for these servers.
    All the servers are in the same domain.
    Thursday, September 27, 2018 5:44 PM
  • I find it difficult to believe that the version of SQL Server matters. This has all the flavours of a local configuration issue. I would not be surprised if you check the SQL 2008 machine you say that it works with only server name that you find an alias in the hosts file. They could also be defined in the Alias section in SSCM.

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

    Thursday, September 27, 2018 8:58 PM
  • Hi Dan123 jj,

    Per your description, you said that 'I did try with my SQL 2008 with the same path, only with OLEDB 12.0, but it works fine with both type of request (FQDN and ServerName)', and I suspect that OLEDB 16.0 may be not compatible with Excel 12.0.

     

    Hope it can help you.

     

    Best Regards,

    Rachel


    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, September 28, 2018 8:33 AM
  • Sounds like a SPN problem.

    Friday, September 28, 2018 8:48 AM
  • To answer some comments...

    • There are no entry in the server's host file
    • The OLEDB16 does work with Excel as I said, in earlier post, my issue has to do with the way to call the file, which shouldn't matter, but apparently it does.
    • We tought too it was a SPN issue and tripple checked all of them (SQL, File server, Nodes, service accounts)

    With IP adress it wont work on the cluster, I did not try else where.

    We also looked at the DNS, everything seems to be normal on that side too...  

    I did another test, calling the Host with priviledge access.


    \\HostServer\D$\restofthepath  (working)

    \\HostServer.domain.local\D$\restofthepath  (working)

    So it seems that the share drive is the cause, now how to debug this when there are no error in the cluster log or even the event viewer.  Are there any settings to look at aside from SPN and DNS, like a cluster configuration?  It does only answer to FQDN....

    Thanks


    • Edited by Dan123 jj Friday, September 28, 2018 5:16 PM
    Friday, September 28, 2018 5:15 PM
  • I am sorry that I have no way.
    Tuesday, October 2, 2018 8:07 AM
  • Something strange I discovered while sniffing the line with WireShark

    To start with before running the query:
    select auth_scheme from sys.dm_exec_connections where session_id=@@spid

    Result: KERBEROS

    As it should be...

    Then when I run the above query while looking with WireShark I get to see this:

    Session Setup Request, NTLMSSP_NEGOTIATE, and is eventually denied... Session Setup Response, Error: STATUS_ACCESS_DENIED

    So even if I am in Kereros, my query is swittching in NTLM...  Does this give you any ideas?

    Thanks!


    • Edited by Dan123 jj Friday, October 12, 2018 2:22 PM
    Friday, October 12, 2018 2:22 PM
  • It definitely smells like a problem with SPN/Kerberos. Unfortunately this is not something I know too much about. But it is as if the SPN is only defined for the FQDN and not for the server name alone.

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

    Friday, October 12, 2018 9:03 PM
  • Well all of you that said it was Kerberos... You were right, but probably not the way you thought.

    My SQL service account entries were messed up, like in the strangest way.
    All entries were there and accounted for.

    BUT

    Some were entered with a different character code somehow...  And that in turned made some of them not working right (never copy a command from an email and past it directly into power shell).

    What I ended up doing is delete every entries and recreated them, not it work like a charm...

    Regards,

    • Marked as answer by Dan123 jj Wednesday, November 7, 2018 4:26 PM
    Wednesday, November 7, 2018 4:26 PM