Unity Catalog - Lakehouse Federation: Permission to read data from foreign catalogs

[Wojciech_BUK]

I have seup connection "SQL-SV-conn" to SQL Server and based on that connection I have created foreign catalog "FC-SQL-SV".

I have granted All permission on CATALOG to developers:

• Use Catalog
• Use Schema
• Select

But they can not query table (e.g. by running SELECT * FROM FC-SQL-SV.my_schema.my_table statement ) and they got this error:

PERMISSION_DENIED: User is not an owner of Connection 'SQL-SV-conn'.

Is this a bug ?
Why users need Ownership over CONNCETION, this is not secure at all as I don't want End User to manage my connection.

[Kaniz]

Hi @Wojciech_BUK, You may need to check the user account under which the service is running. By default, it may run as a Local System, trying to pass the machine name through as the login. You can try changing this to a least-privileged domain user or your account and then granting that u....

• You may need to ensure the SQL Server has been configured to allow remote connections using TCP/IP protocol. You can do this by opening SQL Server Configuration Manager, clicking on SQL Server Services, locati....
• You may need to ensure that your connection string is correct and matches your server name, database name, user ID, password, and network library. For example, if you use TCP/IP protocol, you may need to add “Network Library=dbmssocn” at the end o....
• You may need to ensure your user has access to modify database properties and create database permissions. You can do this by opening SQL Server Management Studio, locating Security->Logins under your databa....

I hope this helps you resolve your issue. If you have any further questions, please feel free to ask me. 😊

[Wojciech_BUK]

Hi Kaniz,

Sorry but this is not what i am looking for.
This is Azure SQL server and I can connect and read data from it when I have ACL on both Unity Catalog objects: 

• Foreign Catalog (select)
  and
• Connection (Owner)

In my post i mentioned that users can't query SQL Server using Foreign Catalog when they are granted only Select and Use permission on it.
Error states that the end user need also OWNER on CONNECTION that was created in Unity Catalog and if i grant it - they can read the data.

But in my option this is highly insecure.

Imagine if you have Table in Unity Catalog and you have to grant both "SELECT" on table for and user as well as ONWER on External Location and Creadentials -> then entire ACL won;t make any sense.

[Wojciech_BUK]

OK, I have found out the answer in below docummentation:

https://learn.microsoft.com/en-us/azure/databricks/query-federation/#limitations

• Single-user access mode is only available for users that own the connection.

So when I use e.g. Job Cluster that runs in single access mode, then princiapal running the job must be Owner of Connection, thats is sad 😞

[Kaniz]

I want to express my gratitude for your effort in selecting the most suitable solution. It's great to hear that your query has been successfully resolved. Thank you for your contribution.