Articles in this section

See more

Creating a Linked Server Between SingleStoreDB Self-Managed (Linux) and SQL Server (Windows)

Overview

This KB article explains how to connect Microsoft SQL Server (on a Windows EC2 instance) to a SingleStore on‑prem cluster using a Linked Server via the SingleStore ODBC driver. It covers prerequisites, UI and T‑SQL setup, validation, and common troubleshooting.


Background

SQL Server connects to ODBC drivers through the Microsoft OLE DB Provider for ODBC Drivers (MSDASQL). Using MSDASQL with the SingleStore ODBC driver allows SQL Server to issue queries to SingleStore as a Linked Server. Ensure the DSN is 64‑bit and visible to the SQL Server service, and prefer connecting to a SingleStore aggregator endpoint for client workloads.

Summary :-

  • Use the Microsoft OLE DB Provider for ODBC Drivers (MSDASQL) to bridge SQL Server to the SingleStore ODBC driver.
  • Configure a 64‑bit System DSN pointing to the SingleStore aggregator/host and client port (example: 3306).
  • Create the Linked Server via SSMS UI; optionally use a DSN‑less provider string.


Architecture and Flow

SQL Server (MSDASQL provider) → ODBC Driver (SingleStore ODBC) → SingleStore aggregator endpoint.
 


Prerequisites

  • Windows EC2 with:
    • Microsoft SQL Server (64‑bit) and SSMS (64‑bit)
    • Local admin rights to manage ODBC sources
  • Network access from Windows EC2 to the SingleStore client endpoint/port (Security Groups/Firewall updated accordingly)
  • SingleStore user with required privileges on the target database(s)
  • SingleStore ODBC Connector for Windows x64 installed:


Step 1 : Create a 64‑bit System DSN for SingleStore

  1. Open “ODBC Data Sources (64‑bit)”.
  2. Go to System DSN → Add → select SingleStore ODBC ANSI Driver (or Unicode, as required). You should see the SingleStore drivers listed among available ODBC drivers.


     
  3. Configure the DSN:
    • Server/Host: SingleStore host or VIP (prefer aggregator where available)
    • Port: SingleStore client port (e.g., 3306)
    • Username/Password: SingleStore credentials

    • Database: optional (can be specified later)
      Example configuration is shown in the setup dialog where host and port are provided.

  • Click “Test DSN” and confirm “Connection successfully established.”

Notes:

  • Ensure you are using the 64‑bit ODBC Administrator and 64‑bit SingleStore driver.
  • A System DSN is required so the SQL Server service can see it.
     


Step 2 : Create the Linked Server (SSMS UI)

  1. In SSMS: Server Objects → Linked Servers → New Linked Server…
  2. General:
    • Linked server: e.g., S2LINK
    • Provider: Microsoft OLE DB Provider for ODBC Drivers (MSDASQL)
    • Product name: SingleStore
    • Data source: the exact System DSN name from Step 1 (e.g., s2link)
    • Provider string: leave blank (for DSN‑based), or see DSN‑less option below
    • Catalog: optional default database
  3. Security:
    • Choose “Be made using this security context”
    • Remote login: SingleStore username
    • With password: SingleStore password
  4. Provider options:
    • If needed, enable “Allow inprocess” for MSDASQL: SSMS → Server Objects → Linked Servers → Providers → MSDASQL → Properties → check “Allow inprocess” (restart SQL Server service if prompted)

Click OK to create.

Once this is done, we will be now able to see the schema from SingleStore Cluster!
 

Troubleshooting Tips

  • Login timeout / connectivity errors: Validate network routing and port access. 
  • ODBC test succeeds but Linked Server fails: Ensure MSDASQL is enabled on the SQL Server instance.

Related articles