BCP Command, Error - Login failed for user

Question

BCP Command, Error - Login failed for user

Learner093

Old Hand

Points: 359
More actions
June 14, 2012 at 3:42 pm

#271130

I am using this BCP command to output the data from a table into the file, and getting this error message:
SQLState = 28000, NativeError = 18456
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'domain\username'.
Here's the command I have:
bcp "SELECT * FROM database.dbo.TableName" queryout \\sharedlocation\Test\File.DAT -S"SERVERNAME" -U"domain\username" -P"password" -o"\\sharedlocation\Test\Log.txt"
I do not want to have -T (trusted connection) rather want to have SQL authentication. Am I missing anything in the command? Please help!!

Viewing 8 posts - 1 through 7 (of 7 total)

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

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

How is the ID set up in SQL Server? Is it set up to be a windows authenticated id? If so, putting the network name and the password on the command line isn't going to work. You'll either have to use a trusted connection or set up a separate ID with SQL Server Authentication.

And then again, I might be wrong ...
David Webb

Learner093 Old Hand Points: 359 More actions · Answer 2

Learner093

Old Hand

Points: 359

June 14, 2012 at 4:38 pm

#1501313

Thanks, that worked!

axpetursson SSC Journeyman Points: 91 More actions · Answer 3

It looks like you are passing a domain\windows_acct to what is supposed to be a sql_login

Nevarda SSC Veteran Points: 298 More actions · Answer 4

Hi,

I use this BCP command in a SP and get the same user login error.

I use widows Auth when connecting but have tried SQL server auth as well with the same error.

SP Part is as follows:

...

begin

set @cmd = 'bcp.exe ' +

@dbName + '..' + @tbName + ' in ' +

@filePath + ' -c -U ' + @usr +

' -P ' + @pwd + ' -t ' + @sep

print @cmd --+ '...'

exec xp_cmdShell @cmd

end

and my call to this would be...

Exec uSp_Import_Table 'SampleDB',

'INV_StageTable',

'c:\Test\Aut_2014-04-22.csv',

',',

'MyUserName',

'MyPassword'

and still get the same error.

any ideas?

Lowell SSC Guru Points: 323504 More actions · Answer 5

you cannot pass a windows username and password. it's just not allowed.

you can pass a SQL username and password, or use the -T for trusted connection, which uses the windows account the SQL instance starts with in services... which may or may not have access to the path for the file in question.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323504 More actions · Answer 6

you'd probably be much better off using BULK INSERT over bcp + xp_cmdshell anyway:

--in via bulk insert

BULK INSERT INV_StageTable FROM 'c:\Test\Aut_2014-04-22.csv'

WITH (

DATAFILETYPE = 'char',

FIELDTERMINATOR = ',',

ROWTERMINATOR = '\n',

FIRSTROW = 1

)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Nevarda SSC Veteran Points: 298 More actions · Answer 7

Hi,

Thanks Lowell, I'll give that approach a go.

I think i did try it but it was forever complaining about a type mismatch or invalid character.

i think its because the file is originally in Excel Format and saved as a .CSV before dropped into a server directory where a SP finds it

and in that process the DateTime gets saved as 123456.654321 value but i'll have another look.

Thanks a mil.

if i dont come right ill get a shout

Nevarda