About Azure SQL Managed Connector

[srikanthp24]

Hi Community,

We are trying to configure a Databricks Lakeflow Connect managed SQL Server connector to ingest data from Azure SQL Database into Databricks.

We are using a contained SQL database user that has access only to the specific source database, with no access to the "master" database.

However, during connection validation, we are encountering the following error:

Authentication failure while obtaining the connection to the source database: Error while creating connection pool for catalog master. Login failed for user '<contained_db_user>'.

Our understanding:

It appears that the Lakeflow Connect managed SQL Server connector attempts to establish a connection to the master database during connection validation, even though the intended source is a specific Azure SQL database.

Since we are using a contained database user, authentication to "master" fails.

Our security requirement:

We need to restrict the service account to the source database only, without granting access to "master".

Questions for the community:

1. Has anyone successfully configured the Lakeflow Connect managed SQL Server connector using a contained database user with access restricted to a single Azure SQL database?

2. Is there a supported configuration to bypass master database authentication and connect directly to the specified source database?

3. Is access to "master" mandatory for this connector, even when ingesting from only one database?

4. Are there any recommended workarounds that preserve database-level security isolation while still using the managed Lakeflow Connect connector?

We would prefer to use the managed Lakeflow Connect connector rather than building a custom JDBC ingestion framework.

If you have any better approach suggest?
Any insights, experiences, or recommendations would be greatly appreciated.

Thanks!

[SirNicholas1st]

Hey,

Unfortunately I have no experience with the said issue, but could federated queries be an option? Though, its also a possibility that you would face the same authentication issue with them.

Basically you wouldn't ingest the data into Databricks first, but you would fire queries from dbx against the Azure SQL server. 

For reference one way we've used federated queries in medallion architecture is that we "replaced" the ingestion to bronze with them and then build managed tables from the data retrieved to silver and gold. Not actually the best practice but worked really well.

If that sounds something worth trying here are the docs for it https://docs.databricks.com/aws/en/query-federation/sql-server

Also as I don't know your use case, here are also docs for deciding if federation is something that would fit it: https://docs.databricks.com/aws/en/query-federation/database-federation#query-federation-vs-lakeflow...

[balajij8]

@srikanthp24 

You can provide the grant below to the user and check again if feasible

GRANT CONNECT TO contained_db_user

MS_DatabaseConnector membership in the master database is the first required privilege for Azure SQL Database sources. There is generally no bypass or option to skip master validation, and no configuration flag to connect directly to a single database. This is a hard requirement of the managed connector - it validates against master during connection pool creation, which is why the contained database user fails with error for contained_db_user. Ensure you run the commands given for Azure SQL as outlined here along with the grant connect given above.

You can create a server-level login in master, add it to MS_DatabaseConnector (grants connection capability only with no data access), and then grant SELECT on schemas, tables and the required views in that database. MS_DatabaseConnector role generally does not expose any data in master or other databases, so the security isolation requirement is preserved for data access. The login can connect to master (required by the connector) but can only read from your single target database.

If the security members prohibit it, you can check the approach of using JDBC connector within a Declarative Pipelines. JDBC connector allows specifying the database directly in the connection info and works with contained database users without requiring master access. It aligns with the existing medallion architecture using Spark Declarative Pipelines, you need to manage the logic rather than relying on the managed connector's built-in CDC and schema drift features. More details here

[Nirmata]

Adding to @balajij8’s answer: the reason a contained user can’t work is that ##MS_DatabaseConnector## is a server-level role, and server roles take logins from master, not database users. That’s the access the connector needs to reach master.

To keep it least-privilege, use a Microsoft Entra service principal rather than a SQL login:

sql

-- in master
CREATE LOGIN [lakeflow-sp] FROM EXTERNAL PROVIDER;
ALTER SERVER ROLE ##MS_DatabaseConnector## ADD MEMBER [lakeflow-sp];
-- in the source database only
CREATE USER [lakeflow-sp] FROM LOGIN [lakeflow-sp];
-- then the grants from the Lakeflow Connect SQL Server privileges doc

##MS_DatabaseConnector## lets the login connect to databases but grants no data access. It can only read what you grant in the source database, so you keep isolation between databases.

[Jen_Evangelista]

Hi srikanthp24, one distinction in the proposed workaround matters: permission to connect across databases and permission to read business tables are different security boundaries.

The current Azure Databricks privilege matrix explicitly requires master access for Azure SQL Database through ##MS_DatabaseConnector##. A service principal changes authentication, but does not remove that requirement. I would not present it as meeting a strict no-master-access policy.

Microsoft describes ##MS_DatabaseConnector## as allowing connection to any database, without granting table-read permissions. Scoped SELECT grants can preserve data-read restrictions, but that is broader than permission to connect to only one database.

If your policy prohibits master access entirely, I would review that documented matrix with the security and Databricks teams before changing the principal. I cannot point to a documented managed-connector option that skips the requirement.

References:
https://learn.microsoft.com/en-us/azure/databricks/ingestion/lakeflow-connect/sql-server-privileges
https://learn.microsoft.com/en-us/azure/azure-sql/database/security-server-roles?view=azuresql