Connect to Your Own Data
Setup Guide
Connect to Your Own Data
Everything runs in Power BI Service — your browser. This guide walks you through connecting your SYSPRO database in one sitting.
What you'll need
Database name
with read access to SYSPRO DB
Premium Per User
Choose your SYSPRO setup
The connection steps depend on where your SYSPRO SQL database is hosted. Not sure? Ask your IT team: "Is our SYSPRO database on Azure, hosted by our partner, or on our own server?"
Azure SQL (most common)
SYSPRO database hosted on Microsoft Azure SQL — cloud to cloud, simplest setup.
No gateway neededPartner / Hosted
SYSPRO hosted by your reseller or a managed service provider on their own servers.
Gateway likely neededOn-Premises
SYSPRO runs on a server in your own office or data centre managed by your IT team.
Gateway requiredConnect Power BI Service to Azure SQL
No gateway needed. Everything is done in your browser at app.powerbi.com.
Install the app from AppSource
Click the AppSource link Foresight SA provided (or find it on appsource.microsoft.com). Click Get it now and sign in with your Microsoft account. The app installs into your Power BI workspace automatically.
Enter your connection details
When prompted to connect your data, enter your Server name (e.g. yourserver.database.windows.net) and Database name. Click Next.
Sign in with your SQL credentials
Select Basic as the authentication method. Enter your SQL username and password. Set Privacy Level to Organizational. Click Sign in and connect.
Set up scheduled refresh
In your workspace, find the dataset → click ⋯ → Settings → Scheduled refresh → turn it On and choose your frequency (e.g. daily at 6am). Click Apply.
Connect to a partner-hosted SYSPRO server
The connection steps are the same as Option A, but you need to confirm a few things with your SYSPRO partner first.
Check with your partner before starting
Ask your SYSPRO partner or hosting provider:
- What is the exact server address or hostname to use?
- Is SQL port 1433 open for external connections?
- Do I need to connect via VPN first?
- Do you already run a Power BI On-Premises Data Gateway?
Install the app and connect
Once network access is confirmed and the gateway (if needed) is in place, follow Option A Steps 1–4 using the server address and credentials your partner provides. For gateway setup, see the Gateway section below.
Connect to an on-premises SYSPRO server
You must install the On-Premises Data Gateway before Power BI Service can reach your local SQL Server. Complete the Gateway section below first, then return here.
Install the app and enter connection details
Follow Option A Steps 1–3 using your on-premises server name and SQL credentials. When entering credentials, select Basic authentication and Organizational privacy level.
Link the dataset to your gateway
In Power BI Service, go to your dataset → ⋯ → Settings → Gateway connection. Select the gateway you installed and the SYSPRO data source you added to it. Click Apply.
Enable scheduled refresh
In the same Settings page → Scheduled refresh → turn it On, set frequency, click Apply.
Authentication quick reference
When Power BI Service asks how to sign in to your database, use the combination below that matches your setup. If in doubt, Basic + Organizational is correct for almost all SYSPRO deployments.
| Your setup | Authentication method | Privacy level | |
|---|---|---|---|
| Azure SQL — SQL login | Basic (username + password) | Organizational | Most common |
| Azure SQL — Azure Active Directory | OAuth2 (Microsoft account) | Organizational | |
| Partner hosted — SQL login | Basic (username + password) | Organizational | Most common |
| On-premises — SQL login | Basic (username + password) | Organizational | |
| On-premises — Windows domain | Windows | Organizational |
⚠️ Always set Privacy Level to Organizational — selecting None or Public may block data refresh in shared workspaces.
On-Premises Data Gateway
Required for Options B and C — lets Power BI Service securely reach a SQL Server that isn't in the cloud.
What it does
Secure encrypted bridge between Power BI Service (cloud) and your on-premises SQL Server. Data only flows outbound.
Where to install
Always-on Windows PC or server on the same network as SYSPRO. Not a laptop.
Who installs it
Your IT team or Power BI admin. Needs Windows admin rights and a Power BI Pro licence.
Cost
Free. No licence beyond Power BI Pro (which you already need).
Install in 4 steps
Download and install the gateway
Run the installer on the always-on machine. Choose On-premises data gateway (standard mode, not personal).
Sign in and register the gateway
Sign in with your Power BI Pro Microsoft account. Choose Register a new gateway and give it a name (e.g. SYSPRO-Gateway).
Add your SYSPRO data source
In Power BI Service → Settings ⚙ → Manage gateways → select your gateway → Add data source → choose SQL Server → enter your SYSPRO server, database, and SQL credentials.
Return to Option B or C above to complete the connection
The gateway is ready. Go back to your option's steps to link the dataset and set up scheduled refresh.
Common issues
❌ Can't connect — timeout or "unable to connect"
- Check the server name for typos — one wrong character will fail the connection
- For on-premises/hosted: confirm the gateway is installed, running, and showing Online in Power BI Service
- For Azure SQL: confirm the "Allow Azure services" firewall rule is turned on
- For hosted: confirm port 1433 is open and VPN is connected if required
❌ "Login failed for user"
- SQL credentials are case-sensitive — double check the username and password
- Confirm you selected Basic (not OAuth2 or Windows) for a SQL login account
- Ask your IT team to confirm the SQL user has db_datareader access on the SYSPRO database
❌ Scheduled refresh fails
- For on-premises/hosted: check the gateway machine is powered on and connected to the network
- Re-enter credentials under dataset → Settings → Data source credentials
- Check that Privacy Level is set to Organizational (not None or Public)
❌ Data looks wrong or fields are blank
- Confirm you connected to the correct SYSPRO company database (each company = a separate database)
- Your User Defined Mapping file must be configured correctly — the app uses it for account hierarchy. Contact Foresight SA if you need help with this.
📚 Further resources
Microsoft documentation and SYSPRO technical references for everything covered in this guide.
Power BI Service & Template Apps
Azure SQL & Cloud
Gateway & On-Premises
SYSPRO Technical Docs
Still stuck? We're here to help.
If you've followed this guide and can't get connected, reach out — we'll walk you through it.
Contact Foresight SA View the P&L App