Forum Discussion
Copy Power BI data mart tables to onprem SQL server
I created a data mart within our power BI server space. I love the ability to leverage Power Query to develop tables needed for reporting purposes. As you know when the data mart is created in power,...
I just tried connecting to the datamart's SQL endpoint in SSMS's SQL Server Import and Export Wizard and was able to copy a table to an on-premises server. At the end of the wizard you can define an SSIS package for re-use. I used the Microsoft OLE DB Driver for SQL Server with AD - MFA authentication:
See:
https://www.sqlshack.com/how-to-copy-tables-from-one-database-to-another-in-sql-server/
You could try connecting with SQL Server Agent and report here whether it also works.
As far as I know you can't create SQL users in the datamart bypassing Power BI's user model. You could try creating a user principal in AAD that has access to the datamart and see if that works.