Connecting Excel to SQL Server With Power Query and ODBC
Connect Excel to SQL Server with the built in connector or ODBC: choose Windows or SQL authentication, encryption, a native query and read only access.
Of all the databases Excel talks to, SQL Server is the best supported, which is to say it is the one where the connection usually works the first time and the interesting problems are about security and speed, not setup. This post covers the built-in connector, the ODBC alternative, authentication and encryption choices, native queries and the permissions to give the account. It is a spoke of connecting Excel directly to a database.
The built-in connector
On the Data tab choose Get Data, From Database, From SQL Server Database. The dialog asks for:
Server: the name or address, with
\instancefor a named instance or,portfor a non-default port, for exampledb01\REPORTINGordb01,1433.Database (optional): if left blank, Excel shows a navigator of all databases you can see.
Data connectivity mode: Import (copy the data into the workbook) or DirectQuery (not available in Excel; it exists in Power BI). In Excel it is Import.
Advanced options: command timeout in minutes, a SQL statement and options for relationship columns and hierarchy navigation.
Click OK. Excel asks how to authenticate (below), shows a navigator or the preview of your query, and offers Load or Transform Data.
The connector needs no separate driver on current Windows and Office versions, which is why it is the first choice.
Authentication
Three tabs appear on the credential prompt.
Windows. Uses your Windows account (or an alternate one you enter). The server never sees a password from you, and access is governed by the groups and logins in SQL Server. This is the best choice inside a domain: no password in the workbook, individual accountability. It requires that SQL Server accepts your account, and, for connections across machines, that Kerberos or NTLM works for your session.
Database. A SQL Server login and password. Use it when users are outside the domain or when a shared reporting login is required. The password is stored in the Excel credential store in your profile, not in the workbook file.
Microsoft account / organizational account. For Azure SQL Database with Microsoft Entra ID (formerly Azure Active Directory) sign-in. Modern and MFA-friendly, and worth using for cloud databases.
Choose the narrowest option your environment allows, and never use the sa login for reporting.
Encryption and certificates
Recent versions of the Microsoft drivers encrypt connections by default, and the Power Query connector offers an Encrypt connection checkbox. If the server's certificate is not issued by an authority your machine trusts, such as a self-signed certificate on a development server, the connection fails with a message about the certificate chain or "SSL Provider". The right fix is to install the issuing certificate on the client so that it is trusted, or to ask for a proper certificate. Unchecking encryption makes the message go away and sends credentials and data in the clear on the network. Reserve that for an isolated test machine.
In ODBC connection strings the settings are Encrypt=yes and TrustServerCertificate=no. ODBC Driver 18 made encryption mandatory by default, which broke connections to servers that had worked with Driver 17, and the remedy is the certificate, not turning it off.
Run a native query
Under Advanced options paste a statement:
SELECT c.Region,
YEAR(o.OrderDate) AS OrderYear,
SUM(l.Quantity * l.UnitPrice) AS Revenue
FROM dbo.Orders o
JOIN dbo.Customers c ON c.Id = o.CustomerId
JOIN dbo.OrderLines l ON l.OrderId = o.Id
WHERE o.OrderDate >= '2025-01-01'
GROUP BY c.Region, YEAR(o.OrderDate);The first time, Excel shows a Native Database Query dialog asking you to approve the statement. This protects you from workbooks that someone else built that run statements you did not expect. Read the statement before approving, and treat a workbook from an unknown source that asks to run SQL as you would a macro. The approval is stored per statement; changing the statement asks again.
Native queries stop Power Query from folding later steps into the SQL for some connectors. Do the filtering and column choice in the statement itself, and keep later steps light. The alternative, building the query from the navigator with Power Query steps, lets folding push filters and group-bys to the server, which suits simple cases (slow Excel refresh from a database).
For queries that depend on a parameter, see using parameters in a database query from Excel.
The ODBC route
Use Get Data, From Other Sources, From ODBC when the connector is not available (some older Office builds), or when you must reuse a DSN. Install Microsoft ODBC Driver 17 or 18 for SQL Server, matching Excel's bitness, and create a data source in the matching ODBC administrator (connecting Excel to PostgreSQL with ODBC describes the 32 versus 64-bit administrators). The connection string form:
Driver={ODBC Driver 18 for SQL Server};Server=db01,1433;Database=Shop;Trusted_Connection=yes;Encrypt=yes;Trusted_Connection=yes uses Windows authentication. For SQL logins use Uid= and ask for the password in the Excel prompt. Do not place a password in a string that you save in the workbook.
A read-only account
In SQL Server, create the login and user and give it read rights on what the report needs:
CREATE LOGIN report_reader WITH PASSWORD = 'set-from-a-password-manager', CHECK_POLICY = ON;
USE Shop;
CREATE USER report_reader FOR LOGIN report_reader;
GRANT SELECT ON SCHEMA::reporting TO report_reader;Granting SELECT on a reporting schema full of views gives access to those and to nothing else. The simpler ALTER ROLE db_datareader ADD MEMBER report_reader; grants read on every table in the database, which is wider than most reports need. For Windows authentication, create the login from a domain group (CREATE LOGIN [DOMAIN\Analysts] FROM WINDOWS;) so that joining and leaving the team is handled in the directory (giving Excel users a read only database account). Row-level security can limit what each person sees.
Typical problems
| Message | Likely cause |
|---|---|
Cannot open database requested by the login | The login exists but is not mapped to a user in that database |
Login failed for user | Wrong password, disabled login or the wrong authentication type |
A network-related or instance-specific error | Server name wrong, SQL Browser stopped for a named instance, firewall, TCP/IP disabled |
The certificate chain was issued by an authority that is not trusted | Self-signed certificate; install the CA certificate |
Timeout expired | A slow query or a short timeout; raise it in Advanced options and fix the query |
The server principal is not able to access the database under the current security context | A user cannot reach a database through impersonation or cross-database access |
When a refresh hangs, use Activity Monitor or sys.dm_exec_requests on the server to see the running statement. A query that takes a minute in Excel and a second in Management Studio is usually sending a different statement, such as a full table fetch followed by client-side filtering.
Checking the result
Verify counts and totals against a direct query in Management Studio, as in the other connection posts. Check data types: datetime2 and date come through as dates, decimal as decimal numbers, and bigint above 2^53 loses precision in a double. Large varchar(max) columns are cut to 32,767 characters on a sheet (Excel cell contains more than 32767 characters). Collation affects joins and grouping inside the query, not what Excel shows.
When a direct Excel connection to SQL Server is the wrong design
Many users refresh heavy queries at the same time. Put the result in a summary table or an indexed view refreshed by a job.
The audience needs row-level restrictions you cannot grant individually. Use a service with proper security.
The data is regulated. Cached rows in workbooks are copies you cannot control; use approved reporting tools.
The workbook writes back. Direct writes from Excel bypass your application's rules (keeping Excel as the front end to a database).
See Microsoft's pages on the SQL Server database connector and on downloading the ODBC driver. To convert a small result to JSON, use the CSV to JSON converter.
