CODEWITHSUNDEEP
ssissql-server-2008-r2lookup
abdu hakin
abdu hakin

Reputation: 1

Why does SSIS connection works with Windows but not with SQL Server authentication?

I have an SSIS package that contains a Lookup Transformation. This requires a connection to the database. At the connection manager, when I use the Windows authentication the connection works fine but when I use SQL Server Authentication then I get this error:

[A140_DAMAGETYPETEXT [3799]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "servername.Datenbankname.sqlUser" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Connection string:

Data Source=TheServerName;User ID=TheSQLUser;Initial Catalog=TheDababase;Provider=SQLNCLI10.1;Persist Security Info=True;Application Name=NameOfThePackage{80F4BFAB-642D-457E-9F47-1E811AFA48D0}ServerName.TheDababas‌​e.TheSQLUser;

Upvotes: 0

Views: 8141

Answers (2)

abdu hakin
abdu hakin

Reputation: 1

here was the solution for me: click

the thread from Raj_007 was the rescue. I use a sequece container and the TransactionOption was setting to required.

I changed the TransactionOption to supported and it worked for me.

Upvotes: 0

user756519
user756519

Reputation:

Cause of the issue:

Your connection string is missing the Password attribute. That is the reason why the connection manager is failing to connect to the database instance.

Possible option to fix:

When you create the connection manager on SSIS, make sure to check the Save my password box so that the Password is retained in the connection string. If you do not check this box, the value will not be retained and you have to key in the value.

Connection Manager

Package configuration:

If you are using package configurations, adding / updating the connection manager will cause the Password attribute to drop from the connection string property. I usually use database based package configuration. So, I use a query to update the connection string in the package configuration table every time I update the package configurations.

Recommendation:

I prefer to use Windows authentication so that the password to connect to the database is not exposed. I feel that Windows Authentication is little more secure than dealing with SQL Server Authentication.

Upvotes: 2

Related Questions