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 directory id]";


string AadInstance = "https://login.windows.net/{0}";
string ResourceId = "https://database.windows.net/";


var authenticationContext = new AuthenticationContext(string.Format(AadInstance, aadTenantId));
var clientCredential = new ClientCredential(clientId, LINQPad.Util.GetPassword("[the password to get]"));

var authenticationResult = authenticationContext.AcquireTokenAsync(ResourceId, clientCredential).Result;

authenticationResult.Dump();

using (var conn = new SqlConnection(sqlConnectionString))
{
    conn.AccessToken = authenticationResult.AccessToken;
    conn.Open();

    using (var cmd = new SqlCommand("SELECT SUSER_SNAME()", conn))
    {
        var result = cmd.ExecuteScalar();
        result.Dump();
    }
}


Notably, the SUSER_SNAME() bring back the username in the format clientid@tenantid (both guids). However this format doesn't work in SSMS.

EDIT 2



Interestingly if in the C# code I switch to the username / password in the connection string, I receive the same user realm error.
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.