# Connect to the database

## Testing the database connection

After creating the database user with use of the [previous manual](https://developer.ultimo.net/connectors/bi-platform/create-database-user),  an email with the connection information, consisting of the server name, database name, and username, is sent to the email address you used in the creation of the database user. 

{% hint style="info" %}
Please make sure that when connecting to production environments, you are connecting to the failover server. 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.<br>

IFS Ultimo reserves the right to disable the database user if it is connected to the production server and our initial request to change it to the failover remains unanswered. Please ensure timely responses to avoid any interruptions in your server connections.\
Thank you for understanding
{% endhint %}

### SSMS

Open a new connection by clicking on connect in the Object Explorer.

![](https://4033746893-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MZmRVbbu75Ds8MiWx_F%2F-MlJy-h4zoRx8ETyYb6u%2F-MlK5iMTot6OUfYEmRMp%2Fimage.png?alt=media\&token=4768bdbe-e9a1-4398-9015-6ee92f788da3)

Fill in the server, login and password when you received an SQL Server account.

<figure><img src="https://4033746893-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MZmRVbbu75Ds8MiWx_F%2Fuploads%2FdXmKtcW1hZrvL7MvgAWr%2Fimage.png?alt=media&#x26;token=ac5ca47e-728a-40a0-87cf-82801ac98c39" alt=""><figcaption></figcaption></figure>

Or when you received an account with AD fill in the server name and user name:

<figure><img src="https://4033746893-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MZmRVbbu75Ds8MiWx_F%2Fuploads%2Fe5iTiJFsiKE2klZsOVeZ%2Fimage.png?alt=media&#x26;token=9e47eed8-1211-448c-8a60-5680dbb691a3" alt=""><figcaption></figcaption></figure>

Before connecting, click on the Options >> button and fill in the database.

![](https://4033746893-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MZmRVbbu75Ds8MiWx_F%2F-MlJy-h4zoRx8ETyYb6u%2F-MlK5pSwBxoVqUiIuqd_%2Fimage.png?alt=media\&token=81ba677e-7421-4fb6-a6bc-62b3ad4477e5)

Now click on Connect and see if the connection can be made.

### ODBC

Go to the System DSN tab and click on Add…

![](https://4033746893-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MZmRVbbu75Ds8MiWx_F%2F-MlJy-h4zoRx8ETyYb6u%2F-MlK6-LNWp1TaNoQ2TlD%2Fimage.png?alt=media\&token=c64ee2a7-ba44-48fa-bb1f-b08279ee4784)

Select the ODBC Driver 17 for SQL Server and Finish

![](https://4033746893-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MZmRVbbu75Ds8MiWx_F%2F-MlJy-h4zoRx8ETyYb6u%2F-MlK61ZMD9xhiWpOzP9z%2Fimage.png?alt=media\&token=45199f75-0cc7-4131-8d21-b702d7404187)

Fill in a name and the provided server name:

![](https://4033746893-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MZmRVbbu75Ds8MiWx_F%2F-MlJy-h4zoRx8ETyYb6u%2F-MlK631GnNeULT5PnMp7%2Fimage.png?alt=media\&token=acda56a3-a96b-44e1-b848-baeee3de8c06)

Select SQL Server Authentication and fill in the username and password.

![](https://4033746893-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MZmRVbbu75Ds8MiWx_F%2F-MlJy-h4zoRx8ETyYb6u%2F-MlK64nGnscs5wSckYsk%2Fimage.png?alt=media\&token=b6d0c19f-a1e6-4d40-a849-4a3c390d4235)

Check the Change the default database to box and fill in the database name in the next screen.

![](https://4033746893-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MZmRVbbu75Ds8MiWx_F%2F-MlJy-h4zoRx8ETyYb6u%2F-MlK68001oxbFhl_1fpM%2Fimage.png?alt=media\&token=8b841302-c1d6-4db1-9da9-80bc35edc963)

Continue by clicking Next and Finish. Test the database source and you are good to go!

![](https://4033746893-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MZmRVbbu75Ds8MiWx_F%2F-MlJy-h4zoRx8ETyYb6u%2F-MlK69Vr_a4HvasybjgS%2Fimage.png?alt=media\&token=38ef15ad-458f-4a3b-8bc1-a9c09c9bdfc9)

### **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 for your region as described below.

<h4 align="center"><em><strong>Development, Test and/or Acceptance environments</strong></em></h4>

<table data-header-hidden><thead><tr><th width="130">Region</th><th width="460">Server</th><th width="170">IP Addresses</th></tr></thead><tbody><tr><td><strong>Australia</strong></td><td>UltimoProductionAUS.Database.Windows.Net</td><td><p>13.70.112.32/29</p><p>20.53.46.128/27</p><p>40.79.160.32/29</p><p>40.79.168.32/29</p></td></tr><tr><td><strong>Europe</strong></td><td><p>UltimoProductionCloud.Database.Windows.Net</p><p>UltimoProductionEurope.Database.Windows.Net</p><p>UltimoProduction2Europe.Database.Windows.Net</p></td><td><p>13.69.112.168/29</p><p>20.61.99.192/27</p><p>52.236.184.32/29</p><p>104.40.169.32/29</p></td></tr><tr><td><strong>Germany</strong></td><td>UltimoProductionGermany.Database.Windows.Net</td><td><p>51.116.149.32/27</p><p>51.116.152.32/29</p><p>51.116.240.32/29</p><p>51.116.248.32/29</p></td></tr><tr><td><strong>India</strong></td><td>UltimoProductionIndia.Database.Windows.Net</td><td><p>40.78.192.32/29</p><p>40.78.193.32/29</p><p>52.172.113.96/27</p></td></tr><tr><td><strong>Sweden</strong></td><td>UltimoProductionSweden.Database.Windows.Net</td><td><p>51.12.46.32/27</p><p>51.12.96.32/29</p><p>51.12.224.32/29</p><p>51.12.232.32/29</p></td></tr><tr><td><strong>United Kingdom</strong></td><td>UltimoProductionUK.Database.Windows.Net</td><td><p>51.105.64.32/29</p><p>51.105.72.32/29</p><p>51.140.144.32/29</p><p>51.143.209.224/27</p></td></tr><tr><td><strong>USA</strong></td><td>UltimoProductionEastUS.Database.Windows.Net</td><td><p>20.42.65.64/29</p><p>20.42.73.0/29</p><p>20.62.132.160/27</p><p>52.168.116.64/29</p></td></tr></tbody></table>

&#x20;

<h4 align="center"><em><strong>Production environment</strong></em> </h4>

<table data-header-hidden><thead><tr><th width="130">Region</th><th width="480">Server</th><th width="170">IP Addresses</th></tr></thead><tbody><tr><td><strong>Australia</strong></td><td>UltimoProductionAUSFailover.Database.Windows.Net</td><td><p>4.199.88.48/29</p><p>13.77.49.32/29</p><p>104.46.179.160/27</p></td></tr><tr><td><strong>Europe</strong></td><td><p>UltimoProductionCloudFailover.Database.Windows.Net</p><p>UltimoProductionEuropeFailover.Database.Windows.Net</p><p>UltimoProduction2EuropeFailover.Database.Windows.Net</p></td><td><p>13.69.233.136/29</p><p>13.74.105.192/29</p><p>52.138.229.72/29</p><p>52.146.133.128/27</p></td></tr><tr><td><strong>Germany</strong></td><td>UltimoProductionGermanyFailover.Database.Windows.Net</td><td><p>51.116.54.96/27</p><p>51.116.57.32/29</p></td></tr><tr><td><strong>India</strong></td><td>UltimoProductionIndiaFailover.Database.Windows.Net</td><td><p>20.192.43.160/27</p><p>20.192.96.32/29</p><p>40.80.48.32/29</p><p>104.211.86.32/29</p></td></tr><tr><td><strong>Sweden</strong></td><td>UltimoProductionSwedenFailover.Database.Windows.Net</td><td><p>51.12.198.32/27</p><p>51.12.200.32/29</p><p>51.12.201.32/29</p></td></tr><tr><td><strong>United Kingdom</strong></td><td>UltimoProductionUKFailover.Database.Windows.Net</td><td><p>20.58.66.128/27</p><p>51.140.208.96/29</p><p>51.140.209.32/29</p></td></tr><tr><td><strong>USA</strong></td><td>UltimoProductionEastUSFailover.Database.Windows.Net</td><td><p>13.86.217.224/29</p><p>20.66.3.64/27</p><p>20.168.163.192/29</p></td></tr></tbody></table>

### 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](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/diagnostic-connection-for-database-administrators?view=sql-server-ver16#connecting-with-dac).

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>