Connect to Azure SQL - Token Based

rstudio
sql

#1

Hi All,

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!

Thanks.

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)


#2

David Smith, cloud developer advocate for Microsoft and long time R user, has written a really good blog post about this:


#3

Thanks for sending that through. I did have a look at this post during my search and no matter the method they all use username and password. That includes the UserDSN.

Unless im missing something here? This isn't token based auth.

Im using the Azure app registration facility to generate the ID and Key/secret if that is of any help.


#4

sorry about the false lead. My lack of familiarity with the nuances of Azure caused me to not realize what you were trying to do. You might have better luck on a Microsoft forum as this is very specific to Azure and not R/RStudio.