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.

Improve this question
5
  • What is your ssis package trying to do?
    – James Rhoat
    Sep 7, 2016 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.
    – Jonathan Fite
    Sep 7, 2016 at 16:02
  • @SQLDataInTraining The SSIS is copying data from one SQL server to another nothing else.
    – Stephen Archbold
    Sep 7, 2016 at 17:41
  • @JonathanFite connection string added to the main body.
    – Stephen Archbold
    Sep 7, 2016 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
    – Martin Smith
    Sep 8, 2016 at 7:01

1 Answer 1

Reset to default
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/

Improve this answer
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.
    – Stephen Archbold
    Sep 7, 2016 at 19:39
  • 1
    Looks like a bog standard SQL Server connection string to me.
    – Martin Smith
    Sep 8, 2016 at 6:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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