1

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.

5
  • What is your ssis package trying to do? Sep 7 '16 at 14:52
  • 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. Sep 7 '16 at 16:02
  • @SQLDataInTraining The SSIS is copying data from one SQL server to another nothing else. Sep 7 '16 at 17:41
  • @JonathanFite connection string added to the main body. Sep 7 '16 at 17:42
  • The connection string you have provided isn't using SQL authentication. It has no username and password and has Integrated Security=SSPI Sep 8 '16 at 7:01
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/

2
  • 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. Sep 7 '16 at 19:39
  • 1
    Looks like a bog standard SQL Server connection string to me. Sep 8 '16 at 6:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.