Introduction
This is the second part of my series explaining Pulumi using C#. In the first post, you got a brief introduction to the basics.
This time, I want to showcase how to deploy an Azure SQL Server instance with a database. For validation, I will restrict database access to my current IP address. Additionally, as preparation for Keycloak, the database will include a separate user.
There are three main components to set up:
- Azure SQL Server Instance
- Azure SQL Server Database
- Firewall rule to allow connections from my current IP
ℹ️ Important
We need an SQL Instace because the Database cannot exist without.
Setup a Stack
After creating the project with the Pulumi CLI, we get a basic application. To improve structure, we’ll organize the project by declaring a Stack . A stack encapsulates the IaC solution within the project. For each resource, a separate method will be created to encapsulate resources and improve reusability.
To ensure security from the start, passwords will not be hardcoded but randomly generated. To achieve this, we need to add Pulumi.Random to the project:
1pushd src/az-keycloak
2dotnet add package Pulumi.Random
Next, create a folder called Stacks to organize our stack:
mkdir Stacks
Here, we’ll add the IamStack class. To maintain flexibility, we’ll use Pulumi’s configuration to read values from Pulumi.(<stack>.)?yaml. We’ll make the name, region, and SQL user names for admin and Keycloak configurable.
1// instance of <see cref="Config" /> to read Pulumi configuration.
2private readonly Config _config = new();
3
4// name used for resource group & sql server
5private string _name => _config.Get("name") ?? "demo";
6
7// get the admin user from the configuration, if not present assume `sqladm`.
8private string _sqlAdminUser => _config.Get("sql.admin.user") ?? "sqladm";
9
10// get the keycloak user from the configuration, if not present assume `kcadm`.
11private string _keycloakDbUser => _config.Get("sql.kc.user") ?? "kcadm";
12
13// region to place our resources, if not present use `westeurope`.
14private string _region => _config.Get("location") ?? "westeurope";
15}
Pulumi uses outputs to store data created during runtime. We’ll add outputs for the SQL admin and Keycloak user passwords. To securely retrieve and manage these outputs, Pulumi provides commands such as pulumi stack output. Sensitive data should be handled carefully by redirecting output to secure locations or using environment variables when needed. After the stack is created, these outputs can be retrieved.
1// will hold the sql admin password, after it was created.
2[Output] public Output<string> SqlAdminPassword { get; set; } = default!;
3
4// will hold the sql keycloak password, after it was created.
5[Output] public Output<string> KeycloakDbPassword { get; set; } = default!;
💡 Tip
The values can be retrieved after deployment with
pulumi stack output --show-secrets
The value of the outputs can be retieved after the deployment.
To separate resource creation, we need to track created resources:
1// reference to the resource group
2private readonly AzureNative.Resources.ResourceGroup _resourceGroup = default!;
3
4// reference to the sql server
5private readonly AzureNative.Sql.Server _sqlServer = default!;
6
7// reference to the sql database
8private readonly AzureNative.Sql.Database _sqlDatabase = default!;
Add a ResourceGroup
The first resource needed in Azure is a resource group. Here’s a method to create it. We simply name the resource group and assign it to the desired region.
💡 Tip
Pulumi requires unique names for resources within a stack.
1/// <summary>
2/// Create a Azure Resource Group.
3/// </summary>
4/// <param name="name">Resource Group name</param>
5/// <param name="region">Azure region</param>
6/// <returns><see cref="AzureNative.Resources.ResourceGroup"/></returns>
7private AzureNative.Resources.ResourceGroup AddResourceGroup(string name, string region)
8 => new AzureNative.Resources.ResourceGroup("resource-group", new()
9 {
10 ResourceGroupName = $"rg-{name}-{region}-1",
11 Location = region
12 });
💡 Tip
I stick to the Azure naming conventions
Add a SqlServer
To create a SQL server, we’ll define a method that generates a random password, places the server in the resource group, and adds firewall rules for connections from Azure and the current IP.
- Generate a random password: To ensure security, the password must be 18 characters long and include special characters.
- Define the Azure SQL Server: Place it in the resource group and the same region, and set the user and password.
- Add firewall rules: Allow connections from within Azure and the current public IP of your internet connection. The rule depends on the SQL Server and will not be created until the resource is finally deployed.
💡 The rule for the public IP of your internet connection is needed to provision additional users later.
1/// <summary>
2/// Create Azure Sql Server.
3/// </summary>
4/// <param name="name">Server name.</param>
5/// <returns><see cref="AzureNative.Sql.Server"/></returns>
6private AzureNative.Sql.Server AddSqlServer(string name)
7{
8 // generate the password for the sql admin user
9 SqlAdminPassword = new Random.RandomPassword("password-sql-admin", new()
10 {
11 Length = 18,
12 Special = true,
13 OverrideSpecial = "!#$%&*()-_=+[]{}<>:?",
14 }).Result;
15
16 var sqlServer = new AzureNative.Sql.Server($"sql-server-{name}", new()
17 {
18 ServerName = _resourceGroup.Location.Apply(location => $"sql-{name}-{location}-1"),
19 ResourceGroupName = _resourceGroup.Name,
20 Location = _resourceGroup.Location,
21 AdministratorLogin = _sqlAdminUser,
22 AdministratorLoginPassword = SqlAdminPassword.Apply(pwd => $"{pwd}"),
23 MinimalTlsVersion = "1.2",
24 Version = "12.0",
25 PublicNetworkAccess = AzureNative.Sql.ServerNetworkAccessFlag.Enabled,
26 RestrictOutboundNetworkAccess = AzureNative.Sql.ServerNetworkAccessFlag.Disabled
27 });
28
29 // Allow connection to SQL Server from other Azure Services, wait fore sqlServer to be ready
30 var azureFirewallRule = new AzureNative.Sql.FirewallRule("fw-rule-public", new()
31 {
32 FirewallRuleName = "allowAllWindowsAzureIps",
33 ResourceGroupName = _resourceGroup.Name,
34 ServerName = sqlServer.Name,
35 StartIpAddress = "0.0.0.0",
36 EndIpAddress = "0.0.0.0",
37 }, new CustomResourceOptions
38 {
39 DependsOn = { sqlServer }
40 });
41
42 // Allow connection to SQL Server via internet for the current IP
43 var myIp = GetPublicIp().GetAwaiter().GetResult();
44 var myFirewallRule = new AzureNative.Sql.FirewallRule("fw-rule-currentIP", new()
45 {
46 FirewallRuleName = "allowMyIp",
47 ResourceGroupName = _resourceGroup.Name,
48 ServerName = sqlServer.Name,
49 StartIpAddress = myIp,
50 EndIpAddress = myIp,
51 }, new CustomResourceOptions
52 {
53 DependsOn = { sqlServer }
54 });
55
56 return sqlServer;
57}
To get the public Ip I will use IpInfo.
1/// <summary>
2/// Get your current public IP.
3/// </summary>
4/// <param name="serviceUrl">Service to use. Default = https://ipinfo.io</param>
5/// <param name="path">Relative path to get the ip. Default = ip</param>
6/// <returns></returns>
7private static async Task<string> GetPublicIp(string serviceUrl = "https://ipinfo.io", string path = "ip")
8{
9 using var httpClient = new HttpClient();
10 httpClient.BaseAddress = new Uri(serviceUrl);
11
12 return await httpClient.GetStringAsync(path);
13}
Add a Sql Database
To create a database, I also add a method that allows passing in the database name, capacity, and tier to use. To avoid spending too much money, I choose minimal settings. The database depends on the Azure SQL Server and will only be created once the server is deployed.
1/// <summary>
2/// Create a SQL database. Defaults are choosen to cut costs as much as possible.
3/// </summary>
4/// <param name="name">Database name.</param>
5/// <param name="capacity">Allocated capacity. Default = 5.</param>
6/// <param name="tier">Tier to use. Default = Basic</param>
7/// <returns><see cref="AzureNative.Sql.Database"/></returns>
8private AzureNative.Sql.Database AddSqlDatabase(string name, int capacity = 5, string tier = "Basic")
9 => new AzureNative.Sql.Database($"sql-db-{name}", new()
10 {
11 DatabaseName = name,
12 Location = _resourceGroup.Location,
13 ServerName = _sqlServer.Name,
14 ZoneRedundant = false,
15 Sku = new AzureNative.Sql.Inputs.SkuArgs
16 {
17 Capacity = capacity,
18 Name = tier,
19 Tier = tier
20 }
21 }, new CustomResourceOptions
22 {
23 DependsOn = { _sqlServer }
24 });
Add a Sql User
Even though we do not require it yet, we will need to add database users to allow access for Keycloak later on.
ℹ️ Important
Please be aware this is not the best practice. You should use a
Service Principalfrom Azure Entra to have passwordless authentication. However, this is out of scope here.
1private void SetupSqlDbUser(string login, Output<string> password)
2{
3 Output.All(_sqlServer.Name, _sqlDatabase.Name, SqlAdminPassword, password).Apply(x => {
4
5 var sqlAdminPassword = x[2];
6 var userPassword = x[3];
7
8 var connectionStringBuilderMaster = new SqlConnectionStringBuilder();
9 connectionStringBuilderMaster.DataSource = $"{x[0]}.database.windows.net,1433";
10 connectionStringBuilderMaster.InitialCatalog = "master";
11 connectionStringBuilderMaster.UserID= _sqlAdminUser;
12 connectionStringBuilderMaster.Password = sqlAdminPassword;
13 connectionStringBuilderMaster.TrustServerCertificate = true;
14 connectionStringBuilderMaster.Encrypt = true;
15 connectionStringBuilderMaster.HostNameInCertificate = "*.database.windows.net";
16 connectionStringBuilderMaster.ConnectTimeout = 30;
17
18 try {
19 using (var conn = new SqlConnection(connectionStringBuilderMaster.ConnectionString))
20 {
21 conn.Open();
22 new SqlCommand(@$"
23 IF NOT EXISTS (SELECT * FROM [sys].[sql_logins] WHERE NAME = '{login}')
24 CREATE LOGIN [{login}] WITH password='{userPassword}';
25 ", conn).ExecuteNonQuery();
26 }
27
28 var connectionStringBuilderKeycloak = new SqlConnectionStringBuilder();
29 connectionStringBuilderKeycloak.DataSource = $"{x[0]}.database.windows.net,1433";
30 connectionStringBuilderKeycloak.InitialCatalog = x[1];
31 connectionStringBuilderKeycloak.UserID= _sqlAdminUser;
32 connectionStringBuilderKeycloak.Password = sqlAdminPassword;
33 connectionStringBuilderKeycloak.TrustServerCertificate = true;
34 connectionStringBuilderKeycloak.Encrypt = true;
35 connectionStringBuilderKeycloak.HostNameInCertificate = "*.database.windows.net";
36 connectionStringBuilderKeycloak.ConnectTimeout = 30;
37 using (var conn = new SqlConnection(connectionStringBuilderKeycloak.ConnectionString))
38 {
39 conn.Open();
40 new SqlCommand(@$"
41 IF NOT EXISTS (SELECT * FROM [sys].[sysusers] WHERE NAME = '{login}')
42 CREATE USER [{login}] FROM LOGIN [{login}];
43 EXEC sp_addrolemember 'db_owner', '{login}';
44 ", conn).ExecuteNonQuery();
45 }
46
47 }
48 catch (Exception ex)
49 {
50 System.Console.WriteLine(ex.Message);
51
52 throw;
53 }
54
55 return true;
56 });
57}
Conclusion
I’ve shown how to create a resource group, an SQL Server, and a database with separate methods. This approach makes it easy to set up multiple resources by reusing the existing code. The solution is configurable and employs reasonable security with a randomly generated password. There’s room for improvement, but the intent is to keep it simple, so I have adhered to this approach.
To get a working deployment, we need to use the created methods.
1/// <summary>
2/// CTOR
3/// </summary>
4public IamStack()
5{
6 _resourceGroup = AddResourceGroup(_name, _region);
7 _sqlServer = AddSqlServer(_name);
8 _sqlDatabase = AddSqlDatabase("keycloak");
9}
You can find the full working code repo here.