Cannot connect to Azure SQL DataWarehouse from RStudio using Active Directory Authentication

Question

I am trying to connect to Azure SQL Datawarehouse using RStudio. The only Authentication that is setup on the warehouse is Active Directory Password Authentication. I tried using the below connection string.

connectionString="Data Source = abc.database.secure.windows.net; Authentication=Active Directory Password; Initial Catalog=dbo; UID='UserName'; PWD= ;

It fails with "neither DSN nor SERVER keywork supplied" . And I dont want to set up a DSN as I am building a front end app and it must be portable.

I tried RODBC too ..but couldn't get much help with Active Dir Authentication.

Answer 1

I finally got to the bottom of this after a few deadends. A key step is updating your ODBC driver to ODBC Driver 17. The following works using Azure Active Directory authentication, which is slightly different to a consumer key/consumer secret but may achieve the same outcome (i.e. authentication without a SQL password).

library(DBI)
server <- "yourserver.database.windows.net"
database = "database-name"
con <- DBI::dbConnect(odbc::odbc(), 
                 UID = rstudioapi::askForPassword("username"),
                 Driver="ODBC Driver 17 for SQL Server",
                 Server = server, Database = database,
                 Authentication = "ActiveDirectoryInteractive")

Answer 2

For integrated AD authentication the connection string should look like:

Driver={ODBC Driver 13 for SQL Server};Server=tcp:{full qualified server name},1433;Database={dbname};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;Authentication=ActiveDirectoryIntegrated

For AD passoword authentication:

Driver={ODBC Driver 13 for SQL Server};Server=tcp:{full qualified server name},1433;Database={dbname};Uid={your_user_name};Pwd={your_password_here};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;Authentication=ActiveDirectoryPassword