Databricks Unity Catalog SQLSERVER Connection - Unable to Change read_only from true to false

Question

Databricks Unity Catalog SQLSERVER Connection - Unable to Change read_only from true to false

Kishor kumar Reddy Akki 50

We are investigating a behavior with Unity Catalog SQL Server connections and would like to confirm whether this is expected product behavior or a potential issue.

Environment

Databricks CLI Version: v1.16.1
Connection Type: SQLSERVER
Unity Catalog Connection

Issue Description

We have an existing connection: *****

The connection is currently returned as: "read_only": true

We attempted to update the connection using the following payload:

{

"name": ********",

"comment": "Connection to COSMOS legacy database test",

"read_only": false,

"options": {

"host": "****",

"port": "1433",

"trustServerCertificate": "true",

"user": "PROXY_DBR_USR",

"password": "<redacted>"

},

"properties": {

"business_unit": "polaris",

"environment": "prd"

}

The API call completes successfully and returns the updated object; however, the connection continues to show: "read_only": true

Additional Testing

We also created a brand-new SQLSERVER connection with: "read_only": false

during creation. The connection was successfully created, but it was still returned as: "read_only": true

Questions

Is read_only a configurable property for Unity Catalog SQLSERVER connections?
Is the read_only attribute enforced by the Databricks backend for SQLSERVER/Lakehouse Federation connections?
Is changing read_only from true to false currently supported via:
- Databricks CLI
  - Connections REST API
  1. If supported, could you provide the expected request format or supported API version?
  2. If not supported, can you confirm whether SQLSERVER Unity Catalog connections are intentionally provisioned as read-only by design?

Observations

Create operation ignores read_only=false.
Patch operation completes successfully but preserves read_only=true.
No API errors are returned.
The connection state remains ACTIVE.

Any clarification on whether this is expected behavior, a documented limitation, or a known issue would be greatly appreciated.

Questions

Is read_only a configurable property for Unity Catalog SQLSERVER connections?
Is the read_only attribute enforced by the Databricks backend for SQLSERVER/Lakehouse Federation connections?
Is changing read_only from true to false currently supported via:
- Databricks CLI
  - Connections REST API
  1. If supported, could you provide the expected request format or supported API version?
  2. If not supported, can you confirm whether SQLSERVER Unity Catalog connections are intentionally provisioned as read-only by design?

Observations

Create operation ignores read_only=false.
Patch operation completes successfully but preserves read_only=true.
No API errors are returned.
The connection state remains ACTIVE.

Any clarification on whether this is expected behavior, a documented limitation, or a known issue would be greatly appreciated.

0 comments

1 answer

Your answer

Answer 1

Hello Kishor kumar Reddy Akki,

Welcome to Microsoft Q&A,

This is expected behavior, not a bug. SQLSERVER Unity Catalog connections used for Lakehouse Federation are read-only at the connection-type level, and the platform silently ignores read_only: false in create and patch requests for this type rather than erroring.

Is read_only configurable for SQLSERVER Unity Catalog connections? No. Microsoft's own connection-type comparison confirms query federation connections (which is what SQLSERVER, Snowflake, and similar external-database connections are) support Read: Yes, Write: No as an inherent capability, not a per-connection setting. See Unity Catalog connections.
Is it enforced by the backend? Yes. This lines up with your own testing: the create and patch calls both succeed without error, but the backend forces the field back to true because write was never an available capability for this connection type to begin with, it isn't validating and rejecting your input, it's just not honoring a field that doesn't apply.
Is there any supported way to flip it via CLI or REST API? No, this isn't a documented or supported operation for SQLSERVER/Lakehouse Federation connections, on any API version. This is confirmed as intentional design (CREATE FOREIGN CATALOG is explicitly documented as producing "a read-only mirror" of the source database), not a gap in the CLI or API.

If you need write access back to SQL Server from Databricks, the connection type itself needs to change. A JDBC connection type does support both read and write in Unity Catalog, unlike query federation. The tradeoff, confirmed by others hitting this same wall, is that JDBC writes fall outside Unity Catalog's governance and lineage tracking that the native SQLSERVER federation connection gives you for reads. There's no way to get write-back with full UC governance on this connection type today.

Please click Accept Answer and upvote if this helped.