xp_cmdshell giving error error browsing Sharepoint Online directory files

Question

xp_cmdshell giving error error browsing Sharepoint Online directory files

skb 44459

SSCarpal Tunnel

Points: 4768
More actions
February 12, 2024 at 3:49 pm

#4377394

DECLARE @var VARCHAR(2000)
SET @var = 'dir ' + '\\company.sharepoint.com@SSL\DavWWWRoot\sites\OrderSite\OrderDocs\ORD001\*Invoice*33023*.pdf' + ' /b'
EXEC master..xp_cmdshell @var
Following error occurs : Access Denied. Before opening files in this location, you must first add the web site to your trusted sites list, browse to the web site, and select the option to login automatically.
I am able to browse the directory from the dos command without any problem.
When i used EXEC master..xp_cmdshell 'whoami'. I get the result domain\Username, it is the same user i can use to browse the directory. I dont know why it is not working from xp_cmdshell SQL command

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

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

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

xp_cmdshell runs server side so you would need to be able to browse the directory from the server side not your local dos command line.

My opinion though - SQL Server and xp_cmdshell is an odd tool to use for downloading files from sharepoint... Powershell is a much better way to do it in my opinion...

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Lowell SSC Guru Points: 323518 More actions · Answer 2

it's permissions.

you have permissions, but the account running the SQL service does not.

regardless of who is running the SQL command executing xp_cmdshell, once outside of SQL server, xp_cmdshell runs as the service account that runs SQL Server...so if that is NT AUTHORITY\NETWORKSERVICE for example, it ends up passing the computername as the AD account to try and access the unc path. if you are actually using an AD account to run the service, perfect! just add that account to the share under security.

you can fix that local account issue by adding a proxy account as your own account TEMPORARILY as the proof of concept.

Right Click on the Server in Object Explorer and go to properties...Security
checkbox for "Enable server proxy account"
enter the domain\name and password.
save and retest.

find or create a new domain account that has access only to that share and add that as the proxy account.

This reply was modified 9 months, 1 week ago by Lowell.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

Mr. Brian Gale wrote:

My opinion though - SQL Server and xp_cmdshell is an odd tool to use for downloading files from sharepoint... Powershell is a much better way to do it in my opinion...

"It Depends". Doing the xp_CmdShell thing (to execute a DOS or PoSh command) is very useful when it comes to building "womb-to-tomb" jobs instead of have to jump back and forth.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)