Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
geofflilley
Frequent Visitor

Cannot Connect to an Oracle Database...GOING INSANE, READY TO QUIT

Hi. Let me get a couple of things out of the way. Even though this problem (which has plagued me for 18 hours and counting) makes me feel like a complete USELESS IDIOT, I have evidence that I'm not. (MCP ID is 3145964, I'm an MCT, I've been using computers for 25 years, I have a 126 IQ. I'm not STUPID. But this problem sure makes me feel like it.)

 

If you looked at my Google history, you'd see that I've been back and forth between Oracle forums and Microsoft Power BI forums continually, trying each solution listed therein. I've downloaded the ODBC driver, the Instant Client, directly from the links provided in the instructions on the PowerBI page.

 

I have an Oracle ODBC connection that I've configured in the ODBC 64 control panel, and I can successfully ping the server, as well as connect via SQL Developer.

 

I can connect neither via ODBC nor Oracle.

 

For ODBC, the error I get is "Restricted data type attribute violation." The only two "workarounds" or "solutions" I've seen after hours of searching are, "are you running the latest version of PowerBI?" (yes, checked twice) and "Yeah, me too, can't seem to get it to work, bummer, any help, Microsoft?" And "send a frown." (Done, no optimism.)

 

For Oracle, the error that I get is "The recommended provider ('Oracle.Data.Access.Client') is not installed. You can continue with your current provider, however it has been deprecated and may not work correctly."

 

I clicked Learn More, and downloaded the software as specifically stipulated therein. Same error message.

 

I've uninstalled and re-installed the software six times, changed the PATH variable, rebooted, hacked registry entries, you name it.

 

I'm sorry if I sound snippy or short; the deal is, I'm supposed to be the Microsoft expert at my office, and I literally have no one to turn to, and I have a multi-million dollar deal that's predicated on me getting this right. So no pressure. 😞

6 REPLIES 6
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi  @geofflilley,

 

Have you downloaded the Oracle client based on the official document here?

 

In addition,  could you please upload the screenshots for your error messages?

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

"Have you downloaded the Oracle client based on the official document here?"

 

Yes. To reiterate in my original post, yes, several times. I clicked Learn More, got directed to that link, followed the directions to the absolute letter of the law, and I get the same error message, contained in this screenshot, which I quoted verbatim in my original post:

 

ODAC Error Message.png

 

 

At first, I downloaded the 64-bit version, since I am running 64-bit PowerBI. Same error. I downloaded the 32-bit client as a precaution. Same error.

 

Trying to connect via ODBC gets me a different error, which, as previously stated, is "Restricted data type attribute violation. Details: DataSourceKind=Odbc." That's the one with dozens of posts on this forum to the effect of, "Yeah, me too, bummer, any help, Microsoft?" To wit:

ODBC ErrorODBC Error

ODBC Connection 01ODBC Connection 01

 

 

ODBC Connection 02ODBC Connection 02ODBC Connection 03ODBC Connection 03

So I can see the connection in PowerBI when I do it via ODBC, and I can get all the way to the screen where it looks like I can access a table, but then, when I actually go to select one, no dice.

 

The only thing I've gotten to work so far is the DevArt 3rd-party ODBC driver. It's slow as molasses in January, but it works.

 

I have a very large vested interest in making this work, since my company partners with MS, and we want to offer this as a viable solution to our clients, but when I as a sales engineer with a technical background, and presumed technical expertise, can't get this thing to work, I can't offer proof of value, so I'm kind of screwed right now. Please help.

@geofflilley,

 

This may not be of much help but when I was attempting to connect to my Oracle DB (pretty sure the Instructor had us start with 14) on AWS for a school project I had the same issue. I ended up upgrading to the newest release, at the time, of Oracle in a new instance.  I know that is probably not an option for you however after upgrading everything to the most current it was able to connect. Oh, I vaguely remember something about creating an entirely new user/pass as well in one of the Oracle ODAC screens that was not my ADMIN or user account. It was odd but that could have been it as well. Since it was a school project I was just trying to get it done and unfortunately did not take the best notes on how I did it.

 

It was harder than it needed to be that's for sure. I thought it was because I am a student however even my instructor and local 'experts' couldn't provide assistance.

 

It can be done, is pretty much all the hope I can give you. Best of luck.

 






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



That makes sense. On a lark, I tried re-installing ODAC using a newly formed user, in the screen in question. Didn't make any difference. But it was a good thought.

 

On the matter of the Oracle platform upgrade - wish I could. Were it up to me, I'd be running this thing on the most current version, but that decision is out of my hands.

 

Thanks for trying.

Hi @geofflilley,

 

What's version of Oracle do you use? Please share your version of your Oracle Database.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I assume you're referring to the version of the Oracle database I'm accessing? 11g. That's something that I cannot control.

 

On an unrelated note; having just returned from a business trip to the South, there's a joke that says, all great stories from a Southerner begin with, "y'all ain't gonna believe this..."

 

So here's my "Y'all ain't gonna believe this" - I installed PowerBI and ODAC on my dev box, which had a pristine install of Windows 10 Pro on it (and almost nothing else). I created the ODBC connection to the same database in C:\Windows\System32\odbcad32.exe.

 

Once again, same "restricted data type attribute" trying to use the ODBC connection.

 

When I tried to connect via Oracle, using the named instance in the TNSNAMES.ORA file, it connected without any errors.

 

So the generic answer, I can see is, "there's something weird with your installation of Windows, since you installed PBI and Oracle on almost virtually identical hardware, and got a different result." 

 

While I concur that's the case, my problem is, I'm gonna have a LOT of explaining to do if I have to bust out a second computer to demo the beauty of this feature. That doesn't really bode well for me.

 

So I'm lucky, most men would have broken both legs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.