Azure AD Authentication

Service Principal Authentication

How to setup:

1. Add an App Registration in Azure Active Directory, eg. AuthAPP
2. From App Registration copy Application (client) ID.
3. In Certificates & secrets Add a new client secret (password).
4. In Azure SQL make sure your app has the right permission
   CREATE USER [AuthAPP] FROM EXTERNAL PROVIDER
EXEC sp_addrolemember 'dbmanager', 'AuthAPP'
5. In project settings.py, add Authentication=ActiveDirectoryServicePrincipal to extra_params

	DATABASES = {
	    "default": {
	        "ENGINE": "mssql",
	        "NAME": "default",
	        "USER": "Application (client) ID",
	        "PASSWORD": "Client secret",
	        "HOST": "example.database.windows.net",
	        "PORT": "1433",
	        "OPTIONS": {
	            "driver": "ODBC Driver 17 for SQL Server",
	            "extra_params": "Authentication=ActiveDirectoryServicePrincipal",
	        },
	    },
	}

Active Directory Interactive

How to setup:
(Interactive authentication only work on Windows, recommend using the latest version of ODBC 17 driver, some older versions may not be supported)

1. In project settings.py, add Authentication=ActiveDirectoryInteractive to extra_params
2. After running the Django project, a window will pop up asking the user to enter a password

	DATABASES = {
	    "default": {
	        "ENGINE": "mssql",
	        "NAME": "default",
	        "USER": "[email protected]",
	        "HOST": "example.database.windows.net",
	        "PORT": "1433",
	        "OPTIONS": {
	            "driver": "ODBC Driver 17 for SQL Server",
	            "extra_params": "Authentication=ActiveDirectoryInteractive",
	        },
	    },
}

ActiveDirectoryMsi

To use managed identity, add Authentication=ActiveDirectoryMsi to extra_params.

DATABASES = {
    "default": {
        "ENGINE": "mssql",
        "NAME": "your_db",
        "HOST": "database.windows.net",
        "PORT": "1433",
        "OPTIONS": {
            "driver": "ODBC Driver 17 for SQL Server",
            "extra_params": "Authentication=ActiveDirectoryMsi",
        },
    },
}

If you want to run unit test then the test database must be manually created and you need to pass in the --keepdb argument.

If you are getting a VIEW ANY COLUMN MASTER KEY DEFINITION permission denied in database error run EXEC sp_addrolemember N'db_owner', N'<Name>', replacing <Name> with the name of the VM in system-assigned managed identity or the name of the managed identity in user-assigned managed identity.