Advertisement
Поиск  
Always will be ready notify the world about expectations as easy as possible: job change page
Nov 30, 2023

Secrets Management: Best Practices for C# Developers

Secrets Management: Best Practices for C# Developers
Автор:
Источник:
Просмотров:
1138

Keeping your C# applications safe and sound.

Keeping app secrets safe is always tricky for developers. We want to work on the main parts of the app without getting distracted by secret-keeping. But, the app’s safety is very important. So, what ways can we use to better keep our secrets?

Some of the common types of secrets managed in applications include:

  • API Keys: Tokens or keys used to authenticate and gain access to external services, like cloud platforms or third-party APIs.
  • Database Credentials: This includes usernames, passwords, and sometimes connection strings needed to access and interact with a database.
  • Encryption Keys: These are used to encrypt and decrypt data. This can be data at rest in a database or data in transit between services.
  • Service Account Credentials: Usernames and passwords or other credentials for accounts that applications use to run or to interact with other services.
  • Third-party Service Credentials: If an application integrates with third-party services (e.g., payment gateways, email services, etc.), it will have credentials or tokens for these services.

It’s essential to manage these secrets securely to prevent unauthorized access and potential breaches, which can lead to data theft, service disruptions, and other security incidents.

The Example

Here’s a straightforward real-world example:

We utilize Azure’s SQL Databases, spreading them across various Azure SQL Servers. Even though each database maintains the same schema, they’re designated for different tenants, following a one-database-per-tenant approach. There’s a stored procedure in each database named “DailyScheduler” that needs to be invoked daily at a set time. Our task is to develop a program that cycles through the Azure SQL Servers in our subscription, accesses the databases within each server, and then triggers the “DailyScheduler” stored procedure for each database.

The app should utilize the Azure Resource Manager API to interact with Azure resources. For authentication with Azure identity, we can employ the Azure Identity Client Secret Credential.

We must register our app in Azure to set up a client secret.

Register an Azure application

Next, add a client secret.

Add a client secret

Next, within the subscription, assign the “Reader” role to the App. This provides the app with the necessary permissions to access the subscription’s details.

Add role assignment

With the ClientID and Client Secret in hand, we’re now ready to start coding.

Hardcode Secrets

Hardcoding secrets means embedding sensitive information directly into the application’s source code. At first glance, it might seem convenient. After all, it’s right there, easily accessible whenever needed. But this very convenience is its Achilles’ heel.

For illustrative purposes, here’s our initial version of the app, with hardcoded secrets:

using Azure.Identity;
using Azure.ResourceManager;
using Azure.ResourceManager.Resources;
using Azure.ResourceManager.Sql;
using Microsoft.Data.SqlClient;

namespace ConsoleApp
{
    internal class Program
    {
        static void Main(string[] args)
        {

            string tenantId = "b7fae12b-e5d8-4cd9-b3a6-25f23dea5e78";
            string clientId = "9a4fd3c7-b8e1-48f5-87af-56d4a2e913b6";
            string clientSecret = "s2K8Q~Hlj3judujiwimocruvaphosijefrichihufu";

            ArmClient client = new ArmClient(new ClientSecretCredential(tenantId, clientId, clientSecret));
            SubscriptionResource subscription = client.GetDefaultSubscription();
            ResourceGroupCollection resourceGroups = subscription.GetResourceGroups();
            List<Task> tasks = new List<Task>();
            foreach (var resourceGroup in resourceGroups.GetAll())
            {
                var sqlServers = resourceGroup.GetSqlServers();
                foreach (var sqlServer in sqlServers)
                {
                    var sqlDatabases = sqlServer.GetSqlDatabases();
                    foreach (var sqlDatabase in sqlDatabases)
                    {
                        var task = Task.Run(() =>
                        {
                            ExecuteStoredProcedure(sqlServer.Data.Name, sqlDatabase.Data.Name);                            
                        });
                        tasks.Add(task);
                    }
                }
            }

            Task.WaitAll(tasks.ToArray());
        }

        private static void ExecuteStoredProcedure(string serverName, string dbName)
        {
            var userId = "sysdba";
            var password = "fAviyL5hL9uS5iTheTr4";
            var connectionString = $"Server=tcp:{serverName}.database.windows.net,1433;Initial Catalog={dbName};Persist Security Info=False;User ID={userId};Password={password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";

            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                using (var command = new SqlCommand("DailyScheduler", connection))
                {
                    command.CommandType = System.Data.CommandType.StoredProcedure;                    
                    command.CommandTimeout = 0;
                    command.ExecuteNonQuery();
                }
            }
        }
    }
}

In 2014, Uber experienced a breach when attackers accessed a private GitHub repository and found AWS credentials hardcoded into the source. This breach exposed data for about 50,000 drivers.

Configuration Files

C# and the .NET ecosystem have a rich heritage of using configuration files, historically known as App.config or Web.config. With the advent of .NET Core, a new, more versatile configuration system was introduced, enabling the use of JSON-based appsettings.json files, among other formats.

The appsettings.json uses a straightforward JSON structure. For instance:

{
  "Azure": {
    "TenantId": "YOUR_TENANT_ID",
    "ClientId": "YOUR_CLIENT_ID",
    "ClientSecret": "YOUR_CLIENT_SECRET"
  },
  "Database": {
    "UserId": "YOUR_USER_ID",
    "Password": "YOUR_PASSWORD"
  }
}

Install the necessary NuGet packages:

Install-Package Microsoft.Extensions.Configuration
Install-Package Microsoft.Extensions.Configuration.Json

Refactor the application:

internal class Program
{
    static void Main(string[] args)
    {
        // Set up configuration
        var config = new ConfigurationBuilder()
            .SetBasePath(System.IO.Directory.GetCurrentDirectory())
            .AddJsonFile("appsettings.json")
            .Build();

        string tenantId = config["Azure:TenantId"];
        string clientId = config["Azure:ClientId"];
        string clientSecret = config["Azure:ClientSecret"];

        ArmClient client = new ArmClient(new ClientSecretCredential(tenantId, clientId, clientSecret));
        SubscriptionResource subscription = client.GetDefaultSubscription();
        ResourceGroupCollection resourceGroups = subscription.GetResourceGroups();
        List<Task> tasks = new List<Task>();
        foreach (var resourceGroup in resourceGroups.GetAll())
        {
            var sqlServers = resourceGroup.GetSqlServers();
            foreach (var sqlServer in sqlServers)
            {
                var sqlDatabases = sqlServer.GetSqlDatabases();
                foreach (var sqlDatabase in sqlDatabases)
                {
                    var task = Task.Run(() =>
                    {
                        ExecuteStoredProcedure(sqlServer.Data.Name, sqlDatabase.Data.Name, config);                            
                    });
                    tasks.Add(task);
                }
            }
        }

        Task.WaitAll(tasks.ToArray());
    }

    private static void ExecuteStoredProcedure(string serverName, string dbName, IConfiguration config)
    {
        var userId = config["Database:UserId"];
        var password = config["Database:Password"];
        var connectionString = $"Server=tcp:{serverName}.database.windows.net,1433;Initial Catalog={dbName};Persist Security Info=False;User ID={userId};Password={password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";

        using (var connection = new SqlConnection(connectionString))
        {
            connection.Open();

            using (var command = new SqlCommand("DailyScheduler", connection))
            {
                command.CommandType = System.Data.CommandType.StoredProcedure;                    
                command.CommandTimeout = 0;
                command.ExecuteNonQuery();
            }
        }
    }
}

Configuration file separated the secretes from the source code. But storing sensitive data directly within configuration files and committing them to source control is a common but dangerous practice. Configuration files with secrets committed to public repositories can be seen by anyone. Even if it’s a private repository, unnecessary exposure of secrets to all members of a project can occur. Most version control systems, like Git, keep a detailed history of changes. If a secret is committed, it remains in the history even if you remove it in a subsequent commit.

Use environment-specific config files like appsettings.Production.json or appsettings.Staging.json. The base appsettings.json file can contain default or dummy values, while environment-specific files (which are not committed to source control) override these settings.

Install-Package Microsoft.Extensions.Configuration.EnvironmentVariables
// Set up configuration
var config = new ConfigurationBuilder()
    .SetBasePath(Directory.GetCurrentDirectory())
    .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
    .AddJsonFile($"appsettings.{Environment.GetEnvironmentVariable("env") ?? "dev"}.json", optional: true)
    .AddEnvironmentVariables().Build();

If you have configuration files with secrets that are necessary for local development, make sure to add them to your .gitignore file to prevent them from being accidentally committed.

Embracing configuration files is akin to moving from clay tablets to paper. It’s a step in the right direction but requires due diligence.

Environment Variables

To fully eliminate secrets from the source code, utilizing environment variables is an effective method.

Environment variables are key-value pairs stored outside the application’s context, making them ideal for storing secrets without the risk of bundling them with the source code.

To set up and utilize environment variables in a C# application:

Creating an Environment Variable: This typically varies based on the OS.

  • Windows: Use the ‘Environment Variables’ dialog (accessible through System Properties) or the setx command.
  • Linux/macOS: Use the export command in the terminal.

For instance, setting up a database password would look like:

export DATABASE_PASSWORD=my_secure_password

Accessing in C#: Once set, environment variables can be easily accessed within your application using the .NET Core framework.

string dbPassword = Environment.GetEnvironmentVariable("DATABASE_PASSWORD");

The .NET Core configuration system is its ability to seamlessly integrate various configuration sources. Environment variables can be included with minimal effort.

var builder = new ConfigurationBuilder()
    .AddEnvironmentVariables(); // Load environment variables
IConfiguration configuration = builder.Build();

string dbPassword = configuration["DATABASE_PASSWORD"];

Here’s a revised version of the program, using environment variables to store sensitive data:

internal class Program
{
    private static IConfiguration Configuration { get; set; }

    static void Main(string[] args)
    {
        var builder = new ConfigurationBuilder()
            .AddEnvironmentVariables();
        Configuration = builder.Build();

        string tenantId = Configuration["TENANT_ID"];
        string clientId = Configuration["CLIENT_ID"];
        string clientSecret = Configuration["CLIENT_SECRET"];

        ArmClient client = new ArmClient(new ClientSecretCredential(tenantId, clientId, clientSecret));
        SubscriptionResource subscription = client.GetDefaultSubscription();
        ResourceGroupCollection resourceGroups = subscription.GetResourceGroups();
        List<Task> tasks = new List<Task>();
        foreach (var resourceGroup in resourceGroups.GetAll())
        {
            var sqlServers = resourceGroup.GetSqlServers();
            foreach (var sqlServer in sqlServers)
            {
                var sqlDatabases = sqlServer.GetSqlDatabases();
                foreach (var sqlDatabase in sqlDatabases)
                {
                    var task = Task.Run(() =>
                    {
                        ExecuteStoredProcedure(sqlServer.Data.Name, sqlDatabase.Data.Name);
                    });
                    tasks.Add(task);
                }
            }
        }

        Task.WaitAll(tasks.ToArray());
    }

    private static void ExecuteStoredProcedure(string serverName, string dbName)
    {
        var userId = Configuration["DB_USER_ID"];
        var password = Configuration["DB_PASSWORD"];
        var connectionString = $"Server=tcp:{serverName}.database.windows.net,1433;Initial Catalog={dbName};Persist Security Info=False;User ID={userId};Password={password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";

        using (var connection = new SqlConnection(connectionString))
        {
            connection.Open();

            using (var command = new SqlCommand("DailyScheduler", connection))
            {
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.CommandTimeout = 0;
                command.ExecuteNonQuery();
            }
        }
    }
}

Before running the application, you’ll need to set the environment variables TENANT_ID, CLIENT_ID, CLIENT_SECRET, DB_USER_ID, and DB_PASSWORD.

On a Windows system, you can set them in PowerShell using:

$env:TENANT_ID="your_tenant_id"
$env:CLIENT_ID="your_client_id"
$env:CLIENT_SECRET="your_client_secret"
$env:DB_USER_ID="your_db_user_id"
$env:DB_PASSWORD="your_db_password"

On a Unix-like system:

export TENANT_ID="your_tenant_id"
export CLIENT_ID="your_client_id"
export CLIENT_SECRET="your_client_secret"
export DB_USER_ID="your_db_user_id"
export DB_PASSWORD="your_db_password"

Environment variables allow you to keep this sensitive data out of your codebase. Since sensitive data isn’t stored in configuration files, there’s no risk of accidentally committing secrets into version control systems like Git.

Environment variables can easily be changed depending on the environment where the application is running (e.g., development, staging, production). This allows for environment-specific configurations without needing to modify the application’s source code or config files.

With containerization technologies like Docker and orchestration tools like Kubernetes, environment variables are a native way to inject configuration into containers. This makes application deployment and configuration seamless.

Environment variables provide a language-agnostic method of configuration. Regardless of the programming language or technology stack, almost every platform supports reading from environment variables.

However, it’s worth noting that environment variables are not a panacea. They have their own set of challenges, such as:

  • Potential for name clashes in the global namespace.
  • Difficulty in managing and tracking changes compared to version-controlled configuration files.
  • Less structure than configuration files, making complex configurations potentially harder to manage.

Azure Key Vault

While environment variables offer developers the crucial advantage of separating sensitive data from source code, it’s just one piece of the secrets management puzzle. Even as environment variables maintain this separation, the onus of ensuring that these variables remain secure on every development, testing, or production machine lies with developers and operations teams. This is where more advanced secrets management tools, like Azure Key Vault, come into the picture.

Azure Key Vault is a cloud service specifically designed to safeguard cryptographic keys and other secrets. It does more than just store secrets — it actively adds layers of security:

  • Centralized Secrets Management: Azure Key Vault provides a centralized repository that is safeguarded against external and internal threats.
  • Integrated with Azure AD: It integrates seamlessly with Azure Active Directory, allowing for strict role-based access controls. This ensures only authorized applications or users can access specific secrets.
  • Encrypted at Rest and in Transit: Secrets within Azure Key Vault are encrypted both at rest and during transit, ensuring data remains confidential.
  • Audit Trails: With Azure Key Vault, you can maintain detailed logs of when and how secrets are accessed, which is invaluable for compliance and monitoring.
  • Automated Rotations: Azure Key Vault, when combined with other Azure services, can automate the rotation of secrets, ensuring that older, possibly compromised secrets are regularly phased out.

However, we face a catch-22 situation. While we can store our secrets in Azure Key Vault, accessing the Key Vault itself requires client secret credentials. So, using Azure Key Vault essentially allows us to progress by just one step, enabling us to store the SQL server credentials therein.

Install the package:

Install-Package Azure.Security.KeyVault.Secrets

Give our app’s access on the Azure Key Vault in Azure Portal, then rewritten Program:

internal class Program
{
    static void Main(string[] args)
    {

        var config = new ConfigurationBuilder()                
            .AddEnvironmentVariables().Build();

        string tenantId = config["Azure:TenantId"];
        string clientId = config["Azure:ClientId"];
        string clientSecret = config["Azure:ClientSecret"];

        var keyVaultUrl = "https://<YourKeyVaultName>.vault.azure.net/";
        
        var secretClient = new SecretClient(new Uri(keyVaultUrl), new ClientSecretCredential(tenantId, clientId, clientSecret));            

        ArmClient client = new ArmClient(new ClientSecretCredential(tenantId, clientId, clientSecret));
        SubscriptionResource subscription = client.GetDefaultSubscription();
        ResourceGroupCollection resourceGroups = subscription.GetResourceGroups();
        List<Task> tasks = new List<Task>();

        foreach (var resourceGroup in resourceGroups.GetAll())
        {
            var sqlServers = resourceGroup.GetSqlServers();
            foreach (var sqlServer in sqlServers)
            {
                var sqlDatabases = sqlServer.GetSqlDatabases();
                foreach (var sqlDatabase in sqlDatabases)
                {
                    var task = Task.Run(() =>
                    {
                        ExecuteStoredProcedure(sqlServer.Data.Name, sqlDatabase.Data.Name, secretClient);                            
                    });
                    tasks.Add(task);
                }
            }
        }

        Task.WaitAll(tasks.ToArray());
    }

    private static void ExecuteStoredProcedure(string serverName, string dbName, SecretClient secretClient)
    {
        var userId = secretClient.GetSecret("sqlUserId").Value.Value;
        var password = secretClient.GetSecret("sqlPassword").Value.Value;
        
        var connectionString = $"Server=tcp:{serverName}.database.windows.net,1433;Initial Catalog={dbName};Persist Security Info=False;User ID={userId};Password={password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";

        using (var connection = new SqlConnection(connectionString))
        {
            connection.Open();
            using (var command = new SqlCommand("DailyScheduler", connection))
            {
                command.CommandType = System.Data.CommandType.StoredProcedure;                    
                command.CommandTimeout = 0;
                command.ExecuteNonQuery();
            }
        }
    }
}

Can we find a solution to the secret zero problem?

Managed Identity

One approach to solving this problem, at least in cloud environments like Azure, is to use managed identities. A managed identity provides an identity for applications to use when connecting to resources, without the need for secrets in your code.

Azure Managed Identities provides an identity for applications to use when connecting to resources. This eliminates the need for developers having to manage credentials. Instead, the credentials are managed by Azure Active Directory (Azure AD), and your code needs no secrets.

Suppose we plan to deploy our app on an Azure Virtual Machine. In that case, we’ll set up Managed Identity as follows:

Assign a Managed Identity to your Azure service (e.g., Azure Functions, Web Apps, VMs). This is done in the Azure portal or through Azure CLI/PowerShell.

Assign a Managed Identity

Grant Permissions: Give the Managed Identity access to the required secrets in Azure Key Vault. This is done in the Key Vault’s access policies.

Azure role assignments

Now we can modified our program like following:

internal class Program
{
    static void Main(string[] args)
    {            
        
        var keyVaultUrl = "https://<YourKeyVaultName>.vault.azure.net/";            
        var secretClient = new SecretClient(new Uri(keyVaultUrl), new ManagedIdentityCredential());            

        ArmClient client = new ArmClient(new ManagedIdentityCredential()));
        SubscriptionResource subscription = client.GetDefaultSubscription();
        ResourceGroupCollection resourceGroups = subscription.GetResourceGroups();
        List<Task> tasks = new List<Task>();

        foreach (var resourceGroup in resourceGroups.GetAll())
        {
            var sqlServers = resourceGroup.GetSqlServers();
            foreach (var sqlServer in sqlServers)
            {
                var sqlDatabases = sqlServer.GetSqlDatabases();
                foreach (var sqlDatabase in sqlDatabases)
                {
                    var task = Task.Run(() =>
                    {
                        ExecuteStoredProcedure(sqlServer.Data.Name, sqlDatabase.Data.Name, secretClient);                            
                    });
                    tasks.Add(task);
                }
            }
        }

        Task.WaitAll(tasks.ToArray());
    }

    private static void ExecuteStoredProcedure(string serverName, string dbName, SecretClient secretClient)
    {
        var userId = secretClient.GetSecret("sqlUserId").Value.Value;
        var password = secretClient.GetSecret("sqlPassword").Value.Value;
        
        var connectionString = $"Server=tcp:{serverName}.database.windows.net,1433;Initial Catalog={dbName};Persist Security Info=False;User ID={userId};Password={password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";

        using (var connection = new SqlConnection(connectionString))
        {
            connection.Open();
            using (var command = new SqlCommand("DailyScheduler", connection))
            {
                command.CommandType = System.Data.CommandType.StoredProcedure;                    
                command.CommandTimeout = 0;
                command.ExecuteNonQuery();
            }
        }
    }
}

Using managed identity, we’ve not only eliminated client credentials from our code and accessed SQL credentials from the Key Vault but also removed the need to register our app in Azure, which is a requirement only when using client secret credentials.

If you decide to deploy this app as an Azure Function instead, there’s no need to alter the secrets management approach. You can continue using managed identity; simply configure it for your function app.

Here is the modified version as Azure function:

public class DbDailyScheduler
{
    [FunctionName("DbDailyScheduler")]
    public static void Run([TimerTrigger("0 0 * * * *"
#if DEBUG
,RunOnStartup= true
#endif
)]TimerInfo myTimer, ILogger log)
    {
        log.LogInformation($"DbDailyScheduler function executed at: {DateTime.Now}");
        var keyVaultUrl = "https://<YourKeyVaultName>.vault.azure.net/";
        
        var secretClient = new SecretClient(new Uri(keyVaultUrl), new ManagedIdentityCredential());            

        ArmClient client = new ArmClient(new ManagedIdentityCredential()));
        SubscriptionResource subscription = client.GetDefaultSubscription();
        ResourceGroupCollection resourceGroups = subscription.GetResourceGroups();

        List<Task> tasks = new List<Task>();

        foreach (var resourceGroup in resourceGroups.GetAll())
        {
            var sqlServers = resourceGroup.GetSqlServers();
            foreach (var sqlServer in sqlServers)
            {
                var sqlDatabases = sqlServer.GetSqlDatabases();
                foreach (var sqlDatabase in sqlDatabases)
                {
                    var task = Task.Run(() =>
                    {
                        ExecuteStoredProcedure(sqlServer.Data.Name, sqlDatabase.Data.Name, secretClient);
                    });
                    tasks.Add(task);
                }
            }
        }

        Task.WaitAll(tasks.ToArray());            
    }


    private static void ExecuteStoredProcedure(string serverName, string dbName, SecretClient secretClient)
    {
        var userId = secretClient.GetSecret("sqlUserId").Value.Value;
        var password = secretClient.GetSecret("sqlPassword").Value.Value;

        var connectionString = $"Server=tcp:{serverName}.database.windows.net,1433;Initial Catalog={dbName};Persist Security Info=False;User ID={userId};Password={password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";

        using (var connection = new SqlConnection(connectionString))
        {
            connection.Open();
            using (var command = new SqlCommand("DailyScheduler", connection))
            {
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.CommandTimeout = 0;
                command.ExecuteNonQuery();
            }
        }
    }
}

Fallback strategy

The example above integrates smoothly with Managed Identity when deployed on Azure. However, outside of Azure, such as in a local development setting, we need alternative methods like environment variables, Azure CLI, or interactive browser authentication.

Rather than coding for various scenarios, we can utilize DefaultAzureCredential to simplify the process.

DefaultAzureCredential is a part of the Azure SDK for .NET and is designed to provide a streamlined developer experience by attempting multiple authentication methods internally to find the best way to authenticate based on the environment where the code is running. It's especially useful for reducing the overhead of managing authentication across various environments, like local development, CI/CD, and production deployments.

Here’s how DefaultAzureCredential works:

Order of Authentication Methods: DefaultAzureCredential attempts to authenticate through a sequence of methods. It will stop once a method has succeeded. The default order is as follows:

EnvironmentCredential
WorkloadIdentityCredential
ManagedIdentityCredential
SharedTokenCacheCredential
VisualStudioCredential
VisualStudioCodeCredential
AzureCliCredential
AzurePowerShellCredential
AzureDeveloperCliCredential
InteractiveBrowserCredential

Environment Variables: If environment variables AZURE_CLIENT_ID, AZURE_TENANT_ID, and AZURE_CLIENT_SECRET are set, the EnvironmentCredential will use them to authenticate.

Managed Identity: If the application is running on an Azure resource that has been configured with a Managed Identity, ManagedIdentityCredential will be used.

Developer Tool Integrations:

  • Shared Token Cache: This is typically for desktop developer scenarios. If the developer has signed into Visual Studio, the SharedTokenCacheCredential can pick up that token.
  • Visual Studio: For developers using Visual Studio, if they are signed in, VisualStudioCredential will use that session.
  • Visual Studio Code: If the developer is using Visual Studio Code’s Azure Account extension, VisualStudioCodeCredential will use its token.

Azure CLI: If a developer has the Azure CLI installed and has logged in using az login, the AzureCliCredential will use that session.

Interactive Browser: As a last-ditch effort, especially useful in local development environments, the InteractiveBrowserCredential can be used to prompt the developer to sign in interactively.

By using DefaultAzureCredential, developers can write their application once, without needing to change the authentication method based on the environment. This is especially helpful in ensuring that production configurations don't accidentally leak into developer environments and vice versa.

With that we can have the following version of our app:

public class DbDailyScheduler
{
    [FunctionName("DbDailyScheduler")]
    public static void Run([TimerTrigger("0 0 * * * *"
#if DEBUG
,RunOnStartup= true
#endif
)]TimerInfo myTimer, ILogger log)
    {
        log.LogInformation($"DbDailyScheduler function executed at: {DateTime.Now}");
        var keyVaultUrl = "https://<YourKeyVaultName>.vault.azure.net/";
        
        var secretClient = new SecretClient(new Uri(keyVaultUrl), new DefaultAzureCredential());            

        ArmClient client = new ArmClient(new DefaultAzureCredential()));
        SubscriptionResource subscription = client.GetDefaultSubscription();
        ResourceGroupCollection resourceGroups = subscription.GetResourceGroups();

        List<Task> tasks = new List<Task>();

        foreach (var resourceGroup in resourceGroups.GetAll())
        {
            var sqlServers = resourceGroup.GetSqlServers();
            foreach (var sqlServer in sqlServers)
            {
                var sqlDatabases = sqlServer.GetSqlDatabases();
                foreach (var sqlDatabase in sqlDatabases)
                {
                    var task = Task.Run(() =>
                    {
                        ExecuteStoredProcedure(sqlServer.Data.Name, sqlDatabase.Data.Name, secretClient);
                    });
                    tasks.Add(task);
                }
            }
        }

        Task.WaitAll(tasks.ToArray());            
    }

    private static void ExecuteStoredProcedure(string serverName, string dbName, SecretClient secretClient)
    {
        var userId = secretClient.GetSecret("sqlUserId").Value.Value;
        var password = secretClient.GetSecret("sqlPassword").Value.Value;

        var connectionString = $"Server=tcp:{serverName}.database.windows.net,1433;Initial Catalog={dbName};Persist Security Info=False;User ID={userId};Password={password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";

        using (var connection = new SqlConnection(connectionString))
        {
            connection.Open();
            using (var command = new SqlCommand("DailyScheduler", connection))
            {
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.CommandTimeout = 0;
                command.ExecuteNonQuery();
            }
        }
    }
}
Похожее
Jul 23, 2023
Unlocking Resilience and Transient-fault-handling in your C# CodeIn an ideal world, every operation we execute, every API we call, and every database we query, would always work flawlessly. Unfortunately, we live in a world where network outages, server overloads, and...
Apr 7, 2023
Author: Matt Eland
Exploring nullability improvements in C# and the !, ?, ??, and ??= operators.Null is famously quoted as being the "billion-dollar mistake" due to the quantity of NullReferenceExceptions that we see in code (particularly when just starting out).The prevalence of null...
Jan 10
Author: MESCIUS inc.
In today’s connected world, data is piling up very fast. We are generating more data than ever in human history, and a lot of this data is getting stored in non-relational formats like JSON documents. JSON has become a ubiquitous...
Mar 28
Author: Orestis Meikopoulos
In today’s story we are going to talk about what Tasks are and have a brief introduction on the Task Parallel Library (TPL). We will also give a small preview of the Async & Task-Based Asynchronous Patterns.TasksTo begin talking about...
Написать сообщение
Почта
Имя
*Сообщение


© 1999–2024 WebDynamics
1980–... Sergey Drozdov
Area of interests: .NET Framework | .NET Core | C# | ASP.NET | Windows Forms | WPF | HTML5 | CSS3 | jQuery | AJAX | Angular | React | MS SQL Server | Transact-SQL | ADO.NET | Entity Framework | IIS | OOP | OOA | OOD | WCF | WPF | MSMQ | MVC | MVP | MVVM | Design Patterns | Enterprise Architecture | Scrum | Kanban