Connecting a Microsoft SQL Server Database

CONTENTS

Home

Connect to SQL Server

Here is what you need to connect:

  • Read-only user (Username field)
  • Whitelist the bipp IP address if you are using a Direct Secure Connection mode.
  • Database connection information
  • Credentials

Create a SQL Server read-only User

bipp supports SQL Server authentication mode and recommends using a read-only user for connecting to your SQL Server databases.

  1. Launch Microsoft SQL Server Management Studio.

  2. Expand Security.

  3. Right-click on Logins and select New Login.

    MSSQL-CreateUser-1

  4. On the Login - New dialog:

    • Enter a Login name (bipp_db_user is used in these examples)
    • Select SQL Server authentication
    • Enter and confirm a secure Password
    • Select the Default database to use with bipp.

    MSSQL-CreateUser-2

  5. Open the User Mapping tab to access the Login Properties.

    • Check the box next to your specified Default database
    • Verify only public’** is selected
    • Click OK

    MSSQL-CreateUser-3

  6. Click New Query on the toolbar.

  7. Select the database you are connecting to bipp.

  8. Execute queries to GRANT the new user privileges on each of the database tables. For example:

    GRANT SELECT ON "SCHEMA_NAME"."TABLE_NAME" TO "bipp_db_user"
    
    • SCHEMA_NAME the schema where you are granting permissions.
    • TABLE_NAME the specific table where you are granting permissions.
    • bipp_db_user the read-only user name you created.

    If your tables are in the a single schema then you can grant SELECT privilege to all tables. For example:

    GRANT SELECT ON SCHEMA :: [dbo] TO bipp_db_user
    
  9. Execute the query to GRANT view definition on the *dbo schema. For example:

    GRANT VIEW DEFINITION ON SCHEMA :: [dbo] TO bipp_db_user
    

Use these credentials to connect bipp to your MS SQL Server database.

Whitelist the bipp IP address

If your SQL Server instance is behind the firewall, you need to whitelist the bipp IP Address 34.123.9.91 to allow bipp to connect to your database. Use Direct Secure Connection as the Connection type when you create the datasource.

Create a SQL Server Datasource in bipp

  1. Select Datasources from the Home Navigation pane to open the Datasources page.

    Datasource Page

  2. Click New Datasource on the Datasources page.

    New Datasource

  3. Enter a Name. For example, TestMSSQL.

  4. Select MSSQL for SQL Dialect for your database. bipp creates and enforces the rules for the selected dialect.

    Datasource SQL Server

  5. Enter the information for your SQL Server database instance:

    • Host: name or IP address of the database host.
    • Port: port on the host for the connection.
    • Username: user name for the connection. bipp recommends using a read-only user account.
    • Password: password for the user.
    • Database: name of the database for the connection.
    • Encryption: select enabled to encrypt the data sent between the client and the server, or disabled for no additional encryption beyond the login packet.
    • Connection: connection type
      • Direct Connection: database directly available over internet or your bipp platform is deployed on-premise in a private subnet.
        • UseTLS: enable Transport Layer Security for the connection. bipp recommends you select yes to use TLS.
        • SslCert: browse to the location of the PEM format file containing your X509 certificate. This certification identifies the SQL Server daemon to clients during TLS/SSL connections.
        • SslKey: browse to the location of your SSL key.
        • SslRootCert: browse to the location of your SSL Root certificate.
      • Direct Secure Connection: secured encrypted TCP connection if your database is behind a firewall. Whitelist the bipp IP address 34.123.9.91. TLS is not supported in this mode. The encrypted data is transmitted to prevent in-transit eavesdropping.
      • SSH Tunnel: encrypted connection using Datasource SQL Server SSH Port Forwarding. bipp supports both forward and reverse SSH tunnels. You can use the SshTunnelAuth to specify Key or Password for the authorization type. Here are the additional SSH fields:
        • SshTunnelHost: name or IP address of the SSH tunnel authentication host.
        • SshTunnelPort: port on the SSH tunnel authentication host for the connection.
        • SshTunnelUser: user name for SSH tunnel authentication.
        • Ssh TunnelAuth: select Key to use an SSH Private Key and optional SSH Passphrase, or select Password to use your SSH Password.
  6. Click Test to verify the datasource connectivity. If you have an error, fix the connection or credentials problem and click Test again.

  7. Click Create to add the datasource.

Congratulations! You have successfully created a SQL Server datasource in bipp.