2.1 Making a connection

---

R users can integrate SQL Server databases using the DBI and ODBC packages. The first step is to build a connection string. A connection string contains a set of key-value pairs. It tells R where the server is and what user credential to use. Different driver types need different connection string formats. Here are two common connection string configurations.

library(DBI)

myDriver <- 'SQL Server'
# Use . for a local connection, otherwise specify Server Machine Name or IP address
myServer <- '.\\snapman'
myDatabase <- 'Cab_Demo'


trusted_connection <- dbConnect(odbc::odbc(),Driver= myDriver,Server = myServer
                                ,Database = myDatabase,Trusted_Connection='yes')

myUserid <- 'Cab_App'
myPassword <- Sys.getenv('Cab_App_Password')

connection <- dbConnect(odbc::odbc(),Driver= myDriver,Server = myServer
                        ,Database = myDatabase,Uid = myUserid,Pwd = myPassword)

The trusted_connection uses my windows account instead of a username and password. I prefer windows logins over creating a SQL login because they are easier to manage. My AD system administrator takes care of securing windows credentials. If I use a SQL login, then I’m responsible for guarding and storing the credentials. The second connection string shows how to connect with a SQL login. I use SQL logins for non-windows clients or when connecting to a DB outside the domain. It’s good practice to avoid storing clear text passwords in the client code. So, I stored an environmental variable and accessed it with the Sys.getenv function.