Issue applying the column-level security for a warehouse to a specific user account

[pmscorca]

Hi,

I'm trying to apply the CLS for a warehouse to a specific user account (reader1).

I'm following this article: SQL granular permissions in Microsoft Fabric 

Well, as the first thing, I've executed this SQL script:

GRANT SELECT ON def.mytable
(
[column_1],
[column_2,
[column_to_hide]
) TO [reader1@___.com];

Then, I've granted the item permissions to the reader1 account in this way:

Using SQL Server Management Studio, considering the warehouse connection string, I obtain an error when I try to connect to the warehouse with the reader1 account:

Inside Fabric, when I open the warehouse and I try to run a SELECT * FROM mytable or SELECT column_to_hide FROM my table I can see normally the column that I wanted to hide.

Any suggests to me in order to apply rightly the column-level security to a warehouse, please? Many thanks

[frithjof_v]

@pmscorca In the SSMS login dialog, can you try to go to the Connection Properties and select the Connect to database there.

Then write the name of the Warehouse or Lakehouse.

It worked for me.

[v-huijiey-msft]

Hi @pmscorca ,

Thanks for the reply from frithjof_v .

Glad to hear that one of your issues has been resolved, and for your other issue, I ran a test.

When I click connect, I will enter the web landing page, and select the account with the same username in SSMS to log in.

At this point, you can return to the SSMS to see that the connection was successful.

Please check if your steps are different from mine, especially the login and username need to be the same.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[pmscorca]

Which is your role in the workspace?

[v-huijiey-msft]

Hi @pmscorca ,

At least Contributor is required.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[frithjof_v]

Interesting, so we cannot connect from SSMS without being Contributor or higher?

Then in reality, read-only users cannot connect from SSMS.

Also, I think Contributors can alter (increase) their own permissions, because they have CONTROL access:

https://learn.microsoft.com/en-us/fabric/data-warehouse/workspace-roles#workspace-roles

So I don't think adding CLS to a Contributor would be secure. Because they can alter the security themselves afterwards.

I haven't tested it, though.

[Srisakthi]

There are ways in which we can control access on changing the permissions to any object for a user.

For instance, below is the sample code for restricting any user on changing security policies applied.

DENY ALTER ANY SECURITY POLICY TO [xxxx.xxx@domain.com];
GO

[frithjof_v]

You should not give ReadData, because that gives access to all the data in the Warehouse's SQL Endpoint.

Instead, try sharing the warehouse while keeping all the Additional Permissions boxes unchecked (empty).

https://learn.microsoft.com/en-us/fabric/data-warehouse/share-warehouse-manage-permissions#fabric-se...

"If no additional permissions are selected - The shared recipient by default receives "Read" permission, which only allows the recipient to connect to the SQL analytics endpoint, the equivalent of CONNECT permissions in SQL Server. The shared recipient won't be able to query any table or view or execute any function or stored procedure unless they're provided access to objects within the Warehouse using T-SQL GRANT statement."

And then use T-SQL GRANT to only grant access to the required data.

[pmscorca]

Hi, thanks for your reply.

I've solved the CLS issue keeping all the Additional Permissions boxes unchecked.

It remains to understand because I cannot access to the warehouse by SSMS with the reader1 user account.

[frithjof_v]

Which authentication method are you using?

https://learn.microsoft.com/en-us/fabric/data-warehouse/connectivity#get-started-with-sql-server-man...

[pmscorca]

Microsoft Entra MFA

[frithjof_v]

Then I don't have any suggestion. Hopefully someone else can chime in with a suggestion regarding the login error.

Have you tried connecting from Power BI Desktop, or a Dataflow, using the SQL Server connector? Just to see if that works.