0

Is there any way to grant my SQL Authenticated SQL Server Account a Full Control access to Disk C? I have an Excel File uploader that opens .xlsx files to read the data and INSERT it into the database. My server is located on Disk C and that's also where I copy the files being uploaded by the user before I open it systematically.

As of now, I am having the

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

error using my SQL Authenticated account because the file is in Disk C but when I use my Windows Authenticated account it works fine. What I want to achieve is that my SQL Authenticated account should have the same level of access in the server as my SQL Authenticated Account so that my uploader can upload just fine even if I'm using an SQL Authenticated Account.

Improve this question
4
  • 3
    Your SQL Server "service" account does of course have very restricted access, as should all service accounts. Why don't you just put the files into a folder where the service does have access? Commented Apr 3, 2023 at 3:36
  • But you can of course grant the service account access to whatever you want. Commented Apr 3, 2023 at 3:41
  • Given that access permissions tend to propagate into subfolders you almost certainly don't want to grant SQL Server Full Control access of C:\ because you're effectively giving it carte blanche access to the whole C: drive. Don't you have a commonly accessible temp folder somewhere? Commented Apr 3, 2023 at 8:46
  • Better idea: use something like Powershell or a VBA script to open the file and bulk insert it. Using OPENROWSET or linked servers on the ACE provider is not properly supported anyway. Commented Apr 3, 2023 at 12:17
Related questions
4347
UPDATE from a SELECT
3327
How to add a column with a default value to an existing table in SQL Server?
2114
LEFT JOIN vs. LEFT OUTER JOIN in SQL Server
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.