question

KaustubhPatil-8558 avatar image
0 Votes"
KaustubhPatil-8558 asked SaurabhSharma-msft edited

Cannot list SQL servers and SQL databases for each server in Azure using service principal

I am trying to get a list of SQL servers and SQL databases using the SqlManagementClient from .NET SDK. I have the following requirement:

  • List the number of SQL servers in a single Azure Subscription

  • List the number of SQL Databases in each of the SQL Servers listed above

In order to do so, we are authenticating using service principal. Following is the snippet of the function that we are using to achieve this.

 var azureCredentials = SdkContext.AzureCredentialsFactory.FromServicePrincipal(
            Constants.ApplicationId,
            Constants.ClientSecret,
            Constants.TenantId,
            AzureEnvironment.AzureGlobalCloud)
     .WithDefaultSubscription(Constants.SubscriptionId);
    
 RestClient restClient = RestClient.Configure()
     .WithEnvironment(AzureEnvironment.AzureGlobalCloud)
     .WithCredentials(azureCredentials)
     .WithLogLevel(HttpLoggingDelegatingHandler.Level.Basic)
     .Build();
    
 SqlManagementClient sqlManagementClient = new SqlManagementClient(restClient);
 sqlManagementClient.SubscriptionId = Constants.SubscriptionId;
 var response = await SubscriptionUsagesOperationsExtensions.ListByLocationAsync(sqlManagementClient.SubscriptionUsages, Constants.Location);


Now the problem part. There are a number of SQL servers that are active under the subscription. Although the above returns an empty list. However if we use a bearer token and list the servers using the same REST API that the above method uses it returns valid results and accurate ones as well. It would be really helpful if you could point out what the issue is with regards to the above example.






dotnet-csharpazure-sql-database
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @kaustubhpatil-8558,

Thanks for using Microsoft Q&A!!
I have checked your code and it gives me the results correctly without any issues (Please check below)
162910-image.png

Are you running your code from your local machine?

Thanks
Saurabh


0 Votes 0 ·
image.png (134.2 KiB)

Yes I am running it from my local machine to test if the results are being returned. The rest of the resources are returned (using respective clients).
What kind of permissions are required to be able to receive this information?
I have provided the permissions to Monitoring Read for the SQL server, tried adding the same on the elastic pool and db (which already inherit it) but still an empty response. What else do I need to consider here?

0 Votes 0 ·

@saurabhsh-msft any update on the above?

0 Votes 0 ·

Good day,

using the SqlManagementClient from .NET SDK.

Off-topic: According to your description you want to get information related to the Azure SQL Database, but the you do not want to get it directly (using queries for example) but using .Net technology which mean that to solve your question someone does not need the knowledge about SQL Server but about .Net

Therefore, you should probably use the relevant tag for .Net

This way you will have better chance to get responded from the right experts


0 Votes 0 ·

Thank you for the information. I was not sure what tags to use. As this was related to both. I will keep it in mind for future purposes.

0 Votes 0 ·
SaurabhSharma-msft avatar image
0 Votes"
SaurabhSharma-msft answered SaurabhSharma-msft edited

@kaustubhpatil-8558 sorry for the delay. I have provided Reader permissions to the service principal on the subscription.

Thanks
Saurabh

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Awesome. Thanks @SaurabhSharma-msft that did the job.

1 Vote 1 ·

Hi @kaustubhpatil-8558,

Great, Thanks for the confirmation.

Thanks
Saurabh


Please do not forget to "Accept the answer" wherever the information provided helps you to help others in the community.

0 Votes 0 ·
KaustubhPatil-8558 avatar image
0 Votes"
KaustubhPatil-8558 answered

As you can see from the discussion above the solution was to set the Reader permission on the subscription for the client being used to fetch the information. Once the permission is set the solution works just fine.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.