SQL Server SSIS Package Configuration with sql server authentication not working

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » SQL Server SSIS Package Configuration with...

SQL Server SSIS Package Configuration with sql server authentication not working

Rate Topic

Display Mode

Topic Options

Author

Message

manole_i

Posted Saturday, October 06, 2012 3:10 PM

Grasshopper

Group: General Forum Members
Last Login: Sunday, April 28, 2013 12:21 PM
Points: 10, Visits: 243

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.

Post #1369467

Thomas LeBlanc

Posted Monday, October 08, 2012 7:18 AM

SSCrazy

Group: General Forum Members
Last Login: Monday, May 20, 2013 3:33 PM
Points: 2,705, Visits: 717

The password has to be in the connection string.

Thomas LeBlanc, MCITP DBA 2005, 2008 & MCDBA 2000
http://thesmilingdba.blogspot.com/

Post #1369799

manole_i

Posted Monday, October 08, 2012 7:58 AM

Grasshopper

Group: General Forum Members
Last Login: Sunday, April 28, 2013 12:21 PM
Points: 10, Visits: 243

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!

Post #1369825

manole_i

Posted Monday, October 08, 2012 7:59 AM

Grasshopper

Group: General Forum Members
Last Login: Sunday, April 28, 2013 12:21 PM
Points: 10, Visits: 243

For me Topic closed.

Post #1369826

« Prev Topic | Next Topic »

Permissions