When setting up the data source in Power BI, it is recommended to connect to the underlying SQL Server database using the DirectQuery mode (see here). This ensures that after the write-back process, all visuals included in the report are automatically updated.
After selecting the SQL Server data source in Power BI Desktop, authentication is performed using the previously configured READ user. This user has read access to the relevant tables in the database and is used solely for data import into Power BI.
Logging in with the READ User:
- In Power BI Desktop, select the option “Import data from SQL Server”.
- Enter the connection details for the server and ensure that “DirectQuery” is selected as the connection mode.
- Use the designated login method for access (e.g., “Database” with the login credentials of a database user) and establish the connection.
