I can successfully connect to my Azure SQL from Excel M365 macOS using SQL user/pass. I can also do the same with Excel M365 Win.
I can equally create connections from Excel M365 Win using the authentication option "Use the authenticated user's account". In this way the user appears to be authenticated as "Authentication=ActiveDirectoryPassword".
The problem is that:
- I cannot create connections in Excel M365 macOS using "Authentication=ActiveDirectoryPassword" and
- When connections are made in Excel M365 win I cannot refresh them in macOS with error "[DataSource.Error] Microsoft SQL: Cannot open server "mycompanydomain.com" requested by the login. The login failed."
In summary, our use-case requires cross-platform connectivity and we wish to stay away from SQL user/pass authentication. Ideally users connect as role-based Azure Active Directory users such that their ability to effect query changes depends on their role (Admin, Contributor etc).
Based on extensive research and trial and error it seems that authenticating using Azure Active Directory is not possible in macOS.
Note OBDC driver 17.8 is installed in macOS using brew commands in terminal with mssql-tools.