Add Entra ID Support to connecting to SQL Server Instances #9851
Description
Summarize Functionality
My company is cloud-only and currently uses SQL Server VMs in Azure which are not Domain joined (Kerberos) but are joined to EntraID. We enable the ability add Entra ID Users and Groups to the OS Groups (for OS Management) and add Entra ID Users and Groups to the SQL Server instance as Logins. This allows us to connect to the SQL Server Instances using SSMS, VSCode, or other tools using Microsoft Entra with MFA. However, when reviewing DBATools connections I did not see any built-in way to use Entra ID SSO with MFA to make that connection.
After researching, I found that adding the following items to Connect-DBAInstance properly allows the Entra ID SSO with MFA. The user will be prompted by a web dialog that requests for them to select the Entra ID Account they are already logged in with and then the connection is made successfully.
Connect-DbaInstance -SqlInstance sql01 -AppendConnectionString 'Authentication=ActiveDirectoryInteractive;Integrated Security=False'
I would like to ensure this is somehow added to the documentation but figured it would be best to open a feature request to start a dialogue and about options. It might be better to look at the several ways to connect and possibly add a new parameter or a new value to existing parameters to use these settings automatically. Instead of having to add -AppendConnectionString 'Authentication=ActiveDirectoryInteractive;Integrated Security=False' to every single connection, having an option like -UseEntraID or something similar might be more useful and easier to use.
If this is added only to Connect-DBAInstance then the result of that would need to be piped to the other cmdlets such as Get-DBADatabase. I did not know if it might be more beneficial to add that as a parameter to all of the commands that might make a connection. It appears there are many ways to easily allow this but some options would touch a LOT of cmdlets.
I figured I would request here and then I can provide any other use cases, details, testing, etc. Anything that I can do to assist in the process would be great.
In my testing, I found that this also works for Azure SQL Database when connecting with an Entra ID Account with MFA. This should also work for Azure Managed Instance and Fabric SQL Database.
Is there a command that is similiar or close to what you are looking for?
Yes
Technical Details
Adding the following parameter to Connect-DBAInstance allows for EntraID Authentication with MFA.
-AppendConnectionString 'Authentication=ActiveDirectoryInteractive;Integrated Security=False'
While adding this to the documentation is helpful, adding a parmeter or other easily remembered item seems to make more sense. The above parameter works but is a bit much to remember or easily use.
Target Instance or database should be one of the following:
- Azure SQL VM with Entra ID Authentication enabled
- Azure SQL Database with Entra ID Authentication enabled
- Azure SQL Managed Instance with Entra ID Authentication enabled
- Fabric SQL Database with Entra ID Authentication enabled
User account connecting should be an Entra ID account with MFA enabled.
When connecting, a web browser dialog will pop up and allow the user to select their Entra ID Credential and use an MFA option. This is the standard process when connecting to any Azure Resource with Entra ID and MFA enabled and SSO.