Solved

Trying to use a cData ODBC DSN in Excel to connect to an OLAP cube in Azure Analysis Services on macOS.

  • 6 June 2023
  • 3 replies
  • 78 views
W
Badge

Objective

Trying to use a cData DSN in Excel to connect to an OLAP cube in Azure Analysis Services on macOS. I can successful setup the DSN in iODBC Data Manager and it tests successfully, but when I try to use the DSN in Excel I get a “Could not bind server socket: Operation not permitted”.   I have screen-grabbed all my steps below in the attached PDF.

 

 

icon

Best answer by Elizabeth G 6 June 2023, 22:16

View original
1 Attachment

3 replies

E
Userlevel 4
Badge

Thank you for reaching out. The "Could not bind server socket: Operation not permitted" typically indicates that another process is blocking a port that the driver is attempting to use. If AzureAD/OAuth authentication is being used, it is likely that port 33333 is blocked, as this port is used for the OAuth flow. To resolve this, you can kill the process that is blocking this port by running 'sudo lsof -i :33333' from the terminal to find its PID (process ID), and then use the command 'kill -9 <PID>' to stop it, as described in the link below:

https://codinhood.com/nano/macos/find-kill-proccess-port-macos

If the issue continues to persist, however, then the issue is likely caused by Excel not recognizing that the port is open rather than the driver not recognizing this, as the driver's test connection is able to succeed. In this case, it may be better to use Connect Cloud instead of the ODBC driver, which should be able to establish a connection from Azure Analysis Services to Excel and function as an alternative to the ODBC driver. Please find a free, fully functional, 30-day trial of Connect Cloud, along with instructions on how to connect it to Azure Analysis Services and Excel, below.

Trial: https://cloud.cdata.com/auth/sign-up-initial
https://www.cdata.com/cloud/
Azure Analysis Services: https://cloud.cdata.com/docs/AAS.html
Excel: https://cloud.cdata.com/docs/Excel-Desktop.html

W
Badge

Thanks, @Elizabeth G, for the thorough reply.

Connect Cloud does work fine in terms of downloading the data to Excel, but the problem is we’re using an Excel pivot table to filter data from an OLAP cube with over 60 million records.  Connect Cloud doesn’t help us directly connect the pivot table to the OLAP cube and there’s too much data to download locally to Excel and have the pivot table connect to it locally.

So, it looks like the  cData DSN ODBC driver seems to be the only viable choice for us. I did check in macOS terminal to see if port 33333 being blocked was causing the problem, but it wasn’t listed as being in use.  Do you have any other ideas or should I just acknowledge that I’m up against a brick wall here?

Many thanks.

 

E
Userlevel 4
Badge

@wecanseeformiles Thank you for your response. Regarding the ODBC driver, if the port is proven to be unoccupied, then the issue appears to be with the Mac machine or Excel not recognizing that the port is open. If they are having issues with port 33333, you may wish to change which port is used in the OAuth flow by creating a custom Azure AD app and setting the Redirect URI. For example, if you set the Redirect URI to http://local host:33334, the OAuth flow will use port 33334. Then, in your DSN configuration, you can set the CallbackUrl to http://local host:33334 so it matches the Redirect URI. Instructions on these steps are provided below.

(A note on the Redirect URI/Callback URL listed above: the forum doesn’t seem to like this URL and wouldn’t let me send the reply without first adding the space. Please remove the space between ‘local’ and ‘host’ from the URL when you are actually using it)

Custom AzureAD app: https://cdn.cdata.com/help/OAH/odbc/pg_azureadoauthcustomappcreate.htm
Configuring DSN: https://cdn.cdata.com/help/OAH/odbc/pg_connectionodbcmac.htm#azure-ad

Alternatively, if it is possible to use the driver on a Windows machine instead, you may wish use try this. Macs are unfortunately notorious for being restrictive about ports, so the same configuration will typically succeed on a Windows machine.

Please let me know if any further clarification is needed.

Reply


Virtual Event: Data Virtualization, Reimagined Ma”arch 14, 2024 style=
Terms and ConditionsCookie settings