>Power BI Desktop provides a data source to SQL Server. However, when distributing that data (such as dashboards) over IIS, it is possible to have Power BI to benefit of a connection to SQL Server as well?

OK, here's the short answer.

When you create and deploy a dashboard, by default PBI creates an in-memory compressed dataset. So users are interacting with the in-memory dataset, not with the data in SQL Server. You can define refreshes for the data, which would point back to SQL Server (possibly through a data gateway)

You "can" set up what's called DirectQuery.....where the dashboard queries back to the sql server (or other data source) every time. However, that's not "normally" the usage pattern. More often than now, users interact with a PBI data model (for best performance), with the idea that data will be refreshed on some periodic schedule.