none
sql server package configuration with sql authentication not working

    Question

  • Hi! 
    I am new to BI and i have a dilema. I created a data source with SQL Server Authentication(username: sa, Password: Password) and a package, then I tried to configure Package Configuration. I have chosen SQL Server connection type and for [SSIS Configurations] i've checked from Connection Managers node -> My DataSource -> "Connection" String and "Password". I go in DataFlow -> Execute Task and i get a error message:

    "[Customer Source [464]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "AdventureWorks2008" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
    "
    In Windows Application log the error message is:
    "Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: <local machine>]"

    If i have the password in ConfiguredValue column of [SSIS Configurations] table why can I not execute succesfully the package?
    If i chose windows authentication everything goes fine.
    Sunday, October 07, 2012 8:26 AM

All replies

  • this happens sometimes when you try to create a sql server authentication mode .when u create connection and enter the password and test the connection the connection would be fine but when u try to run the package u will get the error stating "cannot acquire connection...."

    the workaround is create a xml package configuration for this where u store only connection string,username and password(when u create package config check the boxes next to only these ).Save the package and run.

    And if still ur package fails stating the same error.Go to xml configuration open ur config file in notepad and do control f and find user u wont be finding password next to it so keep a semi colon after user and ;Password=urpassword; Include this and save it.Close ur package open it and things should work this time

    • Edited by Nishink Sunday, October 07, 2012 8:58 AM
    Sunday, October 07, 2012 8:50 AM
  • Check this thread and try the 4 methods mentioned by Rafael in the kb article.

    http://btsbee.wordpress.com/

    Sunday, October 07, 2012 9:11 AM
  • Thank you Nishink, i've already try this method is working just fine(smoothly). I am in a learnig process of BI and I was curious why is not working this method with sql authentication.

    Sunday, October 07, 2012 8:04 PM
  • thanks but is not mt case. I am not running the package from SQL Server Agent, it is run from BIDS.
    Sunday, October 07, 2012 8:07 PM