Connect SQL Server to your Azure data warehouse and Power BI
On-prem and cloud SQL Server
Yres makes a direct database connection to Microsoft SQL Server: host, port (1433 by default), database name and a SQL login with read access. If the server is on-premises or behind a firewall, the connection runs through a self-hosted integration runtime; if it is publicly reachable, the Azure Data Factory cloud runtime is enough. Yres reads the schemas, tables and columns, you choose per table what gets loaded, and the data lands, historized, in an Azure SQL database in your own tenant, ready for Power BI.
At a glance
- Connection
- Direct database connection: host (server\instance for a named instance), port, database name, user name and password
- Default port
- 1433
- Sign-in
- SQL login with user name and password, preferably a separate service account with read-only access
- Network
- On-premises or behind a firewall: self-hosted integration runtime. Publicly reachable: the Azure Data Factory cloud runtime
- Incremental loading
- Yes, on one or two change columns of the same data type. All seven load types are available
- History
- Changed rows get a new version; the previous version is kept (SCD2)
- Password
- Never kept in the Yres web app; it is written as a secret to the Azure Key Vault of your own environment, and the pipelines run in your own Azure tenant
What you arrange yourself
- 1Ask your database administrator for the host, port and database name, or take them from an existing JDBC or ODBC connection string.
- 2Have a separate SQL login created with read-only access to the database, rather than a personal or admin account.
- 3Is the server on-premises or behind a firewall? Then make sure a working, published self-hosted integration runtime is in place first.
- 4Add the SQL Server source in Yres, pick the right integration runtime and enter the connection details.
- 5Refresh the metadata and add the tables you want to load.
When you need this — and when you don't
A standalone connector is enough if…
If a single Power BI report reads from a single SQL Server database and you never need to look back in time, a direct connection from Power BI is enough.
Yres adds value if…
Yres earns its place once the SQL Server database of your ERP or line-of-business application sits behind the firewall and still has to land next to other sources in Azure, when you want a record of how rows change over time, or when large tables should only be loaded on changes.
Frequently asked questions: Microsoft SQL Server
Our SQL Server is on-premises. Can Yres reach it?
Yes. When you create the source you pick a self-hosted integration runtime instead of the cloud runtime. That runtime has to be installed and published before you add the source.
Does the connection work with a named instance or a non-default port?
Yes. For a named instance you can use the server\instance notation in the Host field, and you enter the port yourself. 1433 is only the default value.
Which account does Yres use to sign in to SQL Server?
A SQL login: user name and password. The advice is a separate service account with read access to that one database only, so the connection can never do more than read.
Our tables have a created date and a separate modified date. Can Yres load incrementally on both?
Yes. You can specify a second change column of the same data type. Yres looks at the higher of the two per row, so a row is picked up as soon as either column has moved past the previous load. If one of them is empty, the other one counts.
Full technical description in the knowledge base →·Last reviewed:
Data warehouse automation that runs in your own Azure
Built for organisations on Azure and Power BI. Yres connects your sources, keeps the history and promotes changes in a controlled way, without manual work.