SQL 2012 bcp调用返回SQLState = 28000。NativeError = 18456 用户登录失败。

Question

我正在处理一个SQL存储过程，该存储过程调用了xp_cmdshell。 xp_cmdshell已启用，并且代理帐户设置为“vpexporter”。此存储过程旨在将数据文件写入磁盘。

在SQL 2005服务器上，这个存储过程可以正常运行。但现在环境已经升级到SQL 2012，该存储过程无法运行。造成问题的那一行代码是：

set @sql1 = 'bcp "SELECT * FROM dbo.udPayrollOutput" queryout "D:\Repository\Exports\' + @fileunique  -Uvpexporter -Ppassword -c -t,' 
exec master..xp_cmdshell @sql1

在SSMS中运行此命令会导致以下结果：

SQL状态 = 28000。本机错误 = 18456 错误 = [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user ‘vpexporter’。

我已经尝试了使用SQL登录和域账户进行操作，但是两者都返回相同的错误。'vpexporter'已被添加为登录用户，并且作为主数据库的用户设置，具有对xp_cmdshell执行权限。

我感觉在SQL 2012版本中调用xp_cmdshell方式发生了变化，但是我在谷歌上没有找到任何信息。

我尝试运行“exec xp_cmdshell 'whoami.exe'”，它返回“nt authority\network service”，这是SQL Server正在其中运行的账户。

我的理解是，通过使用'sp_xp_cmdshell_proxy_account'指定一个命令行代理帐户来替换该代理帐户。我不想授予Network Service对xp_cmdshell的执行权限。

---

我已经取得了一些进展但仍然卡住了。由于新环境需要通过在查询行中添加“-S ServerName\InstanceName”来指定SQL实例，因此最初的错误是由此引起的。现在我收到错误消息：

无法启动 'cvADPTaxCreditExp' 存储过程。 拒绝在对象 'xp_cmdshell' 上执行权限，数据库 'mssqlsystemresource'，架构 'sys'。

我已经授予了xp_cmdshell在主数据库中的代理帐户执行权限，但仍然收到此错误。

exec sp_xp_cmdshell_proxy_account 'NEWMECHDOM\vpexporter', 'password';
GRANT EXECUTE ON xp_cmdshell TO [NEWMECHDOM\vpexporter];

我已经通过以下方式进行验证：

select  * from  sys.credentials

安全性是否需要在其他地方进行设置？

- user2015032

---

执行 xp_cmdshell 'whoami.exe'；它是否返回你所期望的内容？ - Mackan

@Mackan，我尝试运行“exec xp_cmdshell 'whoami.exe'”，返回了“nt authority\network service”，这是SQL Server正在运行的帐户。我的理解是，通过使用“sp_xp_cmdshell_proxy_account”指定命令 shell 代理帐户，它将使用该帐户。我不想授予 Network Service 执行 xp_cmdshell 的访问权限。 - user2015032

据我所知，如果您正确地设置了代理帐户（并且它具有足够的权限），则应该使用代理帐户。 EXEC sp_xp_cmdshell_proxy_account 'DOMAIN\user','password'。如果通过sysadmin帐户运行，xp_cmdshell将使用服务帐户来生成子进程。 - Mackan

Answer 1

我曾经在运行于SQL Server 2008 R2机器上的批处理文件中使用bcp.exe时遇到了同样的问题：使用-U -P指定用户/密码会返回“NativeError = 18456”（登录失败），我通过使用参数-T来解决了这个问题，即使用“信任连接”。

BCP.EXE调用出现错误18456：

bcp.exe "SELECT * from DWH.BS.flussi.vw_KLINX_Anagrafiche800" queryout %fname% -t";" -c -SLOCALHOST -dMEF -Uxxx -Pyyy

BCP.EXE的工作调用是：

bcp.exe "SELECT * from DWH.BS.flussi.vw_KLINX_Anagrafiche800" queryout %fname% -t";" -c -SLOCALHOST -dMEF -Uxxx -Pyyy -T

- Massimo Nitri

---

你是否需要明文输入密码并添加-T（信任）选项？这似乎是一个可行的解决方案，但仍然存在安全问题（以明文形式显示密码）。 - alejandrob

Answer 2

我发现2008和2012不同。在2008年，我不需要指定服务器名称。在2008年，我还能够使用D$驱动器号，但现在我需要使用共享驱动器名称。以下内容适用于2012年。

-- Declare report variables
DECLARE @REPORT_DIR VARCHAR(4000)
DECLARE @REPORT_FILE VARCHAR(100)
DECLARE @DATETIME_STAMP VARCHAR(14)
DECLARE @Statement VARCHAR(4000)
DECLARE @Command VARCHAR(4000)

--SET variables for the Report File
SET @DATETIME_STAMP = (SELECT CONVERT(VARCHAR(10), GETDATE(), 112) + 
REPLACE(CONVERT(VARCHAR(8), GETDATE(),108),':','')) -- Date Time Stamp with YYYYMMDDHHMMSS
SET @REPORT_DIR = '\\aServerName\SharedDirectory\' -- Setting where to send the report. The Server name and a Shared name, not a drive letter
SET @REPORT_FILE = @REPORT_DIR + 'Tables_' + @DATETIME_STAMP + '.csv' --the -t below is used for the csv file

--创建包含所有数据的CSV文件报告。必须使用@Statement变量在xp_cmdshell命令中使用变量。

SET @Statement = '"SELECT * FROM  sys.tables" queryout "'+@REPORT_FILE+'" -c -t"," -r"\n" -S"CurrentServerName\Databasename" -T' --The -S must be used with the -T
SET @Command = 'bcp '+@Statement+' '
EXEC master..xp_cmdshell @Command 

- Swerdna