Creating a Linked Server to Access Excel Files in SQL Server

In the world of modern data management and analytics, organizations face constant pressure to unify data from multiple sources – ERP systems, CRM platforms, Excel spreadsheets, APIs, and cloud storage. One of the most common yet overlooked integration scenarios is connecting Microsoft SQL Server directly to Microsoft Excel files for dynamic querying, reporting, and automation.

SQL Server provides a robust mechanism for achieving this via Linked Servers, enabling analysts, data engineers, and business users to access external data seamlessly.

In this article, we’ll walk you through, step-by-step, how to create a linked server to access Excel files in SQL Server = complete with examples, SQL code snippets, and expert insights. We’ll also explore real-world business applications, security considerations, and best practices for scaling this solution in enterprise environments.

What is a Linked Server?

A Linked Server in Microsoft SQL Server allows you to access data from an external data source as if it were part of your local database instance. It extends SQL Server’s native querying capability to include external platforms, whether they’re Excel workbooks, Oracle databases, MySQL servers, flat files, or Azure SQL databases.

Essentially, a Linked Server acts as a bridge between SQL Server and external data systems, supporting distributed queries, joins, updates, and transactions across systems.

For example, you can:

• Query Excel files directly using T-SQL.
• Join Excel data with SQL Server tables in real-time.
• Use Excel-based datasets as staging data for ETL workflows.
• Automate imports and transformations within SQL Server Integration Services (SSIS) or Azure Data Factory pipelines.

In a world of hybrid data ecosystems, this capability dramatically reduces manual work and enables centralized analytics.

Prerequisites

Before creating a Linked Server to connect Excel and SQL Server, ensure the following are in place:

1. Microsoft SQL Server installed and running (any version from SQL Server 2016 onward supports Linked Servers).
2. Microsoft Excel file (.xlsx or .xls) that you want to access.
3. Microsoft Access Database Engine (ACE) OLE DB provider installed on the SQL Server machine. This provider enables Excel connectivity.
4. Appropriate permissions and administrative rights to configure Linked Servers within SQL Server Management Studio (SSMS).

Step 1: Enable the ‘Ad Hoc Distributed Queries’ Option

Before you can access external data sources such as Excel, you need to enable SQL Server’s Ad Hoc Distributed Queries option.

Run the following T-SQL command in SQL Server Management Studio (SSMS):

Step 2: Create a Linked Server

Now, let’s create a Linked Server that connects directly to your Excel file.

1. Open SQL Server Management Studio (SSMS) and connect to your SQL Server instance.
2. In Object Explorer, navigate to:
   Server Objects → Linked Servers → Right-click → New Linked Server…

You’ll now see the New Linked Server dialog box, where you’ll configure connection properties.

Configuration Steps:

• General Tab:
  • Linked server: Enter a descriptive name (e.g., EXCEL_LINKED_SERVER).
  • Server Type: Choose Other data source.
  • Provider: Select Microsoft Office 12.0 Access Database Engine OLE DB Provider (adjust version as necessary).
  • Product Name: Enter Excel.
  • Data Source: Provide the full file path to your Excel workbook (e.g., C:\Data\Finance_Report.xlsx).
  • Provider String: Enter Excel 12.0 (for .xlsx files) or Excel 8.0 (for .xls files).
• Security Tab:
  Choose authentication options. You can either:
  • Use the current login’s context, or
  • Specify a remote login and password if accessing secured file paths or network drives.

Once done, click OK to create the Linked Server.

For cloud environments, you can use Azure File Storage or SharePoint document libraries as your Excel file path — expanding access for distributed teams.

Step 3: Accessing Excel Data Using SQL Queries

After creating the Linked Server, you can query your Excel data as though it were a native SQL Server table.

Example query:

SELECT *

FROM OPENQUERY(EXCEL_LINKED_SERVER, ‘SELECT * FROM [Sheet1$]’);

This T-SQL command uses the OPENQUERY function to pull all records from the Excel worksheet named Sheet1.

You can now perform:

• Filtering: WHERE clauses to filter records.
• Joining: Combine Excel data with SQL Server tables.
• Aggregation: Apply GROUP BY, SUM(), COUNT(), or AVG() for reporting.

For example:

SELECT s.CustomerID, e.[Sales Amount]

FROM dbo.SQL_CustomerData s

INNER JOIN OPENQUERY(EXCEL_LINKED_SERVER, ‘SELECT * FROM [Sheet1$]’) e

ON s.CustomerID = e.CustomerID;

This kind of cross-source query allows organizations to blend Excel-maintained business data with SQL Server’s transactional datasets, perfect for finance, inventory, or audit reporting.

4. After configuring these settings, navigate to the ‘Security’ tab and choose the security options that match your environment. You can use the current login’s security context or specify a remote login and password.

5. Click ‘OK’ to create the linked server.

Security configuration determines how SQL Server authenticates to the Excel file.

• For local Excel files, “Use current security context” is fine.
• For networked environments, specify a domain service account with explicit file permissions.

Using SQL Authentication with limited access is best practice for production-grade implementations.

Step 3: Accessing Excel Data

You can now use T-SQL queries to access data from your Excel file as if it were a regular table. Here’s an example of retrieving data from the Excel file:

You can perform various SQL operations like filtering, joining, and aggregating data from the Excel sheet.

This opens a world of automation. You can now join Excel data with transactional systems, apply filters, or even build temporary staging tables for Power BI or SSRS reports directly from live Excel data.

For enterprises, this functionality bridges the gap between business-managed spreadsheets and IT-governed analytics, reducing shadow data silos.

Step 4: Importing Excel Data into SQL Server Tables

You can import data from Excel into SQL Server tables using `SELECT INTO` statements. Here’s an example of how to create a new table and populate it with data from Excel:

Now you have successfully created a linked server to access Excel files in SQL Server, retrieved data from Excel, and imported it into a SQL Server table.
Once imported, your Excel data becomes a first-class citizen in SQL Server. You can apply indexing, transformations, and even integrate with Azure Data Factory for downstream analytics.

To automate this process:

• Create a SQL Agent job that runs on schedule (e.g., nightly import).
• Validate Excel schema changes before loading.
• Archive previous imports for auditability.

This allows recurring Excel updates (like monthly financial data) to feed dashboards automatically.

Using linked servers to access Excel files in SQL Server can be a powerful tool for integrating data from various sources into your database. It enables you to perform SQL operations on Excel data seamlessly. By following the steps outlined in this blog post, you can set up your linked server, query your Excel data, and even import it into your SQL Server tables.

Remember to handle security and permissions carefully and ensure that your Excel file paths are correctly specified. With this knowledge, you can efficiently work with Excel data within your SQL Server environment.

This workflow, while simple, sits at the heart of data democratization, empowering organizations to unify operational and analytical data.

From a CFO or CIO perspective, this approach reduces manual overhead, mitigates risk from Excel-based reporting, and prepares the enterprise for migration to Microsoft Fabric or Azure Synapse with clean, structured datasets.

At Addend Analytics, we help companies operationalize these capabilities, not just by creating Linked Servers, but by building end-to-end data pipelines that transform how teams use Excel, SQL Server, and Power BI together.