How do I create a Power BI report from SQL Server?

You need to use the SQLServer data connector available within Power BI for connecting to your database and then select required tables (or define a query). Then the columns of the table/query will be available within your report model using which you can define measures to be used inside report. If there are multiple tables involved, remember to relate them properly inside the model using the related columns so as to get correct figures in your report visuals. Once your report development is done, you can publish it to the service for sharing with your users.

Once the report is deployed you would require setting up a data gateway for refreshing the data from service. In the gateway you need to login using the same account that is used for logging in to your Power BI service site. And in the service make sure you go and map your datasources to be refreshed using the installed gateway