locked
BCP Connection Error: SQLState = 28000, NativeError = 18456 RRS feed

  • Question

  • The following bcp command using xp_cmdshell doesn't work returning SQL Connection error.  Looks like bcp command doesn't take Windows-Authenticated ID.
    exec master.dbo.xp_cmdshell 'bcp [DBTest].[dbo].TableTest1 IN "\\server12\d$\test.csv" -S"Server12\isnt12" -U"Domain1\test" -P"Test$12" -c -t","'

    SQLState = 28000, NativeError = 18456
    Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'Domain1\test'.


    However, the following works, which uses SQL-Authentication Login ID.
    exec master.dbo.xp_cmdshell 'bcp [DBTest].[dbo].TableTest1 IN "\\server12\d$\test.csv" -S"Server12\isnt12" -U"test" -P"test" -c -t","'

    Is there anyway to use Windows-Authenticated ID for bcp command?  Please advise.
    Wednesday, November 18, 2009 8:54 PM

Answers

  • a) If using SQL Server authentication in which case you have to specify your SQL Server login and password, say your SQL Server login ID is user and password is pass then you should specify -Uuser -Ppass.

    b) For using Windows authentication, then just specify -T and it will used trusted connection  (do not use –U it will force BCP to sql authentication ) 

     

    does the following work?

    exec master.dbo.xp_cmdshell 'bcp [DBTest].[dbo].TableTest1 IN "\\server12\d$\test.csv" -S"Server12\isnt12" -T -c","'

    Wednesday, November 18, 2009 9:23 PM

All replies

  • a) If using SQL Server authentication in which case you have to specify your SQL Server login and password, say your SQL Server login ID is user and password is pass then you should specify -Uuser -Ppass.

    b) For using Windows authentication, then just specify -T and it will used trusted connection  (do not use –U it will force BCP to sql authentication ) 

     

    does the following work?

    exec master.dbo.xp_cmdshell 'bcp [DBTest].[dbo].TableTest1 IN "\\server12\d$\test.csv" -S"Server12\isnt12" -T -c","'

    Wednesday, November 18, 2009 9:23 PM
  • I want to actually specify the domain id and pw, ano not use the sql id.  Is there a way to do so?
    Wednesday, November 18, 2009 11:02 PM
  • Go to the path C:\Program Files\Microsoft SQL Server\90\Tools\Binn and find bcp.exe application and right click and run as with your domain account and then specify -T in your command (dont specify the -U and -P parameter).. So it will use your domain account credentials..


    Thanks, Leks
    Thursday, November 19, 2009 12:28 AM