r-dbi/odbc

Setting pre-connect connection attributes for Azure SQL Server connection

kjaanson opened this issue · 11 comments

kjaanson commented

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 commented

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 commented

This would be a very welcome feature. It is likely to require support from nanodbc first nanodbc/nanodbc#215.

hongooi73 commented

@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?

  • 👍1
jimhester commented

As mentioned in nanodbc/nanodbc#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.

  • 👍1
olesendan commented

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 commented

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 commented

Bump. This would be extremely helpful.

innir commented

Now that nanodbc seems to have support for it (see nanodbc/nanodbc@b0fa192), things might get going :-)

  • 👍2
detule commented

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

  • ❤️4
BaukjeStienstra commented

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 commented

@BaukjeStienstra: Due to the incredible work of @detule there is now actually a PR that implements this. #521

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

  • 👍2