Authentication failure Lakeflow SQL Server Ingestion

susanne — Mon, 04 Aug 2025 12:05:54 GMT

Hi all 🙂

I am trying to create a Lakeflow Ingestion Pipeline for SQL Server, but I am running into the following authentication error when using my Databricks Database User for the connection:
Gateway is stopping. Authentication failure while obtaining the connection to the source database: Error while creating connection pool for catalog master. Login failed for user 'db_lakeflow_user'.
In order to test if there is a problem with my connection configuration I tried to use my Admin User for the connection. In this case everything works fine, so I am assuming there is something wrong with the permissions of the Database User 'db_lakeflow_user'.
I followed the Databricks Documentation in order to set the correct permissions for the database user (https://docs.databricks.com/aws/en/ingestion/lakeflow-connect/sql-server-privileges) and since I am accessing a Azure SQL Database I granted the following permissions on my source database:

-- On the Server (master DB)

--CREATE LOGIN db_lakeflow_user WITH PASSWORD = 'secret_password';

-- On the target database

CREATE USER db_lakeflow_user FOR LOGIN db_lakeflow_user;

GRANT SELECT ON object::sys.schemas TO db_lakeflow_user;

GRANT SELECT ON object::sys.tables TO db_lakeflow_user;

GRANT SELECT ON object::sys.columns TO db_lakeflow_user;

GRANT SELECT ON object::sys.key_constraints TO db_lakeflow_user;

GRANT SELECT ON object::sys.foreign_keys TO db_lakeflow_user;

GRANT SELECT ON object::sys.check_constraints TO db_lakeflow_user;

GRANT SELECT ON object::sys.default_constraints TO db_lakeflow_user;

GRANT SELECT ON object::sys.change_tracking_tables TO db_lakeflow_user;

GRANT SELECT ON object::sys.objects TO db_lakeflow_user;

GRANT SELECT ON object::sys.triggers TO db_lakeflow_user;

GRANT SELECT ON object::sys.indexes TO db_lakeflow_user;

GRANT SELECT ON object::sys.index_columns TO db_lakeflow_user;

GRANT SELECT ON object::sys.fulltext_index_columns TO db_lakeflow_user;

GRANT SELECT ON object::sys.fulltext_indexes TO db_lakeflow_user;

GRANT SELECT ON schema::SampleData TO db_lakeflow_user;

GRANT SELECT ON object::SampleData.Sales TO db_lakeflow_user;

GRANT SELECT ON object::SampleData.Users TO db_lakeflow_user;

Which permission is missing in order to establish a connection using the database user?

Re: Authentication failure Lakeflow SQL Server Ingestion

szymon_dybczak — Mon, 04 Aug 2025 12:16:47 GMT

Hi @susanne ,

Could you try to add following permission to your user?

GRANT CONNECT TO db_lakeflow_user

Re: Authentication failure Lakeflow SQL Server Ingestion

susanne — Wed, 06 Aug 2025 07:20:10 GMT

Hi @szymon_dybczak,

thanks a lot, that did the trick 🙂

Re: Authentication failure Lakeflow SQL Server Ingestion

szymon_dybczak — Wed, 06 Aug 2025 07:22:23 GMT

No problem @susanne , cool that it worked for you 🙂

topic Re: Authentication failure Lakeflow SQL Server Ingestion in Data Engineering

Authentication failure Lakeflow SQL Server Ingestion

Re: Authentication failure Lakeflow SQL Server Ingestion

Re: Authentication failure Lakeflow SQL Server Ingestion

Re: Authentication failure Lakeflow SQL Server Ingestion