SQL 2012 bcp调用返回SQLState =28000.NativeError = 18456用户登录失败 [英] SQL 2012 bcp call returns SQLState = 28000. NativeError = 18456 Login failed for user

问题描述

我正在使用一个SQL存储过程来调用xp_cmdshell. xp_cmdshell已启用，并且代理帐户设置为"vpexporter".此sproc旨在将数据文件写到磁盘上.

I’m working with a SQL stored procedure that makes a call to xp_cmdshell. xp_cmdshell has been enabled, and has a proxy account set to ‘vpexporter’. This sproc was designed to write out a data file to disk.

此sproc在SQL 2005服务器上时一直在工作.该环境已升级到SQL 2012，并且sproc不再运行.拨打电话的行是:

This sproc had been working when it was on a SQL 2005 server. The environment has been upgraded to SQL 2012 and the sproc no longer runs. The line making the call is:

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

在SSMS中运行此命令可为我提供以下信息:

Running this in SSMS gives me the following:

SQLState =28000.NativeError = 18456错误= [Microsoft] [SQL Server Native Client 11.0] [SQL Server]用户"vpexporter"登录失败.

SQLState = 28000. NativeError = 18456 Error = [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user ‘vpexporter’.

我已经使用SQL登录名和域帐户尝试了此操作.两者都返回相同的错误.已添加"vpexporter"作为登录名，并已设置为具有xp_cmdshell执行权限的主数据库用户.

I have tried this with both a SQL login and with a domain account. Both return the same error. The ‘vpexporter’ has been added as a login, and has been set up as a user of the master db with execute permissions on xp_cmdshell.

我觉得在SQL版本2012中调用xp_cmdshell的方式一定有所改变，但是在进行谷歌搜索时却没有发现任何东西.

I feel something must have changed in how xp_cmdshell is called with SQL version 2012, but I haven’t found anything when googling that.

我尝试运行"exec xp_cmdshell'whoami.exe'"，该程序返回了"nt Authority \ network service"，这是SQL Server正在其下运行的帐户.

I tried running "exec xp_cmdshell 'whoami.exe'" which returned 'nt authority\network service' which is the account SQL Server is running under.

我的理解是，通过使用'sp_xp_cmdshell_proxy_account'指定命令shell代理帐户，它将使用它.我不想授予xp_cmdshell执行对网络服务的访问权限.

My understanding was that by specifying a command shell proxy account with 'sp_xp_cmdshell_proxy_account' it would use that instead. I do not want to grant xp_cmdshell execute access to the Network Service.

我已经取得了一些进步，但仍然陷入困境.原始错误是由于新环境要求通过在我的查询行中添加"-S ServerName \ InstanceName"来指定SQL实例而引起的.现在我得到了错误:

I've made some progress but am still getting stuck. The original error was due to the new environment requiring specifying the SQL instance by adding "-S ServerName\InstanceName" to my query line. I now get the error:

无法启动"cvADPTaxCreditExp"存储过程. 对对象'xp_cmdshell'，数据库'mssqlsystemresource'，模式'sys'的EXECUTE权限被拒绝.

Unable to launch 'cvADPTaxCreditExp' stored procedure. The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

我已将master的xp_cmdshell的执行权限授予了代理帐户，但是仍然出现此错误.

I have granted Execute permission to xp_cmdshell in master to the proxy account, but am still getting this error.

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

我已经对此进行了验证:

I have verified it with this:

select  * from  sys.credentials

还有其他需要设置安全性的地方吗?

Is there someplace else security needs to be set?

推荐答案

在SQL Server 2008 R2计算机上运行的批处理文件中使用bcp.exe时，我遇到了相同的问题:用-U -P指定用户名/密码返回"NativeError = 18456"(登录失败)，我使用带有参数-T的受信任的连接"解决了该问题.

I had the same problem using bcp.exe in a batch file running on a SQL Server 2008 R2 machine: specifying user/password with -U -P was returning "NativeError = 18456" (login failed) and I resolved it using "trusted connection" with parameter -T.

BCP.EXE发生错误18456的呼叫为:

BCP.EXE call WITH ERROR 18456 was:

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

这篇关于SQL 2012 bcp调用返回SQLState =28000.NativeError = 18456用户登录失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！