How do I resolve this "Access is denied" error which occurs when using xp_cmdshell in Microsoft SQL?

Question

This is my entire routine:

Declare @AttFileType as char(5), @HQCo as int, @FormName as Varchar(15),       @KeyID as VarChar(10), @UniqueID as uniqueidentifier, @FilePath as Varchar(100), @StringCommand as Varchar(200)
Declare @AttID as int
DECLARE @cmd as VARCHAR(500)
DECLARE @cmd2 as VARCHAR(500)


CREATE TABLE #tmp(eFileName VARCHAR(100));
INSERT INTO #tmp
EXEC xp_cmdshell 'dir /B C:\Users\*****\Desktop\Test_Images';

Declare @FileName varchar(100)

Set @UniqueID = NewID()

While (Select Count(*) From #tmp where eFileName is not null) > 0
Begin

Select Top 1 @FileName = eFileName From #tmp

Set @FilePath = 'C:\Users\*****\Desktop\Test_Images\' + @FileName

Set @AttID = (Select TOP 1 AttachmentID FROM dbo.bHQAF ORDER BY AttachmentID DESC) + 1
Set @AttFileType = '.jpg'


Insert Into dbo.bHQAF (AttachmentID, AttachmentFileType)
Select @AttID, @AttFileType


SET @cmd = '
Declare @AttID2 as int, @AttFileType2 as char(5), @FilePath2 as Varchar(100)

Set @AttFileType2 = ''.jpg''
Set @AttID2 = (Select TOP 1 AttachmentID FROM dbo.bHQAF ORDER BY AttachmentID DESC)


Update dbo.bHQAF 
Set AttachmentData = (SELECT * From OPENROWSET (Bulk ''' + @FilePath + ''', Single_Blob) rs) 
Where AttachmentID = @AttID2 and AttachmentFileType = @AttFileType2'

Exec (@cmd)

Set @HQCo = 101
Set @FormName = 'HRCompAssets'
Set @KeyID = 'KeyID=2'


Insert Into dbo.bHQAT (HQCo, AttachmentID, FormName, KeyField, UniqueAttchID)
Select @HQCo, @AttID, @FormName, @KeyID, @UniqueID

Insert Into dbo.bHQAI (AttachmentID, HRCo)
Select @AttID, @HQCo

Update dbo.bHQAT 
Set Description = 'TEST3', AddDate =  GETDATE(),  AddedBy = '****', DocAttchYN = 'N',  DocName = 'Database', OrigFileName = @FileName, TableName = 'HRCA'
Where AttachmentID = @AttID and HQCo = @HQCo

Insert Into dbo.bHQAI (AttachmentID, HRCo)
Select @AttID, 101

Update dbo.bHRCA
Set UniqueAttchID = @UniqueID
Where HRCo = 101 and Asset = '00001'

Delete from #tmp Where eFileName = @FileName

End

I have verified that the code works, for loading a single image into the server, without this bit here:

-- Declarations here 

CREATE TABLE #tmp(eFileName VARCHAR(100));
INSERT INTO #tmp
EXEC xp_cmdshell 'dir /B C:\Users\*****\Desktop\Test_Images';

While (Select Count(*) From #tmp where eFileName is not null) > 0
Begin

Select Top 1 @FileName = eFileName From #tmp


-- Rest of code here


Delete from #tmp Where eFileName = @FileName

End

But once the while loop and xp_cmdshell statements are added, the file name is returned as "Access is denied".

Any help would be appreciated!

I'm not an expert in SQL, but I've been asked to load about 1000 PDF and JPEG files into the database and a script seemed to be the most logical approach.

When all is said and done, I would like the script to grab each image from the folder and load it into the database.

I'm open to using a different looping method if necessary.

Edit: I have also tried adding the following to the beginning of the code which didn't resolve the issue:

--Allow for SQL to use cmd shell
EXEC sp_configure 'show advanced options', 1    -- To allow advanced options to be changed.
RECONFIGURE -- To update the currently configured value for advanced options.
EXEC sp_configure 'xp_cmdshell', 1  -- To enable the feature.
RECONFIGURE -- To update the currently configured value for this feature.

I also went into Facets > Surface Area Configuration and made sure xp_cmdshell is enabled / allowed (true). It was also already marked true under Facets > Server Security.

Answer 1

There are a couple of possible issues here.

xp_cmdShell runs on the server. If that machine does not have a folder called C:\Users\*****\Desktop\Test_Images it will not work.

xp_CmdShell runs using the service account. If that account does not have permissions on the target folder it will fail.

xp_CmdShell has to be enabled. From MSDN.

The xp_cmdshell option is a SQL Server server configuration option that enables system administrators to control whether the xp_cmdshell extended stored procedure can be executed on a system. By default, the xp_cmdshell option is disabled on new installations and can be enabled by using the Policy-Based Management or by running the sp_configure system stored procedure.

Answer 2

I figured it out!

Thank you @destination-data & @GarethLyons for all the help!

You guys were right, there was an issue with the folder permissions I didn't realize that I would have to go into the folder and manually update the permissions to include "Service". Once I did, everything worked perfectly!

Thank you both again, sorry for the confusion.

For anyone else who is having this issue in the future, do the following first:

1) Go to the folder

2) Right click

3) Select properties

4) Select the Security tab

5) Click Advanced

6) Click Add

7) Click select a principle

8) Enter "Service" and Check Names

9) Select Service and click OK

10) Select the appropriate permissions under "Basic Permissions"

11) Select "Only apply these permissions to the object in this container"

12) Apply the changes and try running xp_cmdshell again