Connect to the database
Connecting to your Ultimo cloud database
Testing the database connection
After creating the database user with use of the previous manual, IFS Ultimo will provide you the connection information consisting of the server name, database name, username and password. For production environments, a failover server is used to relieve the load of the actual production server. The data is still real time! Testing can be done with for example SSMS or ODBC.
SSMS
Open a new connection by clicking on connect in the Object Explorer.
Fill in the server, login and password when you received an SQL Server account.
Or when you received an account with AD fill in the server name and user name:
Before connecting, click on the Options >> button and fill in the database.
Now click on Connect and see if the connection can be made.
ODBC
Go to the System DSN tab and click on Add…
Select the ODBC Driver 17 for SQL Server and Finish
Fill in a name and the provided server name:
Select SQL Server Authentication and fill in the username and password.
Check the Change the default database to box and fill in the database name in the next screen.
Continue by clicking Next and Finish. Test the database source and you are good to go!
Firewall
In some environments, the firewall on your network needs to be configured too. In the BI connection to your Azure database, a static IP address is not possible. See https://docs.microsoft.com/en-us/azure/azure-sql/database/connectivity-architecture
The best way is to allow (whitelist) the IP addresses and ranges as described below.
Test environment
UltimoProductionCloud.Database.Windows.Net UltimoProductionEurope.Database.Windows.Net UltimoProduction2Europe.Database.Windows.Net
| IP addresses | IP address subnets |
|---|---|
104.40.168.105 | 104.40.169.32/29 |
52.236.184.163 | 13.69.112.168/29 |
52.236.184.32/29 | |
20.61.99.192/27 |
UltimoProductionEastUS.Database.Windows.Net
| IP addresses | IP address subnets |
|---|---|
40.121.158.30 | 20.42.65.64/29 |
40.79.153.12 | 20.42.73.0/29 |
40.78.225.32 | 52.168.116.64/29 |
20.62.132.160/29 |
UltimoProductionGermany.Database.Windows.Net
| IP addresses | IP address subnets |
|---|---|
51.116.240.0 | 51.116.152.32/29 |
51.116.248.0 | 51.116.240.32/29 |
51.116.152.0 | 51.116.248.32/29 |
51.116.149.32/27 |
UltimoProductionSweden.Database.Windows.Net
| IP addresses | IP address subnets |
|---|---|
51.12.224.32/29 | |
51.12.232.32/29 |
UltimoProductionIndia.Database.Windows.Net
| IP addresses | IP address subnets |
|---|---|
104.211.224.146 | 40.78.192.32/29 |
40.78.193.32/29 | |
52.172.113.96/27 |
UltimoProductionUK.Database.Windows.Net
| IP addresses | IP address subnets |
|---|---|
51.140.184.11 | 51.105.64.32/29 |
51.105.64.0 | 51.105.72.32/29 |
51.140.144.36 | 51.140.144.32/29 |
51.105.72.32 | 51.143.209.224/27 |
Production environment
UltimoProductionCloudFailover.Database.Windows.Net UltimoProductionEuropeFailover.Database.Windows.Net UltimoProduction2EuropeFailover.Database.Windows.Net
| IP addresses | IP address subnets |
|---|---|
52.138.224.1 | 13.69.233.136/29 |
13.74.104.113 | 13.74.105.192/29 |
52.138.229.72/29 | |
52.146.133.128/27 |
UltimoProductionEastUSFailover.Database.Windows.Net
| IP addresses | IP address subnets |
|---|---|
104.42.238.205 | 13.86.217.224/29 |
13.86.216.196 | 20.168.163.192/29 |
20.66.3.64/27 |
UltimoProductionGermanyFailover.Database.Windows.Net
| IP addresses | IP address subnets |
|---|---|
51.116.56.0 | 51.116.57.32/29 |
51.116.54.96/27 |
UltimoProductionSwedenFailover.Database.Windows.Net
| IP addresses | IP address subnets |
|---|---|
51.12.200.32/29 | |
51.12.201.32/29 |
UltimoProductionIndiaFailover.Database.Windows.Net
| IP addresses | IP address subnets |
|---|---|
104.211.96.159 | 104.211.86.32/29 |
104.211.86.30 | 20.192.96.32/29 |
104.211.86.31 | 40.80.48.32/29 |
40.80.48.32 | 20.192.43.160/29 |
20.192.96.32 |
UltimoProductionUKFailover.Database.Windows.Net
| IP addresses | IP address subnets |
|---|---|
51.141.8.11 | 51.140.208.96/29 |
51.140.208.96 | 51.140.209.32/29 |
51.140.208.97 | 20.58.66.128/27 |
Security
Port 1433 needs to be allowed for all environments. If you want to use DAC, ports 1434 and 14000 – 14999 need to be allowed too. For more information on DAC, read this page.
If for some reason IP adresses and ranges are a problem, the alternative is to allow the DNS name(s) instead.
Ultimo uses for the connection TLS (Transport Layer Security) 1.2, the highest available level under Azure. For more information regarding TLS you can read https://en.wikipedia.org/wiki/Transport_Layer_Security
How to connect Power BI to the Ultimo database
Ask Ultimo to add the Power BI IP address(es) to the whitelist of your Ultimo database if you want the reports to be published to app.powerbi.com.
Open Power BI Desktop and at the popup window select Get data
Select Database and SQL Server database.
Press Connect.
Fill in the server and database as provided by Ultimo. Press Ok.
Select the Database tab. Fill in the provided user name and password. Select at Select which level to apply these settings to the second option containing the server and database name. Press Connect.
The navigator is opened and you can start creating reports.
Last updated
