---
title: Connecting to SQL Server on shinyapps.io
author: steph
type: post
date: 2018-01-31T09:29:42+00:00
image: img/WorkingWithR.png
spacious_page_layout:
  - default_layout
categories:
  - R
tags:
  - freetds
  - sql server
  - r
  - shinyapps
---

If you use SQL Server (or Azure SQL DB) as your data store and you need to connect to the databasse from shinyapps.io, you're presently stuck with [FreeTDS](http://www.freetds.org/). If you have any control over infrastructure I cannot recommend highly enough the [actual ODBC Driver](https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server) on Linux for ease. Alas, [shinyapps.io](https://shinyapps.io) does not let you control the infrastructure. We have to make do with with FreeTDS and it can be pretty painful to get right.

Due to how obtuse the error messages you end up getting back from FreeTDS in your shiny app and the time to deploy an app, you might just want to cry a little. I know I did. Determined to succeed, here is my solution to getting a working database connection that you can also use to test you're doing it right. If you're on a particularly old version of SQL Server though, I can't guarantee this will work for you.

```r
library(DBI)
library(odbc)

server = "lockedata.westeurope.cloudapp.azure.com"
database = "datasci"
uid = "example"
pwd = "HBBFSE"

dbConnect(
  odbc(),
  Driver      = "FreeTDS",
  Database    = database,
  Uid         = uid,
  Pwd         = pwd,
  Server      = server,
  Port        = 1433,
  TDS_Version = 7.4
)
```

I then had some downstream problems with `dbReadTable()` where it yielded incorrect syntax error messages. As I was strapped for time so I simply moved over to using `dbGetQuery()` and writing a SELECT statement instead.

## Making it work anywhere
You're hopefully going to be using the ODBC driver where possible so you need to make your shiny application context aware so your code will run using the ODBC driver locally or FreeTDS when deployed on shinyapps. Here is a check flag and new function that you can use to seamlessly switch you app between database drivers based on whether it's running on a shiny server.

```r
is_local = Sys.getenv('SHINY_PORT') == ""

server = "lockedata.westeurope.cloudapp.azure.com"
database = "datasci"
uid = "example"
pwd = "HBBFSE"

dbConnector <- function(server, database, uid, pwd, 
               local=TRUE, port=1433, tds_version=7.4){
  if(local){
    DBI::dbConnect(odbc::odbc(), 
      driver = "ODBC Driver 13 for SQL Server",
      server = server, 
	  database = database, 
      uid = uid, 
	  pwd = pwd
	)
    
  }else{
    DBI::dbConnect(odbc::odbc(),
      Driver   = "FreeTDS",
      Database = database,
      Uid      = uid,
      Pwd      = pwd,
      Server   = server,
      Port     = port,
      TDS_Version=tds_version
    )
  }
  }

dbConn <- dbConnector(server, database, uid, pwd, is_local)
```