Administering Aginity Team

Deploying Aginity Team through AWS Marketplace

This section will detail the steps to deploy the CloudFormation template for Aginity Team within AWS.

The deployment will take about 30 minutes, but configuration and testing could take up to an hour.

Pre-Requisites

An AWS Marketplace subscription is required for production use of Aginity Team. The one option for obtaining a subscription and/or license:

  • Bring your own license (BYOL) - Subscribe through the marketplace and apply an existing license. To get a 30 day evaluation license please fill out this form and it will email you a license. Aginity will be in contact with you during the evaluation period. You can also contact us directly at sales@aginity.com.

You must have an AWS account set-up. If you don’t, visit: https://aws.amazon.com/getting-started/

Create an IAM user or role. Your IAM user should have a policy that allows AWS CloudFormation actions. Do not use your root account to deploy the CloudFormation template. In addition to AWS CloudFormation actions, IAM users who create or delete stacks will also require additional permissions that depend on the stack template. This deployment requires permissions to all services listed in the following section.

Knowledge of the following AWS services:

  • Amazon Elastic Compute Cloud (Amazon EC2)
  • Amazon Relational Database Service (Amazon RDS)
  • Amazon Route 53
  • Amazon Virtual Private Cloud (Amazon VPC)
  • AWS CloudFormation

Note

  1. Account limit increases will not be required for this deployment. (More information on proper policy and permissions here: https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/using-iam-template.html)
  2. Individuals possessing the AWS Associate certifications should have a sufficient depth of knowledge

Architecture Overview

Aginity Team can be deployed in a single availability zone. The data flow and architecture components are the same as the ones highlighted below. This solution does not provide high availability or fault tolerance.

Post Install

Deployment Step by Step

  1. Log into AWS account with the IAM entity created in the prerequisites section with the required permissions to deploy the solution.
  2. Click the following link to deploy CloudFormation template(CFT) (deploys in us-east-1):
  3. In ‘Step 1 - Create Stack’, press the ‘Next’ button.
  4. In ‘Step 2 - Specify stack details’, fill out and adjust CloudFormation parameters depending on requirements.
  5. Press the ‘Next’ button.
  6. In ‘Step 3 - Configure stack options’, enter and adjust optional tags, permissions, and advanced options.
  7. Press the ‘Next’ button.
  8. Review your CloudFormation configurations.
  9. Press the ‘Create Stack’ button.
  10. Wait approximately 30 minutes for your CloudFormation template to deploy!
  11. You can verify your deployment has succeeded by looking for a ‘CREATE_COMPLETE’ status.
  12. If the status is ‘CREATE_FAILED’, see the troubleshooting section in this guide. If it succeeds, please use health checks.

Note

You will be prompted during the deployment of the CFT to enter a username and password for the Aginity Team administrator.

Security Considerations

Amazon Certificate Manager SSL/TLS Certificates

AWS Certificate Manager (ACM) is a service that lets you easily provision, manage, and deploy Secure Sockets Layer/Transport Layer Security (SSL/TLS) certificates for use with AWS services. SSL/TLS certificates provisioned through AWS Certificate Manager are free.

If you don’t already have an SSL/TLS certificate for your domain name, it is recommended that you request one using ACM. For more information about requesting an SSL/TLS certificate using ACM, please read the AWS Certificate Manager User Guide.

Certificate Expiration

Certificates must be monitored for expiration. Aginity does not provide an integrated process for monitoring certificate expiration, but AWS provides a CloudFormation template that can help setup an alarm. Please visit the following link for details.

Costs

You are responsible for the cost of AWS services used while running this deployment. The AWS costs will depend on Aginity Team EC2 instance sizing and are outlined in the table below. Use the guidelines in the sizing section to determine what instances are appropriate for your deployment. With that information, you can use the AWS Simple Monthly Calculator to obtain the current prices and a cost estimate. To calculate your resource cost, please visit: https://calculator.s3.amazonaws.com/index.html

Note

Storage and data transfer are not included as these vary depending on configuration. Please consult AWS Pricing for the latest information.

Sizing

The following table outlines recommendations for EC2 instance size. If your team has more than 40 SQL analysts, please contact Aginity for additional sizing guidance. Please refer to the Costs section for pricing.

Post Install

Troubleshooting

I cannot “Create stack” in CloudFormation.

Please check that you have the appropriate permissions to “Create Stack”. Contact your AWS account admin for permissions, or AWS Support if you continue to have issues.

My Aginity Team AMI is throwing errors.

Please check that you have provided a valid license.

Everything is on fire.

Please contact AWS Support for AWS specific problems, or contact Aginity if you are having problems with the AMI!

See also

Backup

We highly recommend you do daily back-ups and snapshots to ensure minimal loss and downtime for your application. Please review the section in the documentation on backup and recovery.

Emergency Maintenance

Our knowledge base has the most comprehensive details about general product troubleshooting. It is accessible from our support site, and should be the first stop for any technical questions.

Clean Up

  1. Follow the AWS CloudFormation Delete documentation to delete the resources deployed by this document
  2. By default, EBS snapshots are retained for recovery purposes. If you wish to delete these, follow the EBS process to delete unwanted backups
  3. Delete any other resources that you manually created to integrate or assist with the deployment

Installing Aginity Team on Linux

Installing team consists of accessing and setting up an environment based on an Ubuntu O/S or Red Hat Enterprise Linux (RHEL). You can either do this on an existing Ubuntu Server or set up a Virtual Machine that will host the shared catalog database and Team application server.

If you have not yet done so download the software from here.

Once you register and download the software you can begin the installation process. Upon registration you the software will automatically download. The software should be titled aginity-team-<version#>-amd64.deb.

Pre-Requisites and Getting to a Server

Team is installed on an Ubuntu or RHEL Server or Virtual Machine environment. The machine Ubuntu or RHEL is installed on must meet the following requirements

  1. Cloud, data center or on-premises
  2. Minimum 200 GB of disk workspace
  3. 4 CPU x 16GB RAM
  4. Running Ubuntu 18.04.2 LTS (Bionic Beaver) or RHEL version 8
  5. Must have network access to databases being connected to
  6. HTTPS certificate (company provided or self-signed)

Desktop requirements

  1. Chrome browser
  2. HTTPS access to the Team Server

Note

If you are trying to install an evaluation of Aginity Team or just getting started there are several options for accessing a server environment in which to install Aginity Team server.

  1. You can ask your IT representative who is responsible for server infrastructure if there is a Ubuntu Linux server or VM available to use.
  2. You can install it locally for trial purposes using personal VMWare such as Parallels or an open source equivalent like Oracle VirtualBox.

If you would prefer, we are happy to schedule a free install for you. Please click on the link below to schedule that install.

Click here to schedule an install with an Aginity Consultant

The Installation Process

The following steps should be executed in order

Step 1: SSH into the Linux server as root or you can also sudo to root once in with another that has administrative rights.

Note

In our case for documentation we are working with a Parallels Ubuntu 18.04 VM and have an account called Parallels.

ssh <username>@<ip_address>

Step 2: SFTP the aginity-team-<version#>-amd64.deb file to the logged in users home directory. Below is a sample command to do that.

sftp <username>@<ip_address>
put aginity-team-<version#>-amd64.deb
exit

Step 3: Running the install

Issue the following command to initiate the install.

// on Ubuntu
sudo apt-get install aginity-team-<version#>-amd64.deb
// on RHEL
sudo yum install aginity-team-<version#>-amd64.rpm

This image below is what you should see after the install is complete.

Post Install

Note

We will generate an encryption key to ensure all information for connections and your catalog are encrypted. The installation will automatically generate and store that key on the /etc/aginity-team/service.conf file.

Step 4: Installing SSL certificate

You have a few options to add an SSL certificate to the application which are:

  1. configuring Aginity Team to use a keystore containing a certificate
  2. importing an SSL certificate to default Aginity Team’s keystore
  3. generating a self-signed certificate by executing a script provided as part of installation package

Configuring Aginity Team to use external keystore

Note

Aginity Team supports keystore only in format PKCS12.

To add a keystore containing a certificate to be used by the application, you should set values of SERVICE_KEYSTORE_PATH, KEYSTORE_PASSWORD and CERTIFICATE_ALIAS in /etc/aginity-team/service.conf. Read section General Configuration Parameters to find more information about the 3 parameters.

Importing SSL certificate to default Aginity Team keystore

If you already have an SSL certificate and an RSA private key used by the certificate then you may want to import the certificate to deafult Aginity Team’s keystore which is /etc/aginity-team/cacerts. Imported certificate has alias aginity_cert in the Aginity keystore. Aginity Team supports importing certificates in PEM, DER and PKCS12 formats.

Warning

We strongly recommend that you change password of Aginity Team’s keystore right after installation of the application. Default Aginity Team’s keystore password is changeit.

The procedure of changing keystore password is as follows:

  1. Change the store password:
# this will change Aginity keystore password
sudo /opt/aginity-team/jre/bin/keytool -storepasswd -keystore /etc/aginity-team/cacerts
Enter keystore password:  changeit
New keystore password:  *new password*
Re-enter new keystore password:  *new password*
  1. Set the value of KEYSTORE_PASSWORD in /etc/aginity-team/service.conf to new password.

To import SSL certificate run the following command:

# this will import certificate
sudo /opt/aginity-team/bin/import-ssl-certificate.sh

Generating self-signed certificate

The SSL certificate generation can be called using

# this will generate certificate
sudo /opt/aginity-team/bin/ssl-certificate-gen.sh

You will be asked for the following information

  1. First and last name
  2. Name of organization unit
  3. Name of organization
  4. Name of City or Locality
  5. Name of State or Province
  6. Name of Country code
  7. Finally when asked to “Enter key password for <aginity_cert> (RETURN if same as keystore password): ” press RETURN

Configuring Aginity Team Server

You can adapt Aginity Team Server to your needs by changing settings in /etc/aginity-team/service.conf and /etc/aginity-team/application.yml configuration files.

The service.conf file contains settings of a linux service running Aginity Team. It is in a plain text file format. Here are the parameters in the /etc/aginity-team/service.conf file:

General Configuration Parameters

SERVICE_KEYSTORE_PATH

The path to a keystore file in format PKCS12. Aginity Team generates this file when you run /opt/aginity-team/bin/ssl-certificate-gen.sh. You can use your own keystore file by setting a path to the file by replacing default one.

Default: /etc/aginity-team/cacerts

KEYSTORE_PASSWORD

A password to the keystore file.

Default: changeit

CERTIFICATE_ALIAS

A keystore alias.

Default: aginity_cert

JVM_MEMORY_LIMIT

The value of JVM maximum heap size. The number has to be an integer. You should specify its value using one of the letters “m” or “M” for MB, or “g” or “G” for GB. Examples: 1024m, 1024MB, 12g, 12GB.

Default: 1024m

AGINITY_ENCRYPTION_KEY

The value of encryption key which is used to encrypt H2 database used by the application. This value is generated by Aginity Team.

Note

We recommend that you copy the encryption key to a safe place as you may need it to recover Aginity Team Server using a database backup.

AGINITY_CONFIG_PATH

The path to the application configuration yml-file. Do not change its value.

Default: /etc/aginity-team/application.yml

DB_PATH

The path to a directory to deploy Aginity Team’s H2 database in.

Default: /var/lib/aginity-team/

Default: /var/lib/aginity-team

Configuring the YML Application File

Aginity Team Server can be customized through a number of settings of the /etc/aginity-team/application.yml file. It uses YAML format. Depending on your needs Aginity Team can be configured to use either embedded H2 database or PostgreSQL database server. You may want to change TCP port the server is accepting HTTP request on or integrate Aginity Team with external identity providers.

Note

YAML does not support tab characters for indentation. You should use spaces for that.

For the reason of backward compatibility, some settings in application.yml use parameter expansions. The basic form of parameter expansion is ${parameter:-}. The parameter is a key from /etc/aginity-team/service.conf. You can replace any ${parameter:-} with your custom value. There are default values for some parameters in the configuration file:

${parameter:-defaultValue}

If parameter is unset in /etc/aginity-team/service.conf, the expansion of defaultValue is substituted. Otherwise, the value of parameter is substituted.

Webserver Options

server Options

server:
  port: ${SERVICE_PORT:-8080}
  publicUrl: ${PUBLIC_URL:-'http://localhost:8080'}

server.port

TCP port Aginity Team uses to accept HTTP requests. You may want to setup Aginity Team to listen on 443 to simplify URL users should use to access Aginity Team. In this case, the server section looks like as follows:

Default: 8080

server:
  port: 443
  publicUrl: https://my.example.com

server.publicUrl

publicUrl is a URL of a server running Aginity Team. It is a public URL available outside your corporate network.

publicUrl has the following format {scheme}://{host}:{port} where {scheme} can be either http or https, {host} is hostname of the server running Aginity Team, {port} is TCP port Aginity Team is using.

Default: http://localhost:8080

Catalog Database Options

database Options

database:
# BEGIN H2 database configuration
  h2:
    dbFilePath: ${DB_PATH:-}
# END H2 database configuration
#
# BEGIN PostgreSQL configuration
#
# This section is commented out by default since only one type of database could be enabled
# to enable PostgreSQL, comment out the H2 database configuration section and uncomment the section below
#  postgres:
#    user: {user}
#    password: {password}
#    jdbcUrl: jdbc:postgresql://{host}:{port}/{database_name}
#    properties: # any properties specific to your JDBC driver:
#      ssl: true # connect using SSL. SSL support must be enabled on the database server side.
#      sslmode: require
#
# END PostgreSQL configuration

By default, Aginity Team installs embedded H2 database to store application data. Another option is to use a PostgreSQL database. In this case, you have to deploy PostgreSQL server and create a database by yourself. Here is an example of the database section with enabled PostgreSQL database:

database:
# BEGIN H2 database configuration
#  h2:
#    dbFilePath: ${DB_PATH:-}
# END H2 database configuration
#
# BEGIN PostgreSQL configuration
#
  postgres:
    user: db_user
    password: db_passowd
    jdbcUrl: jdbc:postgresql://my.example.com:5432/aginity_team_database
    properties:
      ssl: true
      sslmode: require
#
# END PostgreSQL configuration

Note

Only one type of database could be enabled. To enable PostgreSQL, comment out the H2 database configuration section and uncomment the PostgreSQL configuration section.

database.h2.dbFilePath

The path to a directory to deploy Aginity Team’s H2 database in.

database.postgres.user

The database user.

database.postgres.password

The database user’s password.

database.postgres.jdbcUrl

The connection URL.

jdbcUrl has the following format jdbc:postgresql://{host}:{port}/{database_name} where {host} is the IP address or the hostname of the database server, {port} is TCP port the database server is listening, {database_name} is the name of Aginity Team database created by you.

database.postgres.properies

This section may contain any number of additional connection parameters which PostgreSQL JDBC driver supports. You can read about the jdbc driver parameters at jdbc.postgresql.org

Logging Options

logging Options

logging:
    pathToLogFile: ${PATH_TO_LOG_FILE:-/var/log/aginity-team}
    maxFilesCount: ${MAX_FILES_COUNT:-100MB}
    maxFileSize: ${MAX_FILE_SIZE:-1}

logging.pathToLogFile

The path to a directory to store application log files in.

Default: /var/log/aginity-team

logging.maxFilesCount

The maximum number of the log files Aginity Team creates.

Default: 1

logging.maxFileSize

The maximum size of a log file. The number has to be an integer. You should specify its value using one of the letters ‘KB’, ‘MB’ or ‘GB’ after the number. Examples: 200KB, 35MB, 1GB.

Default: 100MB

Platform Options

This section is used to set up OAuth for the Snowflake databases. Please refer to specific Snowflake section in the documentation Enabling Snowflake SSO.

Security Configuration Options

userDirectory Options

This section allows you to integrate Aginity Team with your LDAP system to import users, sync user information and delegate user authentication to LDAP server.

userDirectory:
    name: "LDAP Server"
    enabled: true
    serverSettings:
        host: ldapserver.example.com
        port: 389
        bindDN: cn=admin,dc=example,dc=com
        bindPassword: admin_password
        encryption: PLAIN
        verifyCertificates: false
        tlsOptions:
            trustStorePath: /path/to/trustStore/file
            trustStorePassword: my_password
            trustStoreType: PKCS12
    connectionRule:
        connectTimeout: 0
        responseTimeout: 0
    userSchema:
        baseDN: ou=users,dc=example,dc=com
        userFilter:
        usernameAttribute: uid
        firstNameAttribute: givenName
        lastNameAttribute: sn
        emailAttribute: mail
        searchScope: SUB_TREE

userDirectory.name

A descriptive name of the LDAP connection.

userDirectory.enabled

Flag to indicate whether LDAP authentication is on or off.

userDirectory.serverSettings.host

The IP address or hostname of the LDAP server.

userDirectory.serverSettings.port

TCP port of the LDAP server.

userDirectory.serverSettings.bindDN

The distinguished name of the user that the application will use when connecting to the directory server. Example: cn=admin,dc=example,dc=com

userDirectory.serverSettings.bindPassword

The user password.

userDirectory.serverSettings.encryption

Type of connection encyption. Can be set to PLAIN, START_TLS or SIMPLE_TLS. START_TLS is preferred option. START_TLS begins as a plaintext connection over the standard LDAP port (389), and that connection is then upgraded to SSL/TLS.

userDirectory.serverSettings.verifyCertificates

If set to true, Aginity Team verifies the certificate used for SSLTLS connection to the LDAP server. A java truststore configured in userDirectory.serverSettings.tlsOptions is used to verify the certificate. If you use a self-signed certificate, you must import the certificate to the truststore before starting Aginity Team.

userDirectory.serverSettings.tlsOptions

Use this section to configure your java truststore that is used to verify the LDAP server certificate.

userDirectory.serverSettings.tlsOptions.trustStorePath

The path to the Java trust store where certificates are stored.

userDirectory.serverSettings.tlsOptions.trustStorePassword

The password to the java trust store.

userDirectory.serverSettings.tlsOptions.trustStoreType

The type of the trust store. The most common are JKS and PKCS12.

userDirectory.connectionRule.connectTimeout

Timeout in milliseconds for connection to LDAP server. 0 means no timeout.

userDirectory.connectionRule.responseTimeout

Timeout in milliseconds for response time from LDAP server. 0 means no timeout.

userDirectory.userSchema.baseDN

The root distinguished name (DN) to use when running queries against the directory server.

userDirectory.userSchema.userFilter

An LDAP search filter to be used when searching user objects from the LDAP server within the base DN. Example: “(&(objectCategory=Person)(sAMAccountName=*))”

userDirectory.userSchema.usernameAttribute

Defines which attributes on an LDAP user entry will be used as its username.

userDirectory.userSchema.firstNameAttribute

Defines which attributes on an LDAP user entry will be used as its first name.

userDirectory.userSchema.lastNameAttribute

Defines which attributes on an LDAP user entry will be used as its last name.

userDirectory.userSchema.emailAttribute

Defines which attributes on an LDAP user entry will be used as its email address.

userDirectory.userSchema.searchScope

Specify strategy to use when doing a search. Possible options: SUB_TREE (search request should be performed against the search base and all entries below), ONE (search request should be performed against entries that are immediate subordinates of the entry specified as the search base DN), BASE (search request should only be performed against the entry specified as the search base DN).

Starting Aginity Team Server

Prior to starting the Agnity Team server you must create the administrator username and password.

You can do this by running the team-create-superuser script in the /opt/aginity/bin/ directory on the server.

# this will create a user with the administrator role
sudo /opt/aginity-team/bin/team-create-super-user.sh

Step 5: You are now ready to start Aginity Team

We use the following commands to start Aginity Team in the background and with logging turned on.

sudo systemctl start aginity-team

You can check if Aginity Team is running by issuing this command

sudo systemctl status aginity-team

Accessing Aginity Team for the First Time

Step 6: Step 6: Open browser and navigate to https://<ip or team server name>:<server port>/login. You will see the following image

Opening Amp
  • Enter credentials you used to create the administrator to enter the application.

Step 7: Register and Apply License Key

When you open the application it will ask you to register the software and then you will be asked to apply the license key.

Opening Amp

If it does not ask for the license key you can click on Help -> Register again and the license key box should pop open as shown below.

Opening Amp

Note

It is imperative that all users be created with valid business email addresses.

Send the link to team to any new user along with their username/password combination.

Stopping Aginity Team Services

Log in as the team user and issue the following command

sudo systemctl stop aginity-team
# check to see if it is stopped Using
sudo systemctl status aginity-team

See also

Add Connections

Upgrading Aginity Team

Upgrading team consists of logging into the Team server, downloading the latest update and applying it from the command line.

For clarities sake when we refer to aginity-team-<version#>-amd64.deb we purposely leave version# generic. Each upgrade will have a different number associated with it.

Warning

It is important to take a backup of both your encryption key for the Team catalog and to do a Backing Up or Restoring Team Catalog before upgrading. This way if anything goes wrong you will not use your catalog. It is also recommended to do a manual export of your catalog from time to time.

You can get the encryption key using the command below.

sudo cat /etc/aginity-team/service.conf

Step 1: Logging into Team Server

SSH into the Team server as an administrator.

Note

Typically you will log in as an account other than root and then you will be using the sudo command

Step 2: Check to see if Team is Currently Running and then Shut it Down

Issue the command below to check the status of the Team service

sudo systemctl status aginity-team

If Team is running shut it down by issuing this command

sudo systemctl stop aginity-team

Note

It is always a good idea to run the status check again to make sure Team has been shut down.

Step 3: Download the Latest Version of Aginity Team

Using the wget command you can download the latest version as shown below.

wget http://repository.aginity.com.s3.amazonaws.com/AginityTeam/aginity-team-<version#>-amd64.deb

Step 4: Upgrading the Software

Run the upgrade process by issuing this command.

// on Ubuntu
sudo apt-get install aginity-team-<version#>-amd64.deb
// on RHEL
sudo yum install aginity-team-<version#>-amd64.deb

The package manager will determine if your configuration files are different from ones bringing with the new version of the application being installed and will offer you a few option to proceed with installation

Updating configuration files

You should always keep your currently-installed version and resolve any conflicts by comparing and merging the difference manually.

Step 5: Restart Aginity Team

Restart the server by issuing this command.

sudo systemctl start aginity-team

Backing Up or Restoring Team Catalog

We have provided two files listed and described below which will allow you to automate or manually backup and restore your Aginity Team catalog.

Backup Script

The backup script backup.sh will perform and compress a backup of the Aginity Team Catalog to the /tmp/ directory on your O/S. It will append the date to the end of the file created and should be named in this pattern. aginity_backup_%m_%d_%Y.zip

Note

Before running the backup.sh script you will need to edit it and provide the following variables.

  • DESTINATION ~ if you do not want to use the default /tmp/

  • TOKEN ~ this value is the encryption key for the database. To find out the value of it run this command.

    sudo cat /etc/aginity-team/service.conf
    
  • H2PATH ~ Unless you modified the installation directory from the default you should not need to change this.

  • DBPATH ~ Unless you modified the installation directory from the default you should not need to change this.

When running a backup the Aginity Team server process will be stopped then restarted when complete.

See also

If you want to schedule the backup and automate it we recommend scheduling using Crontab: Crontab Reference Guide

Restore Script

To restore to an instance of the backup follow these instructions.

You’ll need to set the variables within the restore.sh to match what is in the backup.sh.

First download the restore script restore.sh. Next you will provide the file name of the backup you want to restore in the

  • SOURCE variable.

Now run the script and it will automatically re-start Aginity Team when complete.