When connecting a .net application to a local database we have always used a trusted connection connection string.
We are porting our platform over to Azure with: Apps on Virtual Machines Azure Domain Services Sql Managed Instance
We have multiple Apps each run as a specific domain user and use trusted connection to the corresponding database on SQL Managed instance.
Attempting to use trusted connection from a domain joined virtual machine however (for example via SSMS results in the erro 'Azure Active Directory - Integrated' gives the error:
'Integrated Windows authentication supported only in federation flow.'
Looking at the Microsoft documentation it suggests it is possible to have trusted connections to SQL Managed Instance with 'seamless single sign-on' however the documentation only talks about an on premise domain connecting to Azure Active Directory.
Can anyone steer me in the right direction? are trusted connections possible in this setup? If not what is 'best practice' around connection strings to a SQL Managed Instance?
Thanks in advance
The best practice for Azure-native applications is to use Managed Identities to connect to Managed Instance. See eg: Connecting from your application to resources without handling credentials
Each Azure Virtual Machine, App Service App, Function App, etc can be provisioned with an AAD identity. Code running then can generate access tokens for resources (like Managed Instance) without ever touching a password or client secret.
Several Authentication strategies have been added to the SQL Client libraries to facilitate this. Or you can use Azure.Identity, or the local managed identity token endpoint library to fetch your own access token, and leverage that in the SQL Server client.
An alternative for lift-and-shift apps is to use Domain-joined VMs and use Windows Authentication for Managed Instance. This lets you continue to use traditional "Trusted Connection=true" from your VMs running in Azure to Managed Instance.