This page discusses administering Stardog's Business Intelligence (BI) Server. For more information on the BI Server's supported clients and their usage, please see the section on BI Tools and SQL Queries.
<details open markdown="block"> <summary> Page Contents </summary> - TOC </details>Stardog provides a facility to bridge the flexible schema of the graph data model to the traditional relational model required by business intelligence tools that use SQL. This enables seamless use of business intelligence and visualization tools such as Tableau and Power BI.
Using this feature requires creating a schema mapping from Stardog’s data model to a relational data model. This mapping will be used to generate a relational schema. After creating the schema, the full power of SQL becomes available to relational clients.
Authentication requirements dictate that users created in versions of Stardog prior to 7.0.2 will need to have their password reset before connecting through the BI server. This requirement is not applicable if LDAP authentication is configured.
To integrate with business intelligence applications, Stardog includes a BI Server that makes Stardog communicate like a fully SQL-compliant RDBMS. The BI Server can be configured to run inside Stardog using the following configuration options in stardog.properties:
| Property | Default | Description |
|---|---|---|
sql.server.enabled | false | Turns on the BI Server. Must be set to true to use this feature. |
sql.server.port | 5806 | Controls the TCP port which the SQL query endpoint listens on. |
sql.server.default.auth.plugin | caching_sha2_password | The default authentication plugin for MySQL protocol connections. Valid values are caching_sha2_password (SHA-256 based, more secure) or mysql_native_password (SHA-1 based, for legacy client compatibility). See the Authentication section for details. |
sql.server.auth.cache.expiration | 1h | Expiration time for cached SHA-256 password hashes used by caching_sha2_password authentication. Format: positive integer followed by time unit (h for hours, m for minutes, s for seconds, ms for milliseconds). Cache entries are also invalidated when a user's password changes. |
sql.server.commit.invalidates.schema | true | Controls when changes to the schema mappings are visible to new BI connections. If true, changes to the schema mappings will be visible to newly created connections. This can lead to unnecessary load if the mappings rarely change. If false, changes to the schema mappings will only affect the schema after the database is taken offline. In all cases, users with long running connections will not see schema changes until they reconnect. |
The schema mapping is stored in a named graph that can be configured separately for each database. The sql.schema.graph database option should be set to the IRI of the named graph that stores the schema. The default value is tag:stardog:api:sql:schema.
If the schema mapping is not manually added by the user and the database configuration option sql.schema.auto is set to true (the default value), a schema mapping will be automatically created and used. Read more about this in the BI Tools and SQL Queries section.
The BI Server supports two MySQL authentication plugins for client connections.
This is the default and recommended authentication method, using SHA-256 for password hashing. It provides two authentication paths:
Fast Authentication (cached): After a successful initial authentication, the server caches the user's SHA-256 password hash. Subsequent connections can authenticate quickly using this cached hash without requiring SSL or RSA encryption.
Full Authentication (first connection or cache expired): The first connection for a user, or connections after the cache expires (controlled by sql.server.auth.cache.expiration), requires secure password transmission via either:
If neither SSL nor RSA key exchange is available during full authentication, the connection will fail. Configure SSL on the server (see SSL Connections) or enable RSA key exchange on the client.
If SSL is not configured on the server, clients must enable RSA public key retrieval to complete full authentication. The server generates a new RSA key pair on startup and provides the public key to clients upon request.
| Client | Configuration |
|---|---|
| MySQL CLI | Use the --get-server-public-key flag |
| MySQL Connector/J (JDBC) | Add allowPublicKeyRetrieval=true to the connection URL |
| MySQL Connector/ODBC | Set ENABLE_GET_SERVER_PUBLIC_KEY=1 in DSN settings |
| MySQL Connector/NET | Add AllowPublicKeyRetrieval=True to the connection string |
Example connection strings:
# MySQL CLI
$ mysql -h localhost -P 5806 -u admin -p --get-server-public-key
# JDBC connection URL
jdbc:mysql://localhost:5806/mydb?allowPublicKeyRetrieval=true
RSA key exchange without SSL is less secure than using SSL because a man-in-the-middle attacker could potentially substitute their own public key. We recommend configuring SSL for production environments.
This authentication method uses SHA-1 for password hashing and does not require SSL or RSA encryption. Use this option for compatibility with older MySQL clients (before version 8.0) that do not support caching_sha2_password.
To use legacy authentication, add the following to stardog.properties:
sql.server.default.auth.plugin=mysql_native_password
The mysql_native_password plugin is less secure than caching_sha2_password. We recommend using SSL/TLS connections with caching_sha2_password for production environments.
The BI Server uses the same authentication mechanisms configured globally in the Stardog instance. This is generally transparent but may require special configuration in the MySQL client. MySQL's ODBC driver requires "cleartext" password authentication to be enabled when using LDAP authentication. This can be done by setting ENABLE_CLEARTEXT_PLUGIN=1 when editing the DSN file or by checking the "Enable Cleartext Authentication" option of the "Connection" tab in the GUI configuration.
The BI Server supports SSL/TLS connections. SSL is important for:
caching_sha2_password authentication, SSL/TLS provides secure password transmission during full authentication (first connection or after cache expiration). Without SSL, clients must use RSA key exchange.Configure SSL by providing a server key as documented in Configuring Stardog to use SSL. The BI Server reads SSL/TLS configuration from JVM arguments:
export STARDOG_SERVER_JAVA_ARGS="-Djavax.net.ssl.keyStore=/path/to/keystore.jks -Djavax.net.ssl.keyStorePassword=changeit"
When the server key is provided, the BI Server will advertise SSL/TLS capability to clients.
Clients can be configured to require SSL connections:
| Client | Configuration |
|---|---|
| MySQL Connector/ODBC | SSLMODE=REQUIRED in DSN settings |
| MySQL Connector/J (JDBC) | sslMode=REQUIRED in connection URL |
| MySQL Connector/NET | SslMode=Required in connection string |
For maximum security, we recommend enabling SSL on the server and configuring clients to require SSL connections.
To see the SPARQL queries generated by the BI server, add this element to the <Loggers> section of your log4j2.xml file and restart Stardog:
<Logger name="com.complexible.stardog.serf.sql.planner.SparqlEnumerator" level="DEBUG" additivity="false">
<AppenderRef ref="stardogAppender"/>
</Logger>
See the Logging section in the Server Administration chapter for more information on configuring your log4j2.xml file.