'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Connect to Azure Database Without Microsoft 365 Enterprise
Question details
The user is looking for a way to connect to an Azure database without upgrading to an expensive Microsoft 365 Enterprise license and wants to understand the limitations of using ODBC alternatives.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Attempting to query data from an Azure database for use in Power Query and PivotTables without a premium Microsoft 365 Enterprise subscription.
- Observed behavior
- Standard Microsoft 365 versions restrict direct Azure connections. While an ODBC connector works as a bridge, it disables advanced Power Query features like query folding.
Ensure you have the correct Azure database server credentials, your IP address is whitelisted in the Azure portal firewall settings, and you have administrative rights to install ODBC drivers on your PC.
Use an ODBC Connector to Access Azure Data
Create a System DSN using an ODBC driver to connect standard versions of Microsoft Excel to your Azure database without needing an Enterprise license.
While direct Azure Database connections in Power Query are restricted to Enterprise editions of Microsoft 365, you can use the Open Database Connectivity (ODBC) protocol to bridge the gap. This allows you to import data into standard Excel versions for analysis.
Visit the official Microsoft website and download the latest 'ODBC Driver for SQL Server' for your operating system.
Open the 'ODBC Data Sources' application in Windows. Go to the 'System DSN' tab, click 'Add', select the installed SQL Server driver, and enter your Azure server address and login credentials to establish the connection.
Open Excel, navigate to the 'Data' tab, click on 'Get Data', select 'From Other Sources', and then choose 'From ODBC'.
Select the DSN you just created from the dropdown menu, input your database credentials if prompted, and click 'Load' to bring the data into Power Query or a PivotTable.
Contact Microsoft for Enterprise Licensing
If native connections and query folding are critical for your workflow, upgrading to an Enterprise plan is required.
Try WPS Office for Powerful Data Analysis at No Cost
Tired of expensive subscription tiers restricting standard data analysis tools? WPS Office offers a lightweight, highly compatible alternative to Microsoft Office. With WPS Spreadsheet, you can easily perform complex data analysis, build pivot tables, and manage large datasets without the enterprise price tag.
- 1. Download WPS Office: Visit the official WPS website and download the free installation package for your operating system.
- 2. Install and Launch: Run the installer, follow the on-screen instructions, and launch WPS Spreadsheet.
- 3. Open Your Data: Seamlessly open your existing Excel workbooks and continue analyzing your data without any costly subscriptions.
Frequently Asked Questions
What is query folding in Power Query?
Query folding is an optimization feature in Power Query where your data transformation steps are automatically converted into native SQL statements. These statements are executed by the source database (like Azure), which significantly speeds up processing by avoiding downloading the entire dataset to your local machine.
Why can't I connect directly to Azure SQL with standard Microsoft 365?
Microsoft restricts direct, native Azure database connections in Power Query to its Enterprise-tier licenses (such as Microsoft 365 Apps for enterprise or Office 365 E3/E5) to separate premium corporate features from consumer and small business software.
Are there other free ways to practice Power Query?
Yes. If you want to learn and practice Power Query without purchasing an Enterprise license, you can connect to free local data sources. This includes CSV files, local Excel workbooks, web data sources, or a free local installation of SQL Server Express.
