Connecting to Microsoft SQL Server / Azure SQL Database¶
Rational BI uses the MSSQL JDBC driver to connect to remote SQL Server and compatible databases. You can connect to most databases that are wire-compatible with the SQL Server protocol, such as Azure SQL Database as well as hosted databases such as SQL Server in AWS.
Your connection details
You will need to provide several pieces of information. You may need to retrieve this information from your system administrator.
Enter the hostname of the remote server. Rational BI will connect to this host from the set of IPs listed in the right panel of the connection screen. You will need to make sure that any firewalls and access control systems in use are configured to allow connections from Rational BI from these IPs. IP ranges are noted in CIDR notation and may include more than one range.
Enter the port the database server is accepting connections on. If no port value is supplied, the default value of 1433 will be used.
You can specify the target instance of the database. This is an optional setting that appears after the ‘\’ following the host name of your SQL Server connection string. If not specified, Rational BI will connect without a specific instance specified.
Enter the desired database. The system will use this database to reverse engineer table and column information for the dataset metadata. Ensure that the database contains a limited number of tables since a long list may be difficult to navigate when building reports. Other areas of the database can still be accessed (if permissions allow) through programmatic cells.
Enter the username associated with the user you would like to connect as. Do not use your own username or a privileged user. Instead, create a dedicated user with minimal permissions required for your reporting and data access needs. See Security Considerations for more information about how to safely connect to a remote database.
Provide the password associated with the specified username.
Testing your configuration¶
You can test your configuration by pressing the Test Configuration button. Rational BI will attempt to make a connection to the remote database and will indicate if the connection was successful. If an error was reported, check that the supplied information is correct and that no access control lists, firewalls or gateway systems prevent a connection from the Rational BI servers. The right panel contains a list of IPs that should be allowed to connect to the database.
Saving your configuration¶
When the configuration is correct, press Save. The system will connect to the remote database and create the metadata schema with table and column information based on the tables and views visible to the configured user.
The dataset will be updated to reflect that the remote database is online, and the database connection is now ready for use.