BCP Export Login Failed for User NT Authority/Anon

Question

SQL Server is online and I'm trying to export data to a remote server. I'm using Windows Authentication to log on sql server.

Code:

declare @sql1 varchar(8000), @asofdate varchar(50)

set @asofdate = convert(varchar,getdate()-1,112) + '.csv'

select @sql1 = 'bcp DB1.dbo.Table1 out C:\exp\test' + @asofdate  + ' -c -t, -T -S'+ '.\SQL2005'
exec master.. xp_cmdshell @sql1

but I get error

[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

If I try

exec xp_cmdshell 'del C:\exp\*.csv'

The code works fine. So I think I do have access to xp_cmdshell.

Then I wonder why my xp_cmdshell doesn't work for bcp export?

Thanks for any advice!

Answer 1

You're passing off a command to an external application.

This external application doesn't really know you, or where your command came from. Hence the ANONYMOUS LOGON message.

You need to tell the command who to execute as. Have a look at BCP in BoL (http://msdn.microsoft.com/en-us/library/ms162802.aspx) and you'll spot a few parameters that you might wish to add:

-T Specifies that the bcp utility connects to SQL Server with a trusted connection using integrated security. The security credentials of the network user, login_id, and password are not required. If –T is not specified, you need to specify –U and –P to successfully log in.

-U login_id Specifies the login ID used to connect to SQL Server.

-P password Specifies the password for the login ID. If this option is not used, the bcp command prompts for a password. If this option is used at the end of the command prompt without a password, bcp uses the default password (NULL).