Partnership between Microsoft and Canonical

Since 2016, when Microsoft announced its intention to make Linux a first class citizen in its ecosystem, Canonical and Microsoft have been working hand in hand to make that vision a reality. Ubuntu was among the first distributions to support the preview of SQL Server on Linux. Ubuntu was the first distribution offered in the launch of Windows Subsystem for Linux (WSL), and it remains the default to this day. Ubuntu was also the first Linux distribution to support Azure’s Confidential VMs.

SQL server has been a cornerstone of the ongoing collaboration to deliver a seamless Linux experience for developers and enterprises alike. Together, we have been able to meet growing customer demands with a number of joint offerings including ready-made and jointly supported configurations for SQL Server on top of Ubuntu. In this article, we’ll take a look at our latest collaboration: SQL Server 2025 on Ubuntu.

SQL Server 2025

The General Availability of SQL Server 2025 on Ubuntu 24.04 is a milestone in our partnership. SQL Server 2025 brings several new features, including new AI capabilities such as vector search and external AI models integrations. Here’s how you can get up and running with these new capabilities.

Your new AI playground

Deploying a database server instance

Let’s start by installing LXD, our Canonical tool to easily manage virtual machines and containers.

sudo snap install lxd 
sudo snap list | awk '/lxd/ {print $3}'
sudo lxd init --minimal # configure LXD

Let’s now create a virtual machine to serve as a playground:

sudo lxc launch ubuntu:24.04 mssql25 --vm -c limits.cpu=4 -c limits.memory=6GiB -d root,size=24GiB

Next, we will be installing all the pre-requisites to get a SQL Server 2017 running:

sudo lxc exec mssql25 bash
su -l ubuntu
# Like always, we start by updating our packages
sudo apt -y update && sudo apt -y upgrade

# Download and trust the Microsoft key 
curl -fsSL https://packages.microsoft.com/keys/microsoft.asc | sudo gpg --dearmor -o /usr/share/keyrings/microsoft-prod.gpg

# Register the SQL Server repository
curl -fsSL https://packages.microsoft.com/config/ubuntu/24.04/mssql-server-2025.list | sudo tee /etc/apt/sources.list.d/mssql-server-2025.list

# Ensures new repositories are loaded
sudo apt-get update

# Install SQL server and a password generator
sudo apt-get install -y mssql-server pwgen

Let’s now set up the installed SQL Server to get a running instance as follows:

PW="$(pwgen -sB 22 1)" # Generates SQL Server compliant-password
# Prepare some environment variables to setup SQL Server
install -d -m 700 ~/.secrets/sql
install -m 600 /dev/null ~/.secrets/sql/mssql.env

cat > ~/.secrets/sql/mssql.env <<EOF
MSSQL_PID=Developer
MSSQL_SA_PASSWORD='$PW'
SQLSERVER=127.0.0.1
SQLPORT=1433
SQLUSER=sa
SQLCMDPASSWORD='$PW'
EOF

# Export every line in mssql.env as an environment variable
set -a; . ~/.secrets/sql/mssql.env; set +a

# Set up SQL Server
sudo MSSQL_SA_PASSWORD="$MSSQL_SA_PASSWORD" MSSQL_PID="$MSSQL_PID" /opt/mssql/bin/mssql-conf -n setup

# Let's check that everything went fine
systemctl status mssql-server --no-pager

We will now install some additional tools to be able to connect to SQL Server

# Download the deb package
curl -sSL -O https://packages.microsoft.com/config/ubuntu/$(grep VERSION_ID /etc/os-release | cut -d '"' -f 2)/packages-microsoft-prod.deb
# Install the downloaded deb package
sudo dpkg -i packages-microsoft-prod.deb
# Delete the downloaded package
rm packages-microsoft-prod.deb
# Install Microsoft's ODBC driver and additional tools
sudo apt-get update
sudo apt-get install -y msodbcsql18  mssql-tools18 unixodbc-dev libodbc2
echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc
source ~/.bashrc

We will run a query to check that everything is running as intended:

/opt/mssql-tools18/bin/sqlcmd -S 127.0.0.1 -U sa -No -C -P "$SQLCMDPASSWORD"
SELECT @@VERSION AS 'SQL Server Version';
GO

The above should return the exact version of SQL Server you are running. So far, we’ve made simple interactions with SQL Server. In the next section, we will test some new exciting features of the 2025 edition.

Vector type

A vector type is a contiguous array of fixed-length numbers designed to enable hardware-accelerated vectorized (SIMD) execution. A vector type is the perfect type to use to store embeddings (i.e. numerical representations) of objects manipulated by AI models such as text and images.  Let’s create a table with a column of type of vector as follows:

CREATE DATABASE DemoDb;
GO

USE DemoDb;
GO

ALTER DATABASE SCOPED CONFIGURATION SET PREVIEW_FEATURES = ON;
CREATE TABLE dbo.VectorDemo (id INT IDENTITY(1,1) PRIMARY KEY, embedding VECTOR(3) NOT NULL);
GO

INSERT INTO dbo.VectorDemo (embedding) VALUES ('[2,3,5]'), ('[7,11,13]'), ('[17,19,23]');
GO

DECLARE @q VECTOR(3) = '[7,11,13]';
SELECT embedding, VECTOR_DISTANCE('euclidean', embedding, @q) AS distance FROM dbo.VectorDemo ORDER BY distance;
GO

DECLARE @q2 VECTOR(3) = '[7,11,13]';
SELECT embedding, VECTOR_DISTANCE('cosine', embedding, @q2) AS distance FROM dbo.VectorDemo ORDER BY distance;
GO
exit

We have checked how to create a vector type and how to use the vector_distance operator. We will next provide an example of a more advanced use case involving calling an AI model and performing a similarity search.

AI models integration

One of the most exciting features of SQL Server 2025 is the ability to call AI models from within the comfort of your database in a structured way. Let’s check how:

AI models deployment

We will start by deploying a local embedding model using Ollama.

curl -fsSL https://ollama.com/install.sh | sh # Installs ollama
ollama pull nomic-embed-text # Pulls an embedding model
curl -k http://localhost:11434/api/tags  # Checks the api endpoint

Deploying a http proxy

SQL Server 2025 requires that model endpoints are exposed using https (not just plain http). We will therefore deploy and configure Caddy for that purpose as follows:

sudo apt install -y caddy 

sudo cp /etc/caddy/Caddyfile /etc/caddy/Caddyfile.bak # Keep  a backup

sudo tee /etc/caddy/Caddyfile >/dev/null <<'EOF'
https://localhost:11435 {
  tls internal
  reverse_proxy 127.0.0.1:11434
}
EOF

sudo systemctl reload caddy # Needed to pick the previous changes

# We need the system to trust Caddy's certificate by doing the following:
sudo cp /var/lib/caddy/.local/share/caddy/pki/authorities/local/root.crt /usr/local/share/ca-certificates/caddy-local-root.crt

# We need also SQL Server system to trust Caddy's certificate:
sudo mkdir -p /var/opt/mssql/security/ca-certificates
sudo cp /var/lib/caddy/.local/share/caddy/pki/authorities/local/root.crt /var/opt/mssql/security/ca-certificates/caddy-local-root.crt
sudo chown -R mssql:mssql /var/opt/mssql/security
sudo chmod 744 /var/opt/mssql/security/ca-certificates
sudo chmod 644 /var/opt/mssql/security/ca-certificates/caddy-local-root.crt

# Update the VM's trusted certificates
sudo update-ca-certificates

# Restart SQL Server 
sudo systemctl restart mssql-server

# Check a few https endpoints 
curl -w '\n' https://localhost:11435/api/tags 
curl -w '\n' https://localhost:11435/api/embed \
  -H "Content-Type: application/json" \
  -d '{ "model":"nomic-embed-text", "input":"hello from caddy" }'

Python dependencies

Let’s now proceed with the installation of the pre-requisites to connect to the SQL Server instance using a python program.

sudo apt install -y python3-openai python3-pyodbc

Calling AI models from within SQL Server 2025

We will now enable SQL Server to use an external endpoint and then restart it to ensure it correctly picked the new trusted certificates.

/opt/mssql-tools18/bin/sqlcmd -S 127.0.0.1 -U sa -C -P "$SQLCMDPASSWORD" -Q "EXEC sp_configure 'external rest endpoint enabled', 1; RECONFIGURE;"
sudo systemctl restart mssql-server

Then we will write a simple Python script to test the following:

• Connecting to the server programmatically
• Registering Ollama model via HTTPS proxy
• Creating a vector column and feeding it with some examples using the registered model
• Creating a vector-optimized index and using it to perform similarity search

cat <<EOT > ~/mssqlvector.py
import pyodbc

# Define parameters based on your sqlcmd
server = '127.0.0.1'
database = 'DemoDb'
username = 'sa'
password = "$MSSQL_SA_PASSWORD"
port = '1433'

# Connection string using SQL Server Authentication
connection_string = (
    "DRIVER={ODBC Driver 18 for SQL Server};"
    f"SERVER={server},{port};"
    f"DATABASE={database};"
    f"UID={username};"
    f"PWD={{{password}}};"
    f"Encrypt=no;"
    f"Connection Timeout=7;"
    f"TrustServerCertificate=yes;"
)

try:
    conn = pyodbc.connect(connection_string)
    cur = conn.cursor()
    print("Connected to SQL Server!")

    # Example: simple query
    cur.execute("SELECT name FROM sys.databases;")
    for row in cur.fetchall():
        print(row)

    conn.autocommit = True

    # 1) Register Ollama model via HTTPS proxy
    cur.execute("""
IF NOT EXISTS (SELECT * FROM sys.external_models WHERE name='ollama')
CREATE EXTERNAL MODEL ollama
WITH (
        LOCATION   = 'https://localhost:11435/api/embed',
        API_FORMAT = 'Ollama',
        MODEL_TYPE = EMBEDDINGS,
        MODEL      = 'nomic-embed-text'   -- 768 dims
);
""")

    # 2) Create a new table including a vector column
    cur.execute("""
DROP TABLE IF EXISTS dbo.SupportMatrix;
CREATE TABLE dbo.SupportMatrix (
        id        INT IDENTITY PRIMARY KEY,
        sentence  NVARCHAR(400) NOT NULL,
        embedding VECTOR(768)   NULL
);
""")

    # 3) Insert your Ubuntu/SQL Server lines
    texts = [
        "Ubuntu 24.04 is supported by SQL Server 2025",
        "Ubuntu 22.04 is supported by SQL Server 2022",
        "Ubuntu 20.04 is supported by SQL Server 2019",
        "Ubuntu 18.04 is supported by SQL Server 2019"
    ]
    cur.executemany("INSERT INTO dbo.SupportMatrix(sentence) VALUES (?);", [(t,) for t in texts])

    # 4) Generate embeddings in-database (using the external model)
    cur.execute("""
UPDATE dbo.SupportMatrix
SET embedding = AI_GENERATE_EMBEDDINGS(sentence USE MODEL ollama)
WHERE embedding IS NULL;
""")

    # 5) Create an ANN similarity index (DiskANN) on the vector column
    cur.execute("""
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_SupportMatrix_Embedding')
CREATE VECTOR INDEX IX_SupportMatrix_Embedding
ON dbo.SupportMatrix(embedding)
WITH (METRIC = 'cosine', TYPE = 'diskann');
""")

    # 6) Run a similarity search with VECTOR_SEARCH (uses the ANN index)
    query_text = "What Ubuntu version is supported by SQL Server 2025?"
    cur.execute("""
DECLARE @q VECTOR(768) = AI_GENERATE_EMBEDDINGS(?, ollama);

SELECT TOP (3)
  t.id,
  t.sentence,
  s.distance
FROM VECTOR_SEARCH(
    table = dbo.SupportMatrix AS t,
    column = embedding,
    similar_to = @q,
    metric = 'cosine',
    top_n = 3
) AS s
ORDER BY s.distance, t.id;
""", (query_text,))

    print(f"Top matches for: '{query_text}'\n")
    for row in cur.fetchall():
        print(row)

except Exception as e:
    print("Connection failed:", e)

finally:
    conn.close()

EOT

We then can use our python scipt as follows:

python3 ~/mssqlvector.py

We’ve checked some of the new AI-related features that you can use from within the comfort of your SQL Server database. Bringing AI closer to your data is a good recipe for getting more value out of your data without the challenges inherent to data duplication.

Another challenge companies need to address is securing the AI supply chain. Let’s look at how you can handle that next.

Your secure AI playground

In order to get security updates for up to 25,000 open source packages, you can subscribe to Ubuntu Pro (free for personal use for up to 5 machines). You can check whether you have already a subscription by running the following command:

pro api u.pro.status.enabled_services.v1 | grep -c esm

If not enabled then you can subscribe to Ubuntu Pro here. Once you get your token, just type:

sudo pro attach <token>

Now that you have covered all of your Canonical maintained packages, you should next check the providers of your other packages that aren’t included in Ubuntu Pro. The following command helps you identify all the deb packages that are not maintained directly by Canonical:

dpkg-query -W -f='${Package}\n' | while read pkg; do
    src=$(apt-cache policy "$pkg" | grep -m 1 http | awk '{print $2}')
    if [[ -n "$src" && "$src" != *ubuntu.com* ]]; then
        echo "$pkg -> $src"
    fi
done

In my testing virtual machine, the above yields the following Microsoft-provided packages:

msodbcsql18 -> https://packages.microsoft.com/ubuntu/24.04/prod
mssql-server -> https://packages.microsoft.com/ubuntu/24.04/mssql-server-preview
mssql-tools18 -> https://packages.microsoft.com/ubuntu/24.04/prod
packages-microsoft-prod -> https://packages.microsoft.com/ubuntu/24.04/prod

You should only keep trusted sources such as Microsoft and Canonical.  You need also to perform the same checks for all the other types of packages or images you might be using (containers, snaps …). Unfortunately, many companies might overlook securing the models themselves. Canonical not only provides audited AI models but also ones that are optimized for your infrastructure. Let’s look at an example.

Your optimized AI models

Canonical recently announced inference snaps that take advantage of your silicon capabilities (whether CPUs or GPUs) to accelerate your AI workloads.  Here’s how you can install one:

sudo snap install qwen-vl --beta
qwen-vl status # Checks it finished successfully

On my machine, I get:

engine: cpu-avx512
endpoints:
    openai: http://localhost:8326/v1

You can then use the newly deployed model as follows:

curl http://localhost:8326/v1/chat/completions \
-H "Content-Type: application/json" \
-d '{ "model": "<model-name>", "messages": [{"role": "user", "content": "Who is the publisher of Ubuntu?"}],
"temperature": 0.7
}' | jq

You should then get a response to the supplied prompt.

Running optimized AI models along Ubuntu Pro and SQL Server provides you with a secure and reliable stack to support your AI workflows. Canonical and Microsoft can help you go further with jointly supported offerings.

Your supported AI playground

We saw together how Canonical and Microsoft help you run an integrated AI stack from the operating system up to the database engine and the AI models. Canonical and Microsoft  deliver prebuilt Azure VM images with pre-configured SQL Server on top of Ubuntu Pro, with support from both companies. We can also help you run highly available and supported SQL Server deployments on top of Ubuntu Pro. Please contact us for more details.