connection manager losing credentials

On my SSIS package I have 2 connection managers, both are connecting to an Oracle db. I enter in the ID and Password then click 'test connection' I'm able to connect to the database fine. I then go to a data flow of one of my control flow task. Open up my OleDb Source and click preview. The SQL query executes with no issues. I can see the data from my connection manager source.

I then go and run the package and I get this error message:

[OLE DB Source [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "OracleConnection" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

I've been racking my brain on this for a few days and still no luck on getting this to work.

You probably have the 'protection level' property of the package set to 'dontsavesensitive'. You could either change this to 'encryptsensitivewithuserkey' or set the password property of the connection manager through a configuration.

|||

I've done both, changed the package property and tried a config file and I get the same results.

|||Enable package logging, or if you are using BIDS, check the progress tabs and see if you any warning about package configurations.

Package configuration should solve the problem. This KB article has some related info:

http://support.microsoft.com/kb/918760|||

I got it working actually now, well sort of.

I had to remove all of my connection managers, close down BIDs open it back up, add new connection managers, change the package property setting again and it work.

Now I'm running into another issue

I have one package (dtsx file) that calls other dtsx file (all within the same solution) and its unable to connect to the packages it needs to call, though all the packages are in the same solution and the same database

|||Please provide more details:

How are you running the packages (BIDS, DTEXEC, etc)?

Where are the packages stored?

Error messages?|||

I'm trying to run it in BIDS right now and I get this error message:

Error: Error 0xC0012050 while preparing to load the package. Package failed validation from the ExecutePackage task. The package cannot run. .

If I deploy them to my SQL server and try to execute it from there (right click, execute), I'm getting this error message:

Error: Error 0xC0012050 while preparing to load the package. Package failed validation from the ExecutePackage task. The package cannot run. .

and I list of warnings saying it can't find the connection manager and so on.

|||You have a problem here. If you remove a connection manager; you need to go to every single place where that connection manager was being referenced and provide a new/valid connection manager.

The message you are getting indicates that the package failed validation; that means SSIS won't even try to run it. You need to remove all the references to that connection manager(s) you deleted.|||

This is driven my insane.

I can now run the SSIS package in BIDS, but I get the error:

'the acquireconnection method call to the connection manager 'oracle' has failed

and I've removed all of the connection managers used and created new ones and I still get the above error on my SQL server

connection is successful for empty userid

Hi,
In my application , the connection to SQLServer is established
through a DSN name, UserId and Password(both UsrId and Password are
non empty) . The "SQLConnect" function is used for this purpose. The
problem is that the connection to the Database is success when I give
an empty UserId. But this problem is not there when the UserId is
invalid(non empty).In all the cases except for empty UserId , the
connection works as expectd.
Why is it connecting for empty UserId?
Is it because that default windows authentication is used for
connection when UserId is empty?
If so is there any way to avoid this (through a C/C++ program)?
pls help
Thanks
KaimalIf the connection is success full and accessing the data base objects (table
,
view, stored procedure, etc) fails then this likely failing due to a
ownership chain problem. Recommend reading (SQL Server Books on Line) BOL
topics.
Using Ownership Chains
Logins
sp_grantdbaccess
sp_grantlogin or sp_addlogin
TSQL GRANT statement
I think what you are asking is to allow Public or Guest permission to your
database. Is this what you are after?
SQL Server's default behaviour is to lock all database objects. You have to
grant permission to the objects before they are accessible.
"Kaimal" wrote:

> Hi,
> In my application , the connection to SQLServer is established
> through a DSN name, UserId and Password(both UsrId and Password are
> non empty) . The "SQLConnect" function is used for this purpose. The
> problem is that the connection to the Database is success when I give
> an empty UserId. But this problem is not there when the UserId is
> invalid(non empty).In all the cases except for empty UserId , the
> connection works as expectd.
> Why is it connecting for empty UserId?
> Is it because that default windows authentication is used for
> connection when UserId is empty?
> If so is there any way to avoid this (through a C/C++ program)?
> pls help
> Thanks
> Kaimal
>|||> Is it because that default windows authentication is used for
> connection when UserId is empty?
>
Yes.

> If so is there any way to avoid this (through a C/C++ program)?
AFAIK, you'll need to check for the empty userid in your application code
and allow the connection only when not empty.
Hope this helps.
Dan Guzman
SQL Server MVP
"Kaimal" <rbkaimal@.hotmail.com> wrote in message
news:19aff12c.0504220154.142bf7e3@.posting.google.com...
> Hi,
> In my application , the connection to SQLServer is established
> through a DSN name, UserId and Password(both UsrId and Password are
> non empty) . The "SQLConnect" function is used for this purpose. The
> problem is that the connection to the Database is success when I give
> an empty UserId. But this problem is not there when the UserId is
> invalid(non empty).In all the cases except for empty UserId , the
> connection works as expectd.
> Why is it connecting for empty UserId?
> Is it because that default windows authentication is used for
> connection when UserId is empty?
> If so is there any way to avoid this (through a C/C++ program)?
> pls help
> Thanks
> Kaimal|||checking for empty UserId wil not help . I have to verify for the user
id depending on the authentication set in the ODBC driver. Here user is
free to set a blank id or other way round for a particular DSN.
The actal problem is that i have set a non empty UserId and Password for
a my DSN and the connection is established when i gave empty userid for
this DSN.
i would like to know about any ODBC function to this default connect.
can any thing be done with SQLSetConnectAttr function?
Thanks
Kaimal
*** Sent via Developersdex http://www.examnotes.net ***|||Allowing a "empty UserId and Password" is not a good Idea. You potentially
are opening up your SQL Server and database to security threats similar to
the Code Red virus. I think what you are after is an application base
security model. Check out the BOL sp_addapprole.
"Ragesh Kaimal" wrote:

> checking for empty UserId wil not help . I have to verify for the user
> id depending on the authentication set in the ODBC driver. Here user is
> free to set a blank id or other way round for a particular DSN.
> The actal problem is that i have set a non empty UserId and Password for
> a my DSN and the connection is established when i gave empty userid for
> this DSN.
> i would like to know about any ODBC function to this default connect.
> can any thing be done with SQLSetConnectAttr function?
> Thanks
> Kaimal
>
> *** Sent via Developersdex http://www.examnotes.net ***
>