Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2012
    Posts
    52

    Unanswered: BCP Password Issue

    Hi guys,

    I desperately need help with my BCP script everything works good so far I do not add in the script my password as -P actualpassword

    However I would like to know how to do this in the script since I need to import plenty of files into mysql server.
    The script I have looks like this

    @echo off
    bcp TestDB3.dbo.xxx_data in "Z:\path\data\XXX.txt" -F 2 -S servername -U MY_USER_NAME -f "Z:\path\data\format-data.fmt"
    pause

    the above script works fine but, asks me all the time to prompt my password. However if if I try to add the password as in the following scrip it will complain that the login has failed:


    @echo off
    bcp TestDB3.dbo.xxx_data in "Z:\path\data\XXX.txt" -F 2 -S servername -U MY_USER_NAME -P xxxxxxx -f "Z:\path\data\format-data.fmt"
    pause


    Any ideas how to fix this? Thanks in advance.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    SQL login or Windows authentication?
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2012
    Posts
    52
    sql server authentication

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I wouldn't have thought having a command file with both the username and password to an SQL account that has permissions to modify data is a good idea. Have you considered using windows auth (-T)? Remember that if you have this script set up to run in a job then it runs using the credentials of the account that SQL Server Agent uses.

    If you insist on using SQL auth then have you confirmed your ability to connect directly to the SQL instance (using SSMS for instance) using the details in your script? If you can then check the accounts ability to modify data.

    It will also be useful if you post the actual error message in full.
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2012
    Posts
    52
    Thanks for the answer, I have considered to use windows auth, but I would like to know why there is a -P option if it does not work?

    This is the error I get:

    SQLSTATE = 28000, NativeError = 18456
    Error = [Microsoft] [SQL Server Native Client 10.0] [SQL Server]Login failed for user 'myname'

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The -P option does work

    So the login is failing. Check that you can connect to the instance using SSMS with these SQL login details (change the drop down from Windows Auth to SQL Auth when connecting). Then check that this account can access the database in question.
    George
    Home | Blog

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Do you have any special characters in the password that may need to be escaped?

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My first guess is that there is a Windows "special character" inside the password.

    Purely for testing purposes to see if I'm correct, create a second SQL Authenticated user with only letters and numbers in both the user name and the password (no special characters at all). Modify your script to use this username and password.

    If the modified script will run using the sanitized username and password, then we need to explore which characters you are using and how you need to work around the problem. You can find details at remarks if this is the case.

    -PatP
    Last edited by Pat Phelan; 01-12-12 at 10:35.
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Is the password password?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jan 2012
    Posts
    52
    Sorry for the late answer, and thanks for the help guys. The password is exactly the same password, since it is a script I am able to see with letters the password, so I am 100% sure I am not missing something, also there are no slashes on it that might make believe the password has some special characters.

    I think the easier thing will be to get a windows authentication, rather than sql.

    Thanks a lot!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •