Not being able to read xml file by OPENROWSET

Question

This is my code. i have a xml file is in c drive but no way i am being able to read xml file from sql server using OPENROWSET permission.
DECLARE @XML XML
SELECT @XML = BulkColumn FROM OPENROWSET(BULK '\192.168.1.111\c$\test.xml', SINGLE_BLOB) x

 --SELECT  
 --    t.c.value('(PersonId/PersonNr/text())[1]', 'VARCHAR(100)'),  
 --    t.c.value('(Namn/Tilltalsnamnsmarkering/text())[1]', 'INT')  
 --FROM @xml.nodes('*:ArrayOfFolkbokforingspostTYPE/*:FolkbokforingspostTYPE/*:Personpost') t(c)  
  
  
SELECT col.value('(Section/text())[1]', 'NVARCHAR(MAX)') AS Section  
 ,col.value('(LineItem/text())[1]', 'NVARCHAR(MAX)') AS LineItem     
 ,col.value('(XFundCode/text())[1]', 'NVARCHAR(MAX)') AS XFundCode  
 ,col.value('(StandardDate/text())[1]', 'NVARCHAR(MAX)') AS StandardDate  
 ,col.value('(StandardValue/text())[1]', 'VARCHAR(MAX)') AS StandardValue  
 ,col.value('(ActualProvidedByCompany/text())[1]', 'VARCHAR(MAX)') AS ActualProvidedByCompany  
FROM @xml.nodes('/Root/PeriodicalData') AS tab (col)   

This line actually causing the issue
SELECT @XML = BulkColumn FROM OPENROWSET(BULK '\192.168.1.111\c$\test.xml', SINGLE_BLOB) x

Error message return from SSMS Bad or inaccessible location specified in external data source "(null)".

when i give simple path like SELECT @XML = BulkColumn FROM OPENROWSET(BULK 'c\test.xml', SINGLE_BLOB) x
then got different error message and it was Cannot bulk load. The file "c:\test.xml" does not exist or you don't have file access rights.

please tell me what right i need to give as a result i can read xml file from sql server. thanks

Answer 1

'\192.168.1.102\c$\t

C$ is a dedicated admin share and only admins can access this share; the SQL Server service account isn't an admin as I guess.
Create a real share and grant access permissions for the SQL Server service account.

Answer 2

Some qualifications to Olaf's post. As long as you connect to SQL Server with Windows authentication, SQL Server will impersonate you when accessing the file share. That is, what matters are your own permission. Note that for it to work, Kerberos must be setup correctly, or else there will be errors.

I would guess though that Olaf is correct on the administrative shares Work with real shares. And putting files directly under C:\ (or D:\ etc) is not a good idea.

And to be clear: what matters here are the permissions in the file system, not SQL Server.