2

I am trying to set up a PowerShell script to enable Always Encrypted on our Azure SQL Server databases. I am following this guide, which offers the following sample code:

# Import the SqlServer module
Import-Module "SqlServer"  

# Connect to your database
# Set the valid server name, database name and authentication keywords in the connection string
$serverName = "<Azure SQL server name>.database.windows.net"
$databaseName = "<database name>"
$connStr = "Server = " + $serverName + "; Database = " + $databaseName + "; Authentication = Active Directory Integrated"
$database = Get-SqlDatabase -ConnectionString $connStr

# List column master keys for the specified database.
Get-SqlColumnMasterKey -InputObject $database

I run into problems however with getting the database connection to work. From what it seems the Get-SqlDatabase command from the SqlServer module that is used does not seem to support login through MFA. Our company have enforced this type of login, so I can't seem to find a way around this.

When running Get-SqlDatabase -ConnectionString 'Server=myserver.database.windows.net; Database=myDB; Authentication=Active Directory Interactive;' I receive the following error:

Cannot find an authentication provider for 'ActiveDirectoryInteractive'.

Does SqlServer-module lack the ability to support MFA logins? Or am I missing something else?

Dale K
  • 25,246
  • 15
  • 42
  • 71
FinneVirta
  • 374
  • 1
  • 4
  • 14

1 Answers1

6

Get-SqlDatabase Cmdlet uses the System.Data.SqlClient wherein as per the documentation below values are supported in Authentication Keyword.

Valid values are:

  • Active Directory Integrated
  • Active Directory Password
  • Sql Password

Active Directory Integrated :To use This authentication mode can be, you need to federate the on-premise Active Directory Federation Services (ADFS) with Azure Active Directory in the cloud

Active Directory Password: authentication=ActiveDirectoryPassword can be used to connect to an Azure SQL Database/Synapse Analytics using an Azure AD user name and password.

Sql Password :Use authentication=SqlPassword to connect to a SQL Server using userName/user and password properties.

If you want to Authenticate to Sql Server through PowerShell with MFA you need to use Active Directory Interactive in authentication keyword which is not supported by system.data.sqlclient

Active Directory Interactive:authentication=ActiveDirectoryInteractive can be used to connect to an Azure SQL Database/Synapse Analytics using an interactive authentication flow (multi-factor authentication)

Please note that the System.Data.SqlClient driver, and consequently the Get-SqlDatabase cmdlet, does not support the full range of Azure AD authentication methods. If none of the supported methods works in your environment, using SQL authentication may be the only option.

VenkateshDodda
  • 4,723
  • 1
  • 3
  • 12
  • Alright, thanks! That was what I feared. So there seems to be no current way to use PowerShell to set up Always Encrypted that supports authentication through MFA? – FinneVirta Nov 16 '21 at 07:58
  • Yes, currently it is not based on the documentation as mentioned above – VenkateshDodda Nov 16 '21 at 08:03
  • 3
    I raised a documentation enhancement issue to stop anyone else having this issue. https://github.com/MicrosoftDocs/sql-docs/issues/7028 – Nick.Mc Nov 16 '21 at 08:29