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?
