-
Olaf Helper 27,296 Reputation points2020-11-27T13:25:59.287+00:00 '\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.-
Sudip Bhatt 2,246 Reputation points2020-11-28T10:40:01.347+00:00 Sir please share some idea how to give permission to SQL Server service account.
i am the admin of my pc. now sql server db is in different pc. hence i am working from home i am accessing everything by VPN.
so how can i resolve this issue. thanks
Sign in to comment1 additional answer
Sort by: Most helpful
-
Sign in to answer
Erland Sommarskog 72,581 Reputation points MVP2020-11-27T14:23:31.6+00:00 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.
-
Not being able to read xml file by OPENROWSET
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
First of all, it is not the permissions of the SQL Service account you should set, see my answer below, it is your own permission.
I think the easiest is to work with your Windows admin, not the least if you need to fight Kerberos.
You can set up permission in Windows by right-clicking a folder and select Properties and then go the Security tab. Although that only sets the local permission. You also need to set up a share, which I think is a separate option in the context menu.
Questions about this in more detail does not belong in an SQL Server forum, but you would have to ask in a Windows forum. But if you have no experience in Windows security, I think it better to work with your Windows admin as I said.