14,501 questions
That normal account, is that a Windows login? Does that login have access to the file?
OPenrowset with Microsoft.ACE.OLEDB.16.0 only works with SA
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Anonymous
Hello -
I am using SQL SERVER 2019 with Excel office 365 64 bit and have configured the Microsoft.Ace.OLEDB.16.0 linked server correctly. I am able to use openrowset and import an excel file without any issues as long as I am logged in as the SA account.
NT Service/MSSQLSERVER is permissioned for the folder as well.
As soon as I login with a normal account with all rights granted - i get the following error Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.16.0" for linked server "(null)" and sometime i get an error saying bpipe is closing and the MSSQLSERVER service gets shut down.
BUt if i start an instance and login as SA then disconnect without shutting down and re-connect with a normal account it works.
Any ideas?
SQL Server | Other
5 answers
Sort by: Most helpful
-
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator2022-08-23T21:51:00.593+00:00 -
Anonymous
2022-08-23T21:55:46.747+00:00 Hello,
Thanks for the reply.
The second account is windows auth using firm domain\username. That user id was set up by me as i installed sql server. It is setup as sysadmin and public. For the new databases it is dbo. SA is dbo for master, tempdb, model, and msdb. That windows login has full control over folder, the NT Service has full control, sp_configure is set up correctly.
Very weird that it works flawlessly with SA but not windows.
What am I missing?
-
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
2022-08-23T22:03:30.017+00:00 When you login with sa (or some other SQL login) access is through the service account (NT Service\MSSQLSERVER.) With a Windows login, I would guess that impersonates that login, so that login needs permission.
Sign in to comment -
-
Anonymous
2022-08-23T22:08:23.637+00:00 What do you think that login needs permission to? So this is strange - I start ssms and login as sa - run the openrowset and it works. I leave ssms open and just click on disconnect. I then connect and log in with windows auth. It now works. If i shut down ssms and launch and log in as windows auth - it doesn't work.
I am pretty certain my windows auth has more rights now that sa other than the sql tables. This is so bizarre.
-
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
2022-08-23T22:10:16.847+00:00 That part probably has to do with that SQL Server already has the file open, so that you can piggyback on the open file.
Sign in to comment -
-
Anonymous
2022-08-23T22:12:50.52+00:00 That is what i thought. But i moved directories and copied and renamed file just in case and it still worked. I also have complete access to root of server as I installed everything.
-
Anonymous
2022-08-24T10:47:29.333+00:00 I think ive pretty much exhausted all SQL steps to fix this problem. At this point I think it maybe related to windows local policy.
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(163.2, 84%, 25%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EYM%3C/text%3E%3C/svg%3E)
YufeiShao-msft 7,146 Reputation points2022-08-25T08:39:35.993+00:00 Hi @Anonymous ,
There are a lot of people who use windows auth to login in ssms have encountered the same problem, maybe you can try their solution
https://techcommunity.microsoft.com/t5/sql-server/microsoft-ace-oledb-16-0-openrowset-not-working-from-remote/m-p/2579666
https://stackoverflow.com/questions/1362108/cannot-initialize-the-data-source-object-of-ole-db-provider-msdasql-for-linked-------------
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.