Cloud Database Connections

Cloud databases are always hosted remotely, by a cloud provider. We have in this category the Snowflake and Amazon Redshift data warehouses. Followed by Microsoft Azure (similar to the on-premise Microsoft SQL Server), and Amazon Aurora, implemented with either MySQL or Postgres. They come with 7-day free trial for full access to anything. Then you get limited visual SQL query design and SQL generation, unless you pay for a small monthly subscription.

The process described here is for Data Xtractor, but it’s valid for all our products as well, with few exceptions: ER models are supported and generated only in Data Xtractor and Model Xtractor, while queries are not supported or generated in Model Xtractor.

How to Connect to a Snowflake Database

Snowflake is our newest addition. You must have a Snowflake account – you may even get one free trial account when you provide an email address (no credit card required). Your database must have been previously installed using Snowflake’s web UI.

1. Click on Connect > Cloud Databases > Snowflake. This will open the previous New Database Connection popup. Return here anytime with the Properties context menu command on your database node.
2. No need to install any data provider. A free open-sourcey Snowflake Provider for .NET was included in the setup.
3. Enter your Snowflake account number. Follow it by a dot and the region name, if necessary. DO NOT terminate the text with snowflakecomputing.com!
4. (1) Connect with your User Name and Password. Leave Save Password checked.
5. (2) Connect through SSO as an alternative, in which case you leave the User Name empty. This will automatically take a round-trip to your browser, to connect.
6. (3) Connect through OAuth as another alternative, in which case you fill-in the OAuth Token.
7. (4) Connect with Key-Pair as a last alternative. Follow the process as described on Snowflake’s page to generate local private and public key files. Then you pass in the Private Key File the path to your local file. The Password may contain a pass-phrase, if you opted for an encrypted connection.
8. Select eventually a Role. If your connection was right, you should see the list of Roles, and you may eventually select one (but the Role is optional).
9. Select the list entry with your Database name. It should appear in the Database list when expanded. Or type the database name manually. A database is always required.
10. Select one or more Schemas for this Database. The list may have all schema names, with total number of tables in each.
11. Select eventually a Warehouse. If your connection was right, you should see the list of Warehouse, and you may eventually select one (but the Warehouse is optional).
12. Leave the default values for Connection and Query Timeout. They are 15 ms by default, but increase them later if you use a slow connection, or if your queries do not have enough time to complete.
13. Leave Other Properties as they are. Or pass additional values.
14. Click on Test. This will check the number of tables and the Snowflake version, as in the example. Do not continue until your connection has been properly verified as successful.
15. Click on OK. Wait for metadata to be collected. The popup will close and a database connection node will be created, with table child nodes. In Data Xtractor or Model Xtractor, some ER model diagrams will also be generated, and one will be opened on screen.
16. Click on Queries > Demo Queries > add demo queries. If you are not in Model Xtractor and want to play with some built-in SQL samples, this will open a popup with the missing queries.
17. Double-click on any model or query node. This will open it up on the right pane.

How to Connect to an Amazon Redshift Database

Redshift was forked from PostgreSQL 8.0.2 years ago, and we support all its current versions, from 8.4 up. Your database must have been previously installed with a third-party tool. We recommend the free SQL Workbench.

1. Click on Connect > Cloud Databases > Amazon Redshift. This will open the previous New Database Connection popup. Return here anytime with the Properties context menu command on your database node.
2. No need to install any data provider. Npgsql Data Provider – which you may use as well to connect to the AWS (Amazon Web Services) for Amazon Redshift – was included in the setup.
3. Enter your database Host Name and Port Number. Copy and paste your remote AWS web address, without the port number. Default port number in Redshift is 5439.
4. Connect with your User Name and Password. Leave Save Password checked.
5. Connect through SSH if required. Follow the steps described separately below for SSH and come back to continue.
6. Select the list entry with your Database name. It should appear in the Database list when expanded, if previous parameters are correct. Or type the database name manually.
7. Select public Schema. The list may have all schema or database names, with total number of tables in each.
8. Leave the default values for Connection and Query Timeout. They are 15 ms by default, but increase them later if you use a slow connection, or if your queries do not have enough time to complete.
9. Leave Other Properties as they are. Or pass additional values, like pooling=False; (to disable connection pooling).
10. Click on Test. This will check the number of tables and server’s version, as in the example. Do not continue until your connection has been properly verified as successful.
11. Click on OK. Wait for metadata to be collected. The popup will close and a database connection node will be created, with table child nodes. In Data Xtractor or Model Xtractor, some ER model diagrams will also be generated, and one will be opened on screen.
12. Click on Queries > Demo Queries > add demo queries. If you are not in Model Xtractor and want to play with some built-in SQL samples, this will open a popup with the missing queries.
13. Double-click on any model or query node. This will open it up on the right pane.

With SSH (Secure Shell), you no longer connect directly to a remote database, but rather to the remote machine that hosts it. While in the Database Connection popup, you may need extra steps if you are forced to use this type of connection.

1. Switch to the SSH tab and check the Use SSH Tunneling option. The SSH Parameters section appears only when this option is checked.
2. Enter your Tunnel’s Host and Port Number. This is the remote machine you try to connect to, not the database.
3. Enter your Tunnel’s User Name and Password. Leave Save Password checked. This are as well credentials required to connect to the remote machine, not the database.
4. Enter an optional Identity File. This is the path of a file previously saved on your computer.
5. Enter your SSH Local Port. This is a local port number forwarded as the remote database connection port number. Leave empty to use the database default port number.
6. Click on Test. This will check your connectivity with these parameters.
7. Switch back to the Connection tab..Continue from step 5, as described there before.

How to Connect to a Microsoft Azure SQL Database

We support all versions (v12+). Your database must have been previously installed with a third-party tool in Azure. We recommend the free SSMS (SQL Server Management Studio), included in the SQL Server distribution kit.

1. Click on Connect > Cloud Databases > Microsoft Azure SQL Database. This will open the previous New Database Connection popup. Return here anytime with the Properties context menu command on your database node.
2. No need to install any data provider. System.Data.SqlClient driver – used as well for Azure – is already part of .NET.
3. Enter your database Host Name. Copy and paste the remote web address for the Azure cloud.
4. Connect with your User Name and Password. Leave Save Password checked.
5. Connect through SSH if required. Follow the steps described separately below for SSH and come back to continue.
6. Select the list entry with your Database name. It should appear in the Database list when expanded, if previous parameters are correct. Or type the database name manually.
7. Select the database schema, usually dbo. Leave it empty, if you have multiple schemas and you want to import all of them (as for the AdventureWorks and WideWorldImporters database samples). Or select one or more schemas from the list. The list may have all schema names, with total number of tables in each.
8. Leave the default values for Connection and Query Timeout. Increase these values later on if you use a slow connection, or if your queries do not have enough time to complete.
9. Leave Other Properties as they are. Or pass additional connection string values, when in need.
10. Click on Test. This will check the number of tables and server’s version, as in the example. Do not continue until your connection has been properly verified as successful.
11. Click on OK. Wait for metadata to be collected. The popup will close and a database connection node will be created, with table child nodes. In Data Xtractor or Model Xtractor, some ER model diagrams will also be generated, and one will be opened on screen.
12. Click on Queries > Demo Queries > add demo queries. If you are not in Model Xtractor and want to play with some built-in SQL samples, this will open a popup with the missing queries.
13. Double-click on any model or query node. This will open it up on the right pane.

How to Connect to an Amazon Aurora Database

Amazon Aurora was derived from MySQL v5.6, and we support all its versions. We support Amazon Aurora implemented with either MySQL or PostgreSQL. Your database must have been previously installed with a third-party tool. We recommend the free MySQL Workbench, included in the MySQL distribution kit.

1. Click on Connect > Cloud Databases > Amazon Aurora with MySQL, or Amazon Aurora with PostgreSQL. This will open the previous New Database Connection popup. Return here anytime with the Properties context menu command on your database node.
2. No need to install any data provider. MySQL Data Provider – which you may us as well to connect to the AWS (Amazon Web Services) for Amazon Aurora –  was included in the setup. We also included the Npgsql provider for PostgreSQL.
3. Enter your database Host Name and Port Number. Copy and paste your remote AWS web address, without the port number. Default port number in Aurora is also 3306, as for MySQL.
4. Connect with your User Name and Password. Leave Save Password checked.
5. Connect through SSH if required. Follow the steps described separately below for SSH and come back to continue.
6. Select the list entry with your Database name. It should appear in the Database list when expanded, if previous parameters are correct. Or type the database name manually.
7. Leave Schema(s) blank. Or select the same schema name as your database. The list may have all schema or database names, with total number of tables in each.
8. Leave the default values for Connection and Query Timeout. They are 15 ms by default, but increase them later if you use a slow connection, or if your queries do not have enough time to complete.
9. Leave Other Properties as they are. Or pass additional values, like sslmode=none; (if you connect through HTTP) or require.
10. Click on Test. This will check the number of tables and server’s version, as in the example. Do not continue until your connection has been properly verified as successful.
11. Click on OK. Wait for metadata to be collected. The popup will close and a database connection node will be created, with table child nodes. In Data Xtractor or Model Xtractor, some ER model diagrams will also be generated, and one will be opened on screen.
12. Click on Queries > Demo Queries > add demo queries. If you are not in Model Xtractor and want to play with some built-in SQL samples, this will open a popup with the missing queries.
13. Double-click on any model or query node. This will open it up on the right pane.

With SSH (Secure Shell), you no longer connect directly to a remote database, but rather to the remote machine that hosts it. While in the Database Connection popup, you may need extra steps if you are forced to use this type of connection.

1. Switch to the SSH tab and check the Use SSH Tunneling option. The SSH Parameters section appears only when this option is checked.
2. Enter your Tunnel’s Host and Port Number. This is the remote machine you try to connect to, not the database.
3. Enter your Tunnel’s User Name and Password. Leave Save Password checked. This are as well credentials required to connect to the remote machine, not the database.
4. Enter an optional Identity File. This is the path of a file previously saved on your computer.
5. Enter your SSH Local Port. This is a local port number forwarded as the remote database connection port number. Leave empty to use the database default port number.
6. Click on Test. This will check your connectivity with these parameters.
7. Switch back to the Connection tab..Continue from step 5, as described there before.