Authentication failure Lakeflow SQL Server Ingestion

[susanne]

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? 

[szymon_dybczak · Accepted solution]

Hi @susanne ,

Could you try to add following permission to your user?

GRANT CONNECT TO db_lakeflow_user

[susanne]

Hi @szymon_dybczak,

thanks a lot, that did the trick 🙂

[szymon_dybczak]

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

[rkhbo3003]

I am also facing the same issue. We have user id as service principal name however in sql log it shows applicationID that it cannot login . Setvice principal ( name) has highest privileges in sql db . howevrr same is working fine through jdbc