Connect to Azure SQL Database using SPN

Question

It's possible to connect to an azure sql server database xxxx.database.windows.net, using a SPN (already created one in my Azure subscription that I use successfully in ADF).

My goal is to avoid SQL Authentication (that I use at the moment with RODBC::odbcDriverConnect), and avoid 2FA, since R code will run automatically in remote machines.

Right now I use:

con <- RODBC::odbcDriverConnect('driver={SQL Server};server=XXX.database.windows.net;database=XXX;Uid=XXX;Pwd=XXX!')

Thanks community.

Already tried create a ODBC DSN, but couldn't connect using SPN option.

Answer 1

I can't speak for using R or the RODBC library but this is certainly possible in .NET. Although the following relates to .NET I'm hopeful that there should be enough in common for it to be of help to you, and you can port the second step to using R/RODBC:

1. Set the service principal as the administrator of the database. To to this follow these instructions: Provision Azure AD admin (SQL Database). This will be the same no matter what language/library you're using on the client application.
2. To connect to a database on the above SQL Server from a .NET client application you'd use code similar to that listed here in your client application code: Using Active Directory Service Principal authentication. Perhaps you can port this to R?