Configuring ODBC drivers to communicate with SQL servers - Linux and R/Python

Purpose:

• Instructions to connect to Partners MS SQL servers from MGB Rstudio (rstudio.partners.org)

Requirements:

• You have to have access ready for the SQL server that you want to connect to.
• You need to know the database server name. You do not have to set the database name.

Instructions

In this example, we are trying to connect to phssql826.partners.org as user hp911 from R

1. Find out existing configurations:
   

   1. odbcinst -j

2. Confirm that odbc files are configured either system wide or in your home directory 
   
   1. Generally, odbcinst files will house whatever driver you'd like to use {Freetds,MySQL,PostgreSQL} and the path to the library
   2. odbc.ini files configure the databases you will connect to, and what driver to use.
   3. If needed, copy from elsewhere (see below), or create your own.
      1. http://www.unixodbc.org/odbcinst.html
      2. check out the manpage for odbcinst utility
3. Open any text editor and paste the following:
   
   

   [FreeTDS] Description=FreeTDS ODBC Driver Driver64=/usr/lib64/libtdsodbc.so.0 FileUsage=1 [phsedw] Driver = FreeTDS Server = phsedw.partners.org Port = 1433 TDS_Version = 8.0

4. Save as “.odbc.ini”. Do not forget the leading dot.
5. Open Partners Rstudio and upload this file to your home directory. 
6. Quit the current session and start new session
7. Let’s test in R console (rstudio.partners.org)
   
   1. first load and confirm config (try just user or system instead of all)
      

      >library(RODBC) >odbcDataSources(type = c("all", "user", "system"))

   2. try connecting
      
      

      >library(RODBC) >odbc <- odbcConnect("phsedw", "Partners\\hp911", .rs.api.askForPassword("password")) >dat <- sqlQuery(odbc, "## your query here ##")​

   3. if you run into issues,
      
      1. check documentation for rodbc
         https://www.rdocumentation.org/packages/RODBC/versions/1.3-19/topics/odbcConnect
      2. also consider documentation for the respective driver as they may have their own debugging or tracing implementations:
         https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-configuration-trace-unix.html
         https://www.postgresql.org/docs/7.2/odbc-config.html

from python you can do some confirmations

>>> import pyodbc >>> pyodbc.drivers() ['PostgreSQL', 'MySQL', 'FreeTDS', 'phsedw']