Setup guide
Connect a template app to SYSPRO data.
Power BI connects to SYSPRO through the Microsoft on-premises data gateway: a free Windows app, installed once, that securely links the Power BI service to a hosted or on-premises SQL Server.
Step 1
Connect to the SYSPRO SQL database
The IT team or SYSPRO partner has the server and login details.
Power BI service (cloud)
Microsoft on-premises data gateway
On-premises/hosted
Steps 2 to 7 take place in the Power BI service (app.powerbi.com), not in Power BI Desktop.
The gateway goes on an always-on Windows PC or server that can reach the SQL Server.
1
Server details from the IT team or SYSPRO partnerThe SQL Server name, the database name and a SQL login with read access. For a hosted server, also whether a VPN is needed.
2
Active gatewayOpen Settings, then Manage connections and gateways, then On-premises data gateways, and make sure the gateway shows Online. If no gateway runs yet, see the gateway section below.
3
App installation from AppSourceOn the AppSource listing, select Get it now and sign in with a Microsoft work account. The app installs into the Power BI service with sample data.
4
ParametersIn the semantic model’s Settings, under Parameters, enter the SQL Server name, the database name and the licence key, plus the Excel file path if the app uses an Excel sheet. Then select Apply.
5
SQL Server on the gatewayIn the semantic model’s Settings, under Gateway connections, turn on Use an On-premises or VNet data gateway and select the active gateway. Next to the SqlServer data source, select Add to gateway, choose Basic, enter the SQL username and password, and set Privacy level to Organizational.
6
Licence check and Excel file on the gatewayAdd the other data sources listed under the gateway the same way. Licence check (Web): Anonymous, Privacy level Organizational, with Skip test connection ticked. Excel sheet, if the app uses one: a File connection for a local or network folder, or a SharePoint connection signed in with the Microsoft work account for SharePoint or OneDrive. For SharePoint or OneDrive, the gateway setting Allow user’s cloud data sources to refresh through this gateway cluster must be on.
7
Data sources and scheduled refreshUnder Gateway connections, select the matching connection in Maps to for each data source, then select Apply. Ignore the Cloud connections section when a gateway is used. Then turn on Scheduled refresh and set a time, for example daily at 06:00.
Done. The reports now refresh from live SYSPRO data through the gateway.
Step 2
Which sign-in to choose
| Login | Authentication | Privacy level |
|---|---|---|
| SQL login Most common | Basic (username and password) | Organizational |
| Windows domain login | Windows | Organizational |
Always choose Organizational. None or Public can block data refresh in shared workspaces. Microsoft: privacy levels
Required
The on-premises data gateway
A secure bridge between Power BI in the cloud and the SQL Server. Microsoft: what the gateway is
What it does
An encrypted bridge. Data only flows outbound.
Where it goes
An always-on Windows PC or server that can reach the SYSPRO SQL Server. Not a laptop.
Who installs it
The IT team or Power BI admin, with Windows admin rights and Power BI Pro.
Cost
Free. No licence beyond Power BI Pro.
1
Download and installRun the installer on the always-on machine and choose the standard on-premises data gateway, not personal mode.
2
Sign in and registerSign in with a Power BI Pro account, choose Register a new gateway and name it, for example SYSPRO-Gateway.
3
Confirm it is OnlineIn the Power BI service, open Settings, then Manage connections and gateways, and make sure the new gateway shows Online.
4
Finish the setupGo back to Step 1 to install the app, enter the parameters, add the connections and turn on scheduled refresh.
Watch: install the gateway for SYSPROOur video walks through the whole gateway setup.
Watch the video Troubleshooting
Common issues
Can’t connect: timeout or “unable to connect”
- Check the server name for typos. One wrong character fails the connection.
- Check the gateway is installed, running and shows Online in Power BI.
- Hosted: check port 1433 is open and the VPN is connected, if one is needed.
“Login failed for user”
- SQL logins are case-sensitive. Check the username and password.
- Choose Basic, not OAuth2 or Windows, for a SQL login.
- The IT team can give the SQL user db_datareader access to the SYSPRO database.
Scheduled refresh fails
- Check the gateway machine is on and connected.
- Enter the credentials again in the semantic model’s Settings, under Data source credentials.
- Check the Privacy level is Organizational.
Data looks wrong or fields are blank
- Check the connection points to the right SYSPRO company database. Each company is a separate database.
- Profit and Loss: the User Defined Mapping file must be set up correctly. Contact ForesightSA for help.
Further resources
Microsoft and SYSPRO documentation
The SYSPRO administrator or reseller has the SQL Server name, database and setup details.
Still stuck? We’re here to help.
Followed this guide and still can’t connect? Get in touch and ForesightSA will help get it connected.