Setting pre-connect connection attributes for Azure SQL Server connection

[kjaanson]

Connecting to Azure SQL database using access token via pre-connect connection attributes

Is it possible to set pre-connect attributes on a connection using r odbc? I want to use Active Directory access token to connect to Azure SQL Data Warehouse. Azure docs specifiy that the token needs to be given to the driver as a pre-connect attribute SQL_COPT_SS_ACCESS_TOKEN (https://docs.microsoft.com/en-us/sql/connect/odbc/using-azure-active-directory?view=sql-server-2017).

I know that it is doable in pyodbc but I have not seen any implementation or mention of pre-connect connection attributes in r-odbc docs.

[jimhester]

No, there is currently no support for specifying pre-connection attributes. Adding support for it would likely require a decent amount of work, it is unlikely to be done without an external PR.

[samssann]

This would be a very welcome feature. It is likely to require support from nanodbc first https://github.com/nanodbc/nanodbc/issues/215.

[hongooi73]

@jimhester I've received a number of requests for something like this. What is required to make this happen? Is there anything I can contribute?

[jimhester]

As mentioned in https://github.com/nanodbc/nanodbc/issues/215 we would have to modify nanodbc to support this, similar to what was done in pyodbc, and then expose an R interface for it in this odbc package.

[olesendan]

Hi everybody.

I'm one of persons, trying to implement this in my workflow. I would like to contribute aswell. I consider myself a newbie in software development, and only know R. But I am willing to learn an have some time to work on a solution.

[julieinsan]

Upvote and offer to contribute. We are dealing with this issue presently as the only means of connecting to Azure SQL DB to satisfy our security team.

[JoshRosenstein]

Bump. This would be extremely helpful.

[innir]

Now that nanodbc seems to have support for it (see https://github.com/nanodbc/nanodbc/commit/b0fa192eb7289bba5450f90ad57075dec3a6581c), things might get going :-)

[detule]

Yes / should have a solution that works for us here soon.

[BaukjeStienstra]

That would be very nice! But I do have a question about this enhancement of nanodbc: Is there also a need to modify the odbc/DBI R-packages to make it available in an R-environment? And if so, when will this be available?

[kummerer94]

@BaukjeStienstra: Due to the incredible work of @detule there is now actually a PR that implements this. https://github.com/r-dbi/odbc/pull/521

I can only say that I am very, very grateful for the work and effort that has gone into this.