Post reply

SQL Server SSIS Package Configuration with sql server authentication not working

  • October 6, 2012 at 3:10 pm

    #145714

    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.

    Thank you in advance for yours advices.

  • October 8, 2012 at 7:18 am

    #1546711

    The password has to be in the connection string.

    Thomas LeBlanc, MVP Data Platform Consultant

  • October 8, 2012 at 7:58 am

    #1546733

    Thanks! This worked. For anyone who will read this post, steps to follow if you want to use SQL configuration SSIS Package with SQL Authentication:

    - after you generate [SSIS Configurations] table with ConnectionString checked(it is not mandatory to check Password, because it will not work anyway), go to SSMS open new query and update ConfiguredValue column:

    -update [SSIS Configurations] set ConfiguredValue = 'Data Source="YourServerName";User ID=sa(or another user);Password="YourPassword";Initial Catalog="YourDatabaseName";Provider=SQLNCLI10.1;Persist Security Info=True;'

    where ConfigurationFilter = "YourFilterName" . The value automatically generated does not contain the Password. The drawback is that you have the password in plain text.

    -execute task once again

    Hope this helps others too!

    Thanks again Thomas LeBlanc!

  • October 8, 2012 at 7:59 am

    #1546734

    For me Topic closed.

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

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