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

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

  • Click OK to finish the setup
  • You should now see the PetroAI data tables under Linked Servers

  • With the servers linked, you can now copy data into PetroAI or setup scheduled tasks to move the data