5

I'm looking to use a service principal as the server admin, so it can be used in a release pipeline to create further active directory users.

I'm successfully able to make the service principal the server admin* and connect to the database using an Access token, so the service principal authentication works fine, which is great nice and an interesting challenge.

*EDIT: The AAD server admin was creating via the ARM template, notably if you tried creating manually via the portal, you cannot select service principals as admin users. But interestingly you can see the service principal name ok in the portal after creation using ARM instead. And you can login successfully with AAD auth, so it appears a server principal admin works fine.

However when creating further users, they never seem to be found with the error:

"Principal 'name here' could not be found at this time. Please try again later."

However when logged in as myself I am able to create other users fine.

Which leads me to think is SQL Server using some sort of Active Directory impersonation to validate the subsequent users / service principals exist?

If I were to grant my service principal access to the Graph API would that give it enough permissions? If so what is the minimum rights necessary?

I also see that Azure SQL Server has created a managed service identity in the background, so I wonder if that's also at play?

Also I saw on managed instances that they explicitly require a service principal with AAD rights to hook into AAD - which I presume is like an Azure SQL Server's MSI? But Azure SQL Server doesn't mention this with an AAD admin being enough.

I'd love to know how the underlying mechanism for the active directory hookup works as it could help me investigate my challenges further.

  • Hi @MarmiK thanks for the interest, yes it's an Azure AD admin, I've edited the question to add some details on how I did this. – Alex KeySmith Nov 27 '18 at 9:11
  • Thanks, I've changed the question to AAD to be clearer. I'm not doing anything with GraphAPI, however I'm wondering whether SQL Server is using GraphAPI in the background or something similar, which I naturally have access to as a user in AAD, but perhaps my service principal needs permissions granted to something in GraphAPI (or similar?) – Alex KeySmith Nov 28 '18 at 12:36
  • thanks for Edit, Please refer this link And [blog on Azure Graph API vs Microsoft Graph API ](dev.office.com/blogs/microsoft-graph-or-azure-ad-graph) try to use Microsoft Graph API if any future scope is related with that. – MarmiK Nov 28 '18 at 15:54
  • 1
    @AlexKeySmith: Did you ever find a solution to this? – Arne Bergene Fossaa Apr 4 at 7:48
  • Not really no, I had a hint that SQL Server converts the service principal's id to it's own internal representation, but I'm not sure what mechanism it uses to obtain the id. – Alex KeySmith Apr 4 at 10:24
1

Got an official answer from Microsoft today:

I have confirmed that creating AAD users and groups is currently NOT supported when using Service Principal accounts. This particular limitation is only specific to database contained users.

  • That's very interesting it's in regard to data based contained users. Would a service principal which is not a contained user be able to create other users? Also did you get any hints to whether this is using GraphAPI in the background? – Alex KeySmith Apr 15 at 15:34
  • 1
    Source: blogs.msdn.microsoft.com/azuresqldbsupport/2018/10/26/… (see Fernando's comment) The service principal is NOT contained in database, it belongs to AAD Security Group, assigned as SQL Active Directory Admin. Don't know if it uses Graph API or not – Evgheni Ponomarenco Apr 16 at 12:39

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.