3

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.

  • Please read this and this. – SqlWorldWide Aug 29 '17 at 15:34
  • Thanks @SqlWorldWide but it's an Azure SQL DB and Azure Active Directory so shouldn't have any connectivity problems involving a proxy. Notably I was able to add a user from active directory, but not a service principal - so likely due to it being a service principal. – Alex KeySmith Aug 29 '17 at 15:39
1

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.

  • Thanks MirekS is there any documentation you've come across for the supported scenarios for service principals? I also didn't have luck with connection strings. – Alex KeySmith Aug 31 '17 at 10:08
  • 1
    @AlexKeySmith I can't find any explicit documentation about this either but a Microsoft employee confirms what MirekS is saying here: blogs.msdn.microsoft.com/azuresqldbsupport/2018/05/10/… – mikebridge Jan 18 at 22:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.