when running SSIS package with Windows Auth it works but with SQL Auth it fails

Question

I have setup a SSIS package, and within one of my connections to the same server that the package is on it fails when I use SQL server Auth for login and it does not matter which user I put in.

I can put in my user name which works fine when logging in from SQL Management studio and which works fine when I run the package manually and the connection is set to Windows Auth.

The error I get is Test connection failed because of an error in initializing provider. Login failed for user.

I even tried using our SA account and it fails as well.

Connection string: Data Source=SQL004;Initial Catalog=SAP_TO_E1;Provider=SQLNCLI10.1;Integrated Security=SSPI;Application Name=SSIS-Package-{1AE2C7B9-91AC-34B3FB255590}SQL004.SAP_TO_E1;Auto Translate=False;

It's an OLEDB connection.

• James Rhoat: What is your ssis package trying to do?
• Jonathan Fite: Could you post the connection strings that are being used? You can strip out any sensitive information, but it would be helpful to see what each connection string is set to.
• Stephen Archbold: @SQLDataInTraining The SSIS is copying data from one SQL server to another nothing else.
• Stephen Archbold: @JonathanFite connection string added to the main body.
• Martin Smith: The connection string you have provided isn't using SQL authentication. It has no username and password and has Integrated Security=SSPI

Answer 1

I believe your connection string is off based on what you added, I would try looking at the following website. It looks like your string is designed for a CE device...

https://www.connectionstrings.com/sql-server-2008/

• Stephen Archbold: The connection string is generated by the SSIS when I setup the connection. I'll check your link out to see if I can figure it out.
• Martin Smith: Looks like a bog standard SQL Server connection string to me.