SQL 2012 bcp call returns SQLState = 28000. NativeError = 18456 Login failed for user

Question

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.

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

Running this in SSMS gives me the following:

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

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.

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.

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

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.

---

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:

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

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?

Answer 1

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 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 WORKING call is:

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