This guide walks through the setup of Microsoft SQL Server Managment Studio (SSMS) in order to access PetroAI Cloud. SSMS is a common tool used across our clients who store much of their internal data on MS SQL servers. PetroAI Cloud uses MySQL as the staging database in our calculation pipeline. While many off the shelf tools, such as DBeaver, support both MS SQL and MySQL, SSMS requires a few extra steps. This guide assumes you already have a MS SQL server and SSMS setup and working.

How-to Use SQL Server Management Studio (SSMS) to Connect to PetroAI Cloud

This guide goes through the following steps to configure your SQL server and SSMS:

Step 1: Download and install the MySQL ODBC driver
Step 2: Create DSN using the MySQL ODBC driver
Step 3: Create a linked server in SSMS

Step 1: Download and install the MySQL ODBC driver

The MySQL ODBC driver needs to be installed on the SQL server
The driver can be downloaded here: MySQL :: Download Connector/ODBC

Be sure to download the ODBC driver (red box) and not the MySQL Web Community Installer.

When the download finishes, open the setup wizard to install the driver
Select the correct option in the Config Type drop down, the screenshot below is for example purposes only

Ensure the driver is installed on the same machine that hosts the database.

Continue through the wizard until installation is complete

Step 2: Create DSN using MySQL ODBC driver

Open the Windows control panel
- Open the Start menu
- Search "control panel"
Search the control panel for "Administrative Tools" and open the ODBC Data Source Administrator

Click on the System DSN tab
Click on Add...
Select the MySQL driver from the prepopulated list
Fill in the fields for the Data Source Name and Description
Use the TCP/IP Server option and past the PetroAI connection string into the empty field
The Port should be 3306

Click Ok
The new Data Source should be visible in the list

Step 3: Create a linked server in SSMS

Open SQL Server Managment Studio (SSMS) and confirm you are connected to the database which will serve as the source for data pushed to PetroAI
Expand the Server Objects tree and then right click on Linked Servers
Select New Linked Server...

Enter a name for the Linked server at the top of the window
Select Other data source as the Server type
Select Microsoft OLE DB Provider for ODBC Drivers for the provider
Enter a product name
Use the Data source name created in step 2 above
The Provider string is the connection string available from the Access tab of PetroAI Cloud
Location and Catalog can remain empty

Go to the security tab
You can map specific user credentials by adding them here, alternatively, you can use a single log in which is shown below
The Remote login and password are the Database username and password created in PetroAI Cloud