Connect to Azure SQL - Token Based

user_10538011 0
Viewed 328

I've tried this over on the Rstudio forums and they've suggested i post here/azure support. Azure doesn't seem to be helping right now so if anybody has any suggestions that would be awesome thanks.

I am trying to connect our RStudio scripts to our Azure SQL instance using token based authentication (I.E. using a client ID and Secret). I've done quite a bit of research into this and it looks like this functionality is not possible with the standard drivers (ODBC, RODBC, JDBC), does anybody else here know different?

If not possible with these drivers, is there another method i can employ?

All help is greatly appreciated, this is giving me a headache!

Edit: Just to add to this, i am using the below connection string and odbc function:

connstr <- 'Driver={ODBC Driver 13 for SQL Server}; Server=tcp:[Your Server 
Name Here].database.windows.net,[Your Port Number Here]; Database=[The name 
of the database you are connecting to];Uid=[Your username];Pwd=[your 
password];Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;'

conn <- odbcDriverConnect(connstr)

I am hoping to remove the Uid and pwd and replace with something like this:

token <- create_token(
app = "yourdatabase.database.windows.net",
consumer_key = "An Azure KEY",
consumer_secret = "An Azure SECRET")

connstr <- 'Driver={ODBC Driver 13 for SQL Server}; Server=tcp:[Your Server 
Name Here].database.windows.net,[Your Port Number Here]; Database=[The name 
of the database you are connecting 
to];Token=token;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;'

conn <- odbcDriverConnect(connstr)`
azure-sql-database token azure r
1 Answers

This works for me to connect Microsoft SQL Server to R with multi-factor authentication (MFA). This can take a long time depending on your table sizes.

# packages
library(dplyr)
library(dbplyr)
library(odbc)

# connection
con <- dbConnect(odbc(),
                 Driver = "ODBC Driver 17 for SQL Server",
                 Server = "server.name.goes.here.net",
                 Database = "databaseName",
                 UID = rstudioapi::askForPassword("Database user"),
                 Authentication = "ActiveDirectoryInteractive",
                 Port = 1433)

# lazy loading from the server
tbl(con, from = in_schema('databaseName', 'tableName'))

# collect the lazy load as a tibble
collect(tbl(con, from = in_schema('databaseName', 'tableName')))
user_174194140
Related