Lakehouse Federation to Azure SQL fails: timeouts, login errors, missing tables and no pushdown

#Symptom

You created a Unity Catalog connection of type sqlserver and a foreign catalog on top of it. Now one of these happens:

• SHOW SCHEMAS IN sales_sql or a SELECT hangs, then fails with The TCP/IP connection to the host ... port 1433 has failed.
• The query fails with Login failed for user or Client with IP address '...' is not allowed to access the server.
• The foreign catalog opens, but schemas or tables you know exist are missing.
• Queries work but are slow. A simple filtered query pulls the whole table across the network.

Work through the sections in order. Most failures are network problems, then credential problems, and only after that pushdown problems.

#Quick triage

#Check 1: confirm the connection and who owns it

SHOW CONNECTIONS;
DESCRIBE CONNECTION sales_sql_conn;
DESCRIBE CATALOG EXTENDED sales_sql;

DESCRIBE CONNECTION shows the host, port and owner. A common mistake is a host such as myserver instead of the full myserver.database.windows.net. Also check that the foreign catalog's database option names the right database.

Warning

With user/password authentication, every Databricks user who can query the foreign catalog reaches SQL Server as that one login. SQL Server sees a single identity. Use a login with read-only rights on the objects you intend to expose, and do access control in Unity Catalog.

#Check 2: the network path from the compute

Federated queries go directly from the cluster or SQL warehouse to the database. Neither the Databricks control plane nor Unity Catalog is on the network path. So the right question is "can this compute reach port 1433?"

From a notebook on a classic cluster in the same workspace:

import socket
host, port = "myserver.database.windows.net", 1433
print(socket.gethostbyname(host))          # private IP = private endpoint DNS works
s = socket.create_connection((host, port), timeout=5)
print("TCP OK"); s.close()

• If DNS resolves to a public IP and you expected a private endpoint, the privatelink.database.windows.net private DNS zone isn't linked to the workspace VNet, or your custom DNS server doesn't forward to it.
• If the TCP connection times out, check the NSG, UDR and firewall rules on the workspace subnets. See Azure Databricks cluster can't reach a database or API.

Check the Azure SQL side:

az sql server show -g rg-data -n myserver --query "{public:publicNetworkAccess, fqdn:fullyQualifiedDomainName}"
az sql server firewall-rule list -g rg-data --server myserver -o table
az network private-endpoint-connection list --id $(az sql server show -g rg-data -n myserver --query id -o tsv) -o table

If public access is enabled and the compute leaves Azure through a NAT gateway or firewall with a stable IP, allow that IP:

az sql server firewall-rule create -g rg-data --server myserver \
  -n databricks-egress --start-ip-address 20.10.10.10 --end-ip-address 20.10.10.10

#Serverless compute and SQL warehouses

Serverless SQL warehouses and serverless notebooks don't run in your VNet. Your NSGs and your NAT IP don't apply to them. On Azure, the connector supports Private Link from serverless compute through a network connectivity configuration (NCC) private endpoint rule to the SQL server. You then approve the pending private endpoint connection on the SQL server. The setup is the same pattern as in Databricks serverless can't reach ADLS behind a firewall.

Tip

If a query works on a classic cluster but fails on a serverless warehouse, the network path is the cause. The credentials are fine.

#Managed Instance and on-premises SQL Server

Azure SQL Managed Instance uses port 1433 on its VNet-local endpoint, or 3342 on the public endpoint if you enabled it. Use the port that matches the host name you configured. For on-premises SQL Server, the compute needs a routed path over VPN or ExpressRoute and DNS that resolves the on-premises name.

#Check 3: credentials

Store the user and password in a secret scope rather than in the connection definition:

CREATE CONNECTION sales_sql_conn TYPE sqlserver
OPTIONS (
  host 'myserver.database.windows.net',
  port '1433',
  user secret('federation', 'sales-sql-user'),
  password secret('federation', 'sales-sql-password')
);

When the password rotates, update the secret, then update the connection. As the connection owner, run ALTER CONNECTION sales_sql_conn OPTIONS (...) with the same options, or drop and recreate the connection and foreign catalog in a maintenance window. Test the login outside Databricks with sqlcmd or SSMS against the same database named in the foreign catalog. With contained database users, a login that works in master can still fail in the target database.

For Microsoft Entra ID authentication, the connector supports OAuth U2M and M2M. You configure these in Catalog Explorer with a client ID, client secret and authorization endpoint. With M2M, the service principal needs a user in the database:

-- run in the Azure SQL database
CREATE USER [sp-databricks-federation] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [sp-databricks-federation];

Caution

For OAuth connections, the Databricks control plane must reach the Entra ID token endpoint. Allowing only the compute plane is not enough. If an egress firewall blocks login.microsoftonline.com, the connection fails even though port 1433 is reachable.

#Check 4: Unity Catalog grants

A user needs privileges on the foreign catalog. A grant on the connection alone isn't enough:

GRANT USE CATALOG ON CATALOG sales_sql TO `analysts`;
GRANT USE SCHEMA, SELECT ON SCHEMA sales_sql.dbo TO `analysts`;
-- only for people who create new foreign catalogs on this connection
GRANT CREATE FOREIGN CATALOG ON CONNECTION sales_sql_conn TO `data-platform-admins`;

To create a foreign catalog, a user needs CREATE CATALOG on the metastore, plus ownership of the connection or CREATE FOREIGN CATALOG on it. For more on grants, see the Unity Catalog grants cheat sheet.

#Check 5: missing tables and stale metadata

The foreign catalog shows only what the SQL login can see. Check this in SQL Server as that login:

SELECT s.name AS schema_name, t.name AS table_name
FROM sys.tables t JOIN sys.schemas s ON t.schema_id = s.schema_id
ORDER BY 1, 2;

If a table is missing from that list, grant SELECT on it (or on its schema) in SQL Server. If it is in the list but Databricks doesn't show it, or a column change isn't showing up, refresh the cached metadata:

REFRESH FOREIGN CATALOG sales_sql;
REFRESH FOREIGN SCHEMA sales_sql.dbo;
REFRESH FOREIGN TABLE sales_sql.dbo.orders;

Watch the type mappings. SQL Server datetime, datetime2 and smalldatetime map to TIMESTAMP by default. If values look shifted by hours, see Timestamps off by hours in Azure Databricks. geography, geometry and varbinary map to BINARY.

#Check 6: slow queries and pushdown

Run EXPLAIN FORMATTED to see what is sent to SQL Server:

EXPLAIN FORMATTED
SELECT customer_id, SUM(amount)
FROM sales_sql.dbo.orders
WHERE order_date >= '2026-10-01'
GROUP BY customer_id;

In the Scan JDBCRelation node, look at PushedFilters and the pushed aggregates. If PushedFilters is empty, SQL Server returns the whole table and Databricks does the filtering.

• Filters, projections, LIMIT, aggregates and sorting with a limit are pushed down. Some string and math functions are pushed down only inside filters.
• Window functions are not pushed down. Neither are expressions the connector can't translate.
• Join pushdown is limited to newer runtimes and is a preview feature. Names and availability vary by version, so check your build's EXPLAIN output.
• Index the filter columns on the SQL Server side. Pushdown only helps if SQL Server can run the pushed query efficiently.

If the same large table feeds dashboards or daily jobs, stop querying it live. Copy it on a schedule:

CREATE OR REPLACE TABLE main.bronze.orders_snapshot AS
SELECT * FROM sales_sql.dbo.orders WHERE order_date >= current_date() - INTERVAL 30 DAYS;

For the trade-offs, see Advisory: query in place or ingest?.

#Prevent it next time

• Document the network path for each compute type that queries the connection: classic clusters (subnet, NAT IP) and serverless (NCC private endpoint).
• Keep connection credentials in a secret scope and put password rotation in the runbook, including the ALTER CONNECTION step.
• Use a dedicated read-only SQL login or service principal per connection. Never use a personal account.
• After schema changes on the source, run REFRESH FOREIGN TABLE as part of the release.
• Add an EXPLAIN FORMATTED check to code review for any federated query that runs on a schedule.