Service Principal as SQL Active Directory Admin – Does it Use Graph API?

active-directoryazureazure-sql-databasesql server

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.

Best Answer

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.

Related Solutions

Grant Admin to Active Directory Account in SQL Server

Some of what you are asking for is actually pretty simple.

Permissions for your AD Group

You create a login for your AD group

CREATE LOGIN [domain\AD Group] FROM WINDOWS

Then grant it the access you want. You say you want to grant it Admin access but unless this is your DBA team I wouldn't add the AD Group to something like sysadmin. Instead grant them the permissions you actually want them to have. It sounds like you want

db_ddladmin - Role that grants permissions to create/modify objects within a DB.
db_datareader - Role that grants permissions to run a SELECT against any table/view within the DB
db_datawriter - Role that grants permissions to run UPDATE, INSERT or DELETE statements against any table/view in the DB.
EXECUTE - Permission that grants the ability to EXECUTE any stored procedure or function within the database.

To add this run the following on each user database.

CREATE USER [domain\AD Group] LOGIN [domain\AD Group] 
ALTER ROLE db_ddladmin ADD MEMBER [domain\AD Group]
ALTER ROLE db_datareader ADD MEMBER [domain\AD Group]
ALTER ROLE db_datawriter ADD MEMBER [domain\AD Group]
GRANT EXECUTE TO [domain\AD Group]

If you want these permissions added to new user databases then run the script in the model database as well.

Connecting using your service account

If you have an application running under your service account then when that application connects using trusted authentication it will connect to SQL using that service account. If however you want to connect to SQL using something like SSMS then you have two choices. You can either log into a machine using the service account or run SSMS as a different user.

To run SSMS under a different user hold down the SHIFT key and right click on your shortcut to SSMS and select Run as different user

You'll then get a login/password prompt for an AD login. Type in your service account name & password. Once SSMS opens any trusted (windows auth) connections will be made using the AD login you connected under (your serviced account in this case).

Unable to Connect Using Azure AD Service Principal on SQL Server

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.

Best Answer

Related Solutions

Grant Admin to Active Directory Account in SQL Server

Unable to Connect Using Azure AD Service Principal on SQL Server

Related Question