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.
Integrated Security=SSPI