Post reply

Import XML file in an Table

  • January 23, 2015 at 1:52 am

    #296927

    I want to import an XML file in an Table.

    The XML file is on an Other Network location than the Database server I’m connected to and I have access to this location with my windows Credential.

    I connect from my local PC to the Database server “RS1” with my Windows Credentials.

    There I run this script

    CREATE TABLE XMLwithOpenXML

    (

    Id INT IDENTITY PRIMARY KEY,

    XMLData XML,

    LoadedDateTime DATETIME

    )

    INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)

    SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE()

    FROM OPENROWSET(BULK '\\<Networkname>\<MAP>\Name_.xml', SINGLE_BLOB) AS x;

    I Get the error:

    Msg 4861, Level 16, State 1, Line 10

    Cannot bulk load because the file "\\<Networkname>\<MAP>\Name_.xml'" could not be opened. Operating system error code 5(Access is denied.).

    (\\<Networkname>\<MAP>\Name_.xml' is not the real name )

    So it looks like the openrowset connects to the network location with other credentials.

    Can you explain me where I can find (and change) the credentials which is used to connect to the networklocation?

    I found this article on MSDN (Security Considerations) but It’s not helping me to find this solution

    http://msdn.microsoft.com/en-us/library/ms175915.aspx

    I hope you can help me with this?

    Thanks in advance!

  • January 23, 2015 at 2:19 pm

    #1772834

    I believe the answer is in the article you posted. under Security Considerations it reads (emphasis mine),

    ...A login using SQL Server authentication cannot be authenticated outside of the Database Engine. Therefore, when a BULK INSERT command is initiated by a login using SQL Server authentication, the connection to the data is made using the security context of the SQL Server process account (the account used by the SQL Server Database Engine service). To successfully read the source data you must grant the account used by the SQL Server Database Engine, access to the source data.

    That means that you need to go into services for the machine that is running the query and locate the Service for the SQL Server instance that is attempting to connect to that file and grant that account access to the XML file...

    Note that, in my screenshot example I am showing the services on my machine (I have a local SQL install using the default installation options). Hopefully the machine that is attempting to access the file has an Active Directory account for the SQL Service and not a local account (which is the case on my local PC).

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply