Connection Strings

I have created a SSIS package, and got it to perform as I want.

I am having a problem though with connecting to my data source (OLE DB Source) using SQL Server Authentication.

If I use Integrated security, I have no problems. If I use SQL Server Authentication, I get errors.

Here is the connection string that works:

Provider=SQLNCLI.1;Data Source=.\SqlExpress;Integrated Security=SSPI;Initial Catalog=MyDataBase

Here is the connection string that fails:

Provider=SQLNCLI.1;Data Source=.\SqlExpress;Persist Security Info=True;Password=**********;User ID=myUserid;Initial Catalog=MyDataBase

The strange thing is that when I use the Connection Manager dialog screen and click on "Test Connection", I get the response that the connection succeeded. It is only when I try to run the task that I get an error.

Here is the error I am getting:

[OLE DB Source [1]] Error: The AcquireConnection method call to the connection manager "MyDataBase" failed with error code 0xC0202009.

[DTS.Pipeline] Error: component "MyDataBase" (1) failed validation and returned error code 0xC020801C.

I have eliminated the obvious errors (incorrect Userid or password). Also, I am unable to use Integrated Security as this package needs to be distributed to various systems that may or may not be on our company's primary domain.

It seems I am missing something really basic here, but am not seeing it.

Any thoughts?

Has this ever worked for you? Are you able to create a brand new package and it still fails? Are you using package configurations?|||

Has this ever worked for you? Yes, using integrated security

Are you able to create a brand new package and it still fails? No, actually I tried a new simple package, and it works.

Are you using package configurations? No

I think the key here is your second point. I created a new package, and the connection now works. So, the question is how do I fix my original package? I'm not really thrilled about creating a brand new package being that this one is quite complicated.

Suggestions?

|||Well, thanks to R.K.S. response, I created a brand new package, this time setting the connections to use SQL Server authentication from the beginning, and it fixed my issue. But what a PIA!!! I spent way too much time researching and trying to figure out the problem, and never did find what is was, nor how to fix it. There must be a better way.|||

WRBehning,

Welcome to the new world of SSIS! I have had similar problems, especially when you copy and paste items. Basically I think the internal Ids in SSIS get messed up somehow. Alot of times if you delete and re-add an item it fixes the problem. Doesn't give me great confidence when an "enterprise solution" has so many strange problems. Good Luck! (to us all)

|||

WRBehning wrote:

Well, thanks to R.K.S. response, I created a brand new package, this time setting the connections to use SQL Server authentication from the beginning, and it fixed my issue. But what a PIA!!! I spent way too much time researching and trying to figure out the problem, and never did find what is was, nor how to fix it. There must be a better way.

If you have a RFE (Request for Enhancement) then file it at http://connect.microsoft.com with a detailed write-up.

Only thru doing this will SSIS get closer to being the product that people want it to be.

-Jamie

|||

WRBehning wrote:

Well, thanks to R.K.S. response, I created a brand new package, this time setting the connections to use SQL Server authentication from the beginning, and it fixed my issue. But what a PIA!!! I spent way too much time researching and trying to figure out the problem, and never did find what is was, nor how to fix it. There must be a better way.

Can you explain exactly what the problem was in the original. Its not clear from this?

If its something that people need to know then I'd like to add it here: http://blogs.conchango.com/jamiethomson/archive/2006/10/18/SSIS_3A00_-Random-information-for-beginners.aspx

Thanks

-Jamie

|||The issue I had was that I created the SSIS package using Windows Authentication for my OLEDB connections. After I had completed the package, I tried to switch to SQL Server Authentication and even though the "Test Connection" responded with "Test Connection Succeeded", the package would not run. Finally, like I stated earlier, I created a new package, using Sql Server Authentication from the beginning, and the issue was resloved.|||

WRBehning wrote:

The issue I had was that I created the SSIS package using Windows Authentication for my OLEDB connections. After I had completed the package, I tried to switch to SQL Server Authentication and even though the "Test Connection" responded with "Test Connection Succeeded", the package would not run. Finally, like I stated earlier, I created a new package, using Sql Server Authentication from the beginning, and the issue was resloved.

That's worrying if it didn't work. However I have previously had no problems with changing connection strings (for that is what we're talking about here).

One thing that can cause problems is if (for example) the ServerName property doesn't marry up with the server name that you are setting in the ConenctioNString property although in your case the only difference is in authentication mode so I shouldn't expect it to be a problem.

I've also tried your repro steps as above and didn't experience any problems.

What version/service pack are you on?

-Jmie

|||

Here you go...

A couple of things I should mention.

I tried deleting and re-creating the connections and this did not solve the problem.

The connections are connecting to SQL Express, not standard SQL, though I do not see where this would be a consideration.

SQLExpress (SQL Server 9.0.2047)

Microsoft Visual Studio 2005
Version 8.0.50727.42 (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727

Installed Edition: Professional

Microsoft Visual Basic 2005 77626-009-0000007-41520
Microsoft Visual Basic 2005

Microsoft Visual C# 2005 77626-009-0000007-41520
Microsoft Visual C# 2005

Microsoft Visual C++ 2005 77626-009-0000007-41520
Microsoft Visual C++ 2005

Microsoft Visual J# 2005 77626-009-0000007-41520
Microsoft Visual J# 2005

Microsoft Visual Web Developer 2005 77626-009-0000007-41520
Microsoft Visual Web Developer 2005

Crystal Reports AAC60-G0CSA4B-V7000AY
Crystal Reports for Visual Studio 2005

SQL Server Analysis Services
Microsoft SQL Server Analysis Services Designer
Version 9.00.2047.00

SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 9.00.2047.00

SQL Server Reporting Services
Microsoft SQL Server Reporting Services Designers
Version 9.00.2047.00

|||

WRBehning wrote:

Here you go...

A couple of things I should mention.

I tried deleting and re-creating the connections and this did not solve the problem.

The connections are connecting to SQL Express, not standard SQL, though I do not see where this would be a consideration.

SQLExpress (SQL Server 9.0.2047)

Microsoft Visual Studio 2005
Version 8.0.50727.42 (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727

Installed Edition: Professional

Microsoft Visual Basic 2005 77626-009-0000007-41520
Microsoft Visual Basic 2005

Microsoft Visual C# 2005 77626-009-0000007-41520
Microsoft Visual C# 2005

Microsoft Visual C++ 2005 77626-009-0000007-41520
Microsoft Visual C++ 2005

Microsoft Visual J# 2005 77626-009-0000007-41520
Microsoft Visual J# 2005

Microsoft Visual Web Developer 2005 77626-009-0000007-41520
Microsoft Visual Web Developer 2005

Crystal Reports AAC60-G0CSA4B-V7000AY
Crystal Reports for Visual Studio 2005

SQL Server Analysis Services
Microsoft SQL Server Analysis Services Designer
Version 9.00.2047.00

SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 9.00.2047.00

SQL Server Reporting Services
Microsoft SQL Server Reporting Services Designers
Version 9.00.2047.00

Seems we're on the same. Strange.

Oh well. Let's chalk it up to experience. If it happens again, let us know.

Thanks

Jamie

|||

I am experiencing the same behaviour.

The package was first created using EncryptWithUserKey setting, and the connection credentials was for a trusted connection to a SQL Server.

We changed the package security to use EncryptWithPassword, and the connection credentials stayed the same and continued to work properly.

When we changed the connection credentials to use a Standard SQL login, I would set the login and password and check the "Remember password" option. The Test Connection button was successful. Then after closing the connection manager properties, I would immediately reopen the properties and the password was gone, but the checkbox was still there, and the Test Connection would fail.

Mike