A. First make sure that you have the rights to run xp_cmdshell - do an xp_cmdshell 'dir' and check you get a resultset of filenames back.
The MSSQLSERVER service is running under a separate set of NT credentials. It doesn't matter who YOU are logged on as (after all SQL runs quite happily when no-one is logged on to the console doesn't it). Therefore your logon account and any mapped drives are irrelevant. It is SQL Server running the program (e.g. bcp) not you.
If you're logged on as "sa" or are still running SQL 4.x then xp_cmdshell runs with the NT account that the mssqlserver service is configured to run under. The default set of NT credentials used by MSSQLSERVER is the Localsystem account. You can check what userid that MSSQLSERVER is running under by looking at control panel/services highlighting MSSQLSERVER and choosing the start-up option. If no username is present then you are using the localsystem account - this account has no access to shares on the network as it isn't an authenticated network account.
If you're not logged on as "sa" then another account may be used :-
With 6.5 and below it is used if the option (in SQL setup) 'xp_cmdshell - simulates Client' is checked.
With SQL 7.0 the other account is always used for non "sa" users.
This account is SQLExecutiveCmdExec for SQL 6.x and SQLAgentCmdExec for SQL 7.0. These accounts are only in the local user group by default.
So, if you want bcp running under xp_cmdshell to access a network resource you have two choices :-
1. Change the account the MSSQLSERVER service runs under to a user account with the relevant network rights.
or
2. Amend the following registry value on the TARGET server and add the sharename you want to access - the share does not then authenticate who is coming in and so a Localsystem account will work. The server service on the target server must be re-started before the change takes effect. Note that this effectively removes security on that share, so you need to be careful about what is in the share.
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\Parameters\NullSessionShares
Whichever method you use, you MUST use a UNC name to reference the resources required and not a drive letter.
i.e. xp_cmdshell 'bcp servername..tablename out \\server01\share\bcp.fil .............'
