Failed to acquire connection. Connection may not be configured correctly or you may not have the right permissions on this connection.

Question

Failed to acquire connection. Connection may not be configured correctly or you may not have the right permissions on this connection.

Ali-437314

Old Hand

Points: 387
More actions
May 26, 2014 at 1:51 am

#285863

I have a package that connects to Oracle and brings data. It also deletes some from Oracle.
So when the first step runs where the data is brought from Oracle into SQL Server, it runs fine. But when the deletion step runs, following error is shown.
Failed to acquire connection "My Connection". Connection may not be configured correctly or you may not have the right permissions on this connection.
This "My Connection" is basically OracleClient Data Provider connection.
The thing is the same package is working fine on two other machines but this 3rd machine it is not working on. The only differences between those 2 machines and this one are:
1. This new machine is part of SQL Cluster while other two are standalone
2. This new machine is Windows Server 2012 R2 and the other two are Windows 2012
So I am thinking could this error be due to Windows Server 2012 R2? Like somethings may have changed in this Windows version due to which it is not working? Or this error is due to some Oracle misconfiguration on machine?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

Are there any configurations in the package?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Ali-437314 Old Hand Points: 387 More actions · Answer 2

I don't think so. Although it reads connection string from a table in SQL Server.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

Ali-437314 (5/26/2014)
I don't think so. Although it reads connection string from a table in SQL Server.

That might be the issue.

Check if there are any validation warnings when running the package.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Ali-437314 Old Hand Points: 387 More actions · Answer 4

Koen Verbeeck (5/26/2014)
Ali-437314 (5/26/2014)
I don't think so. Although it reads connection string from a table in SQL Server.
That might be the issue.
Check if there are any validation warnings when running the package.

When running the package source through SQL Server Data Tools, it give warning that it is loading connection string from database which is perfectly fine as this same package is also working on two other machines.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 5

Ali-437314 (5/26/2014)
Koen Verbeeck (5/26/2014)
Ali-437314 (5/26/2014)
I don't think so. Although it reads connection string from a table in SQL Server.
That might be the issue.
Check if there are any validation warnings when running the package.
When running the package source through SQL Server Data Tools, it give warning that it is loading connection string from database which is perfectly fine as this same package is also working on two other machines.

Can you post the warning?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Rooster Mr or Mrs. 500 Points: 523 More actions · Answer 6

Right click on the solution and select properties. Under Configuration Properties -> Debugging change "Run64BitRuntime" to False and try again. The drivers you have installed are most likely 32bit not 64bit