BCP query out - cannot execute from .Net code

Question

BCP query out - cannot execute from .Net code

crab-592037

Old Hand

Points: 385
More actions
September 29, 2011 at 4:57 pm

#390511

Folks-
I have a Proc that executes a BCP query out of another proc, and writes the results file to a UNC path. This works great in a query editor window. I am trying to run it through a .Net console app and it's failing.
If I set the connection string as follows:
Initial Catalog=MYDB;Data Source=MYSERVER;user id=domain\windowsUsername;password=THEPWD;
I get "Login failed for user 'domain\windowsUsername'". If I remove "DOMAIN'" I get the same error. The password is correct. This user is a domain admin with rights to the folder.
If I switch the connection string to
Initial Catalog=MYDB;Data Source=MYSERVER;user id=SQLSERVERUsername;password=THEPWD;
I get "Could not obtain information about Windows NT group/user 'SQLSERVERUsername', error code 0xffff0002.
This is the typical username i use in all my .Net apps, works like a charm. Except now when I need to write that file out.
I ran this , but it does not help.
create credential ##xp_cmdshell_proxy_account## with identity = 'domain\windowsUsername', secret = 'THEPWD'
I was under the impression that this would create an account that the bcp would run under in the event that the logged in user did not have rights to run xp_cmdshell.
Either way, it's not working. If i run another simple proc that does not write to the file system, either of these logins works fine.
Thanks for looking,
Crab

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Seems to me that you're running the BCP command with integrated security.

In order to log on with windows authentication, the BCP process must be spawned using the windows credentials of the target login.

Something like this:

System.Diagnostics.ProcessStartInfo psi = new System.Diagnostics.ProcessStartInfo();

string password = "somePassword";

fixed (char* pChars = password.ToCharArray())

{

pass = new System.Security.SecureString(pChars, password.Length);

}

psi.UserName = "someUser";

psi.Password = pass;

psi.Domain = "DOMAIN_NAME";

//Run the process

psi.FileName = "C:\\Program Files\\Microsoft SQL Server\\90\\Tools\\Binn\\bcp.exe";

psi.Arguments = "BCP arguments here";

//This has to be set to false when running as a different user

psi.UseShellExecute = false;

psi.ErrorDialog = true;

psi.LoadUserProfile = true;

psi.WorkingDirectory = "c:\\";

System.Diagnostics.Process.Start(psi);

Hope this helps,

Gianluca

-- Gianluca Sartori

crab-592037 Old Hand Points: 385 More actions · Answer 2

Nice code, thanks. The proc that's doing the BCP is just a part of a larger process; create a file, write it to a windows server,email it, then also move it to a JDE FTP server.

I guess I'm starting over. The request is that the user be able to do this all alone with the click of an icon.

Crab