Post reply

.XML" could not be opened. Operating system error code 5(Access is denied.).

  • February 19, 2010 at 6:57 am

    #98992

    Hello,

    Environment: SQL2005 64Bit Active/Passive Cluster

    I've written a proc to Import XML files and parse validate and post the data to our ERP system, unfortunately I receive the error.

    ".XML" could not be opened. Operating system error code 5(Access is denied.)."

    I tried utilizing Execute As which returns the error "You do not have permission to use the bulk load statement".

    For the execute as the user(s) being tested have sysadmin and Bulk admin rights and are DB Owners.

    I have even created an SSIS package to execute from SQL Agent utilizing a proxy account that has rights to the UNC path/file share being accessed.

    What I find most unusual is if I connect to the SQL Server as sa (and only sa) the proc will execute, however if I connect utilizing windows Authentication or any other user I receive Operating system error code 5(Access is denied.)."

    I have tested with and without proxy accounts (works under sa only).

    All Windows accounts being used are Domain Admins with full rights to the UNC path being used.

    Error occurs on:

    SET @vs_SQL = 'INSERT INTO #Counters (xCol)

    Select xCol =rEPLACE(Replace(Replace(Replace(Replace(Replace(Replace(Replace(xCol,''&'',''&''),''<'',''<''),''&'',''''),'' '',''''),char(10),''''),char(13),''''),''! '',''''),''!'','' '')

    FROM (SELECT * FROM OPENROWSET

    (BULK '''+@vs_PostFile+''',

    SINGLE_BLOB) AS xCol) AS R(xCol)'

  • February 21, 2010 at 12:38 am

    #1122448

    Set your database to TRUSTWORTHY.

    (also, please be aware that this has security implications).

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • February 22, 2010 at 6:48 am

    #1122909

    I have worked around the issue with an SSIS package, I am aware of the Trustworthy option and the security implications which is why I opted not to use it.

    I have created an SSIS Package with for loop to move the files from the UNC resource to a local resource; the xml files are then processed and deleted for those processed successfully.

    By using SSIS I am able to build in a little more advanced error handling as well.

    Not ideal, but I do prefer it to the Trustworthy option.

    Thank you for your feedback

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

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