CODEWITHSUNDEEP
razureactive-directoryazure-sql-database
HadoopAddict
HadoopAddict

Reputation: 225

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

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.

Upvotes: 3

Views: 4173

Answers (5)

José
José

Reputation: 51

This code works but a window appears:

library(DBI)
server <- "xxxxxxxx.database.windows.net"
database = "myDB"
con <- DBI::dbConnect(odbc::odbc(), 
                 UID = "[email protected]",
                 Driver="ODBC Driver 17 for SQL Server",
                 Server = server, Database = database,
                 Authentication = "ActiveDirectoryInteractive")

Upvotes: 1

daviddiviny
daviddiviny

Reputation: 111

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

Upvotes: 6

MirekS SQL PM
MirekS SQL PM

Reputation: 114

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.

Upvotes: 0

MirekS SQL PM
MirekS SQL PM

Reputation: 114

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?

Upvotes: 0

Marcelo
Marcelo

Reputation: 4282

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

Upvotes: 3

Related Questions