CODEWITHSUNDEEP
c#azure-sql-databaseazure-service-principal
Vinny
Vinny

Reputation: 629

How to get connect Azure SQL instance using service principal with federated credentials in C#

I used below working code to connect Azure SQL instance using my service principal which has a secret. The code shown here is working fine. But I am confused how to get AccessToken (conn.AccessToken) using the ServicePrincipal without a secret or certificate.

string clientSecretKey = "XXXXXXXXXXXXXXXXXXXX";
string sqlConnectionString = string.Format("Data Source=tcp:{0},1433;Initial Catalog={1};Persist Security Info=False;Connect Timeout=30;Encrypt=True;TrustServerCertificate=False", configs.SqlServerName, configs.SqlDatabaseName);

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

AuthenticationContext authenticationContext = new AuthenticationContext(string.Format(AadInstance, configs.PMETenantID));

ClientCredential clientCredential = new ClientCredential(configs.AppClientID, clientSecretKey);

AuthenticationResult authenticationResult = authenticationContext.AcquireTokenAsync(configs.SqlResourceId, clientCredential).Result;

List<Employee> employeeResult = new();

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

    using (var cmd = new SqlCommand(sampleSqlQuery, conn))
    {
        var results = cmd.ExecuteReader();

        while (results.Read())
        {
            employeeResult.Add(new Employee
            {
                EmployeeId = results.GetInt16(0),
                FirstName = results.GetString(1),
                LastName = results.GetString(2),
                LastUpdatedDate = results.GetDateTime(3),
                SessionId = results.GetGuid(4)
            });
        }
    }
}

Can someone give me sample method to connect to an Azure SQL instance using my service principal with federated credentials? Meaning, my service principal does not have a secret or a certificate associated with it. It has a managed identity associated with it.

Upvotes: 0

Views: 424

Answers (1)

Vinny
Vinny

Reputation: 629

I got it working using below code

var aADManagedIdentity = configs.Tenants.Where(x => x.Description.Equals("PME")).First();
var aadAuthContext = new AadAuthContext(aADManagedIdentity.Auth.ManagedIdentityClientId, aADManagedIdentity.Id, aADManagedIdentity.Auth.AadInstance);
aadAuthContext.AadAppClientId = aADManagedIdentity.Auth.ClientId;
var token = Authentication.GetTokenUsingSpnAsync(new string[] { configs.SqlResourceId + "/.default" }
    , aadAuthContext, logger).Result.AccessToken;

Upvotes: 0

Related Questions