SAS OLE DB connection in Power BI

[amar17]

Hi folks,

I need to connect Power BI to SAS using an OLE DB connection (can't use ODBC nor the native connection). Here is the string from the build:

provider=sas.IOMProvider.9.45;data source="iom-name://SASApp - Logical Workspace Server";mode="ReadWrite|Share Deny None";sas cell cache size=10000;sas port=0;sas protocol=0;sas server type=1;sas metadata user id=dxru984;sas metadata password=XXXXX;sas metadata location=iom-bridge://lhwappb1.xxx-xx.xxx:8561

I also tried with this one:

Provider=sas.IOMProvider.9.45;Data Source=iom-name://SASApp - Logical Workspace Server;SAS Cell Cache Size=10000;SAS Port=0;SAS Protocol=0;SAS Server Type=1;SAS Metadata User ID=dxru984;SAS Metadata Password=xxxxxxx;SAS Metadata Location=iom-bridge://lhwappb1.xxx-xx.xxx:8561

The first string works perfectly with Excel but not in PowerBI with that error message:

OLE DB : Format of the initialization string does not conform to the OLE DB specification

Any idea would be appreciated.

thanks

[JuanS_OCS]

Hello @amar17,

I think this question would be best addressed to the Microsoft's Technical Support. Have you already drop this question at them?

[AlanC]

Wild guess but try switching your double quotes to single.

Why can't you use ODBC?

[HugoFonseca]

My connection work, by removing some default parameters: provider=sas.IOMProvider.9.45;data source=[APP_HOST];sas port=8591;sas protocol=2;sas metadata user id=[USERID];sas metadata password=[PASSWORD];sas metadata location=[META_HOST]:8561

I saw the solution in https://www.connectionstrings.com/sas-iom-provider/

[SASKiwi]

@HugoFonseca - I'm giving this a go myself with Power BI Desktop 2.56. What SAS version did you get this going with? I'm trying it with SAS 9.4M2.

My connection string is:

provider=sas.IOMProvider;data source=MySASAppServer;sas port=8592;sas protocol=2;sas metadata location=MyMetadataServer:8561;

Then I'm relying on the Power BI user name and password prompt to supply the logon credentials.

 I can see in the SAS metadata server log that connections are being successfully opened, but then straightaway they are closing. Then Power BI repeats trying to connect. In the Power BI connection window the "busy wheel" displays and never stops. No errors are shown. The Cancel button stops the continuous attempts.

Any help would be much appreciated! I'll open a Tech Support track as well.

[HugoFonseca]

I'm using SAS 9.4M3, my Metaserver and AppServer are in Windows and the libnames in the AppServer use a FedServer driver (my FedServer is in Linux Suse).

Share my list of products in AppServer:

Base SAS Software 29JUN2019

SAS/STAT

SAS/GRAPH

SAS Integration Technologies

SAS/Secure 168-bit

SAS/Secure Windows

SAS Enterprise Guide

SAS/ACCESS Interface to PC Files

SAS/ACCESS Interface to ODBC

---SAS Workspace Server for Local Access

---High Performance Suite

---SAS Add-in for Microsoft Excel

---SAS Add-in for Microsoft Outlook

---SAS Add-in for Microsoft PowerPoint

---SAS Add-in for Microsoft Word

[dtravassos]

Hi,

Do you guys know if it's possible for SAS EG to connect to a power bi server?

Thanks

Danielle

[Tom]

SAS Enterprise Guide is a GUI tool that runs on Windows and helps you generate and run SAS code on a SAS server (possibly your same WIndows machine). I don't think there is any reason for EG itself to connect to Power BI.  But since you can use SAS EG to run actual SAS code then just use the methods already suggested to allow your SAS session to connect.

[KoketsoM]

A little late to the party but I used the following string to connect:

provider=sas.IOMProvider.9.45;data source=demo;sas port=8591;sas protocol=2;sas metadata user id=domain\userid;sas metadata password=PassWord;sas metadata location=demo:8561;

provider=sas.IOMProvider.9.45;

data source=server;

sas port=8591;

sas protocol=2;

sas metadata user id=domain\userid;

sas metadata password=PassWord;

sas metadata location=server:8561;

Hope it helps.