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

2
votes

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.

4
razureactive-directoryazure-sql-database
Do you have the latest ODBC Driver? Also you need to install the Online Services Sign-in Assistant (go.microsoft.com/fwlink/?LinkId=234947) - Marcelo
yes I have the latest ODBC driver.Still not able to connect using Active Directory Authentication - HadoopAddict

4 Answers

4
votes

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")
2
votes

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
0
votes

Did you download the ADALSQL.DLL library https://www.microsoft.com/en-us/download/details.aspx?id=48742? required to connect to Azure AD. Also to double check the ODBC driver you use is 13.1 - correct?

0
votes

If the recommendation indicated above (the ADALSQL.dll download) will not solve your problem, please create a customer case for "Azure AD authentication with SQL DB" and report it to SQL customer service.