Connecting to Azure SQL database using DBI in R and ActiveDirectoryPassword authentication

user_1688265 0
Viewed 717

I want to connect to AzureSQL database using DBI in R. I want to manage this connection via AzureAD ActiveDirectoryPassword authentication. This requires me to state the AzureAD UID and Password.

I have verified that I can connect to this server using other authentication methods.

Running the code below results in an error :

DBI::dbConnect(
            odbc()
            , Driver = "SQL Server"
            , .connection_string = 
                    sprintf("server=%s;database=dbName;UID=%s;PWD=%s;Authentication=%s;"
                            , 'sqlservername.database.windows.net'
                            , 'justin@contoso.com'
                            , pwd # Let's just assume this is securely done for now
                            , 'ActiveDirectoryPassword'))

Expected result was to connect to the database

Actual result was the following:

Error: nanodbc/nanodbc.cpp:950: HY000: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open server "contoso.com" requested by the login.  The login failed.

I think the @ in the username is not behaving in the way I initially expected that it might. How do I make this connection work? - I think I just need to find the correct format to express (escape?) the username.

dbi azure-sql-database azure odbc r
1 Answers

This worked for me

DBI::dbConnect(
            odbc()
            , Driver = "SQL Server"
            , .connection_string = 
                    sprintf("server=%s;database=%s;UID=%s;PWD=%s;Authentication=%s;"
                            , 'sqlservername.database.windows.net'
                            , 'databasename'
                            , 'user@company.com'
                            , pwd # Let's just assume this is securely done for now
                            , 'ActiveDirectoryPassword'))
user_90207440
Related