Token-based authentication to Azure SQL Server

[jeffjon12]

I have need to authenticate to Azure SQL Server using token based authentication. This is possible in other languages like Python because the ODBC packages like pyodbc support pre-connection attributes (mkleehammer/pyodbc#228). However, none of the R ODBC packages: RODBC (https://www.rdocumentation.org/packages/RODBC), ODBC (https://www.rdocumentation.org/packages/odbc), or r-dbi (https://www.rdocumentation.org/packages/DBI), support pre-connection attributes.

I can't use ActiveDirectoryPassword authentication because this doesn't work from Mac/Linux when the user is a guest of the tenant.

It'd be great if MS could provides means of token-based authentication to Azure SQL Server.

[jeffjon12]

I've worked around this for now by using Reticulate to call out to Python code which makes the DB connection and runs the query. However, that's a pretty poor experience for R users to need to have Python installed and configured properly for this scenario.

[hongooi73]

I think this belongs more in the odbc repo. Maybe @jimhester could comment.

[hongooi73]

Open feature request here: r-dbi/odbc#299

[olesendan]

@jeffjon12 I'm looking in using this in my workflow. How is your Work around holding up?
If I'm allready using reticulate in my workflow, how hard is it to implement this solution?

[jeffjon12]

Dependency management turned out to be too difficult with reticulate, so we ended up wrapping the R code with Python which performs the query, writes the results to a file, and then calls R.