Unable to connect using Azure AD Service Principal on SQL Server

I'm having trouble testing a connection to Azure SQL Server using SSMS with an active directory service principal.

I have an AD Admin account created, and have successfully added a colleague's AD user account, whom can connect via SSMS.

The service principal is a Web App / Api service principal with a key. And I am attempting to create a database contained user (understanding this has better future compatibly)

Thinking it could be the syntax for creating the user I have tried many variations, however only this syntax has worked:

CREATE USER [username] FROM EXTERNAL PROVIDER

(other variations say the service principle cannot be found or type is not supported).

I have the database name specified in SSMS.

I've tried "Active Directory - Universal with MFA support" and "Active Directory - Password".

The error I receive is "Could not discover user realm" which suggests it's not aware of the active directory tenant to use, however none of these user login formats seem to work (using the password version, I wouldn't expect them to in universal):

• user

• domain\user

• user@domain.com (FQDN)

• user_domain.com#EXT#@domain.onmicrosoft.com

• object id

• application id

However MSDN documentation suggests AD service principals are supported.

I am using azure active directory service principals for other things fine.

Any ideas?

EDIT

The plot thickens, after reading Connect to Azure SQL Database by Using Azure AD Authentication

I wondered if the service principal needed explicit permissions in AD, however modifying the code slightly so it wasn't doing impersonation, I was able to connect fine using c# (I've added the c# tag for stackexchange syntax highlighting)

```
var sqlConnectionString = "Data Source=tcp:[servername].database.windows.net,1433;Initial Catalog=[databasename];Persist Security Info=False;Connect Timeout=30;Encrypt=True;TrustServerCertificate=False";

string clientId = "[service principal application id]";
string aadTenantId ="[azure active d

Service principal is not supported in SSMS as well as in other SQL tools. Only application interface is supported for this type of AAD services.Your C# program works and this is the right way to proceed with testing.

StackExchange Database Administrators Q#184598, answer score: 2