sql - 如何解决在 Microsoft SQL 中使用 xp_cmdshell 时出现的 "Access is denied"错误？

Question

这是我的整个例程:

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

我已经验证代码有效，用于将单个图像加载到服务器，这里没有这个位:

-- 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

但是一旦添加了 while 循环和 xp_cmdshell 语句，文件名将返回为“拒绝访问”。

如有任何帮助，我们将不胜感激!

我不是 SQL 方面的专家，但有人要求我将大约 1000 个 PDF 和 JPEG 文件加载到数据库中，脚本似乎是最合乎逻辑的方法。

说完一切后，我希望脚本从文件夹中抓取每张图像并将其加载到数据库中。

如有必要，我愿意使用不同的循环方法。

编辑:我还尝试将以下内容添加到未解决问题的代码开头:

--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.

我还进入了 Facets > Surface Area Configuration 并确保启用/允许 xp_cmdshell (true)。它也已在 Facets > Server Security 下标记为 true。

Answer 1

这里有几个可能的问题。

xp_cmdShell 在服务器上运行。如果该机器没有名为 C:\Users\*****\Desktop\Test_Images 的文件夹，它将无法工作。

xp_CmdShell 使用 service account 运行.如果该帐户没有目标文件夹的权限，它将失败。

必须启用 xp_CmdShell。来自 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.