2021-06-07

Transport for Azure Service Bus

There are two transport mechanisms for service bus

  • AQMP
  • AQMP over web sockets

The default is to use plain AQMP but this uses port 5671. Often times this port may be blocked by firewalls. You can switch over to using the websocket based version which uses port 443 - much more commonly open already on firewalls.

.NET Code

You just need to update the TransportType in the service bus set up

var client = new ServiceBusClient(Configuration["ServiceBusConnection"], new ServiceBusClientOptions
{
    TransportType = ServiceBusTransportType.AmqpWebSockets
});

Azure Functions

The simplest way of getting websockets to work on functions is to update the connection string to mention it

Endpoint=sb://someendpoint.servicebus.windows.net/;SharedAccessKeyName=SenderPolicy;SharedAccessKey=asecretkey;TransportType=AmqpWebSockets
2021-06-07

Add user to role in sql server

This can be done with

sp_addrolemember @rolename = 'role', @membername = 'security_account'

example

sp_addrolemember @rolename = 'db_owner', @membername = 'evil_hacker_account'

another example

sp_addrolemember @rolename = 'db_datareader', @membername = 'datafactory'

and another

sp_addrolemember @rolename = 'db_datawriter', @membername = 'asca_webapp'

Built in database roles are

db_owner Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database in SQL Server. (In SQL Database and Azure Synapse, some maintenance activities require server-level permissions and cannot be performed by db_owners.)
db_securityadmin Members of the db_securityadmin fixed database role can modify role membership for custom roles only and manage permissions. Members of this role can potentially elevate their privileges and their actions should be monitored.
db_accessadmin Members of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.
db_backupoperator Members of the db_backupoperator fixed database role can back up the database.
db_ddladmin Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.
db_datawriter Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.
db_datareader Members of the db_datareader fixed database role can read all data from all user tables and views. User objects can exist in any schema except sys and INFORMATION_SCHEMA.

db_denydatawriter Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database.

db_denydatareader Members of the db_denydatareader fixed database role cannot read any data from the user tables and views within a database.

2021-06-03

Sequences

Sequences are a handy feature in SQL server which provide an increasing, unique number. You wouldn’t typically use them directly but might use them under the covers in an identity. However from time to time they are useful when you need numbers but your primary key is a uniqueidentifier or you need two different ways of numbering records. I’ve been using them to associate records in a table into groups.

create SEQUENCE Seq_PermitNumber 
    start with 1 
    increment by 1

You can then use them like this

update tblManualPayment 
   set PermitNumber = next value for Seq_PermitNumber 
 where PermitNumber is null

This will give each record a unique permit number.

2021-05-20

Using Durable Entities

Durable entities are basically blobs of state that are stored somewhere (probably table storage). You can retrieve them and signal them with changes. They can be tied directly into standard Azure functions.

You build one as pretty much a POCO that looks like

[JsonObject(MemberSerialization.OptIn)]
public class DuplicatePreventor
{
    [JsonProperty("value")]
    public int CurrentValue { get; set; };

    public void Add(int amount) => this.CurrentValue += amount;

    public void Reset() => this.CurrentValue = 0;

    public int Get() => this.CurrentValue;

    [FunctionName(nameof(DuplicatePreventor))]
    public static Task Run([EntityTrigger] IDurableEntityContext ctx)
        => ctx.DispatchAsync<DuplicatePreventor>();
} 

In this example there is one piece of state: the CurrentValue. You can retrieve it using the Get() function. Add and Reset are other signals you can send to the state.

Using it in a function involves adding a client to the signature of the function like so

[FunctionName("ShopifyPurchaseWebhook")]
public static async Task<IActionResult> Run(
    [HttpTrigger(AuthorizationLevel.Function, "post", Route = null)] HttpRequest req,
    [DurableClient] IDurableEntityClient client,
    ILogger log)
{
        ...
}

Once you have the client you can retrieve an existing state by specifying an entityId and then getting it from the client

var entityId = new EntityId(nameof(DuplicatePreventer), webhook.order_number.ToString());
var duplicationPreventionEntity = await client.ReadEntityStateAsync<DuplicatePreventer>(entityId);

This gets you back a wrapper which includes properties like EntityExists and EntityState.

You can signal changes in the entity through an unfortunate interface that looks like

await client.SignalEntityAsync(entityId, "Add", 1);

That’s right, strings are back in style.

Gotchas

If you create the durable entity in your function and then request it’s value you at once you won’t get the correct value - you just get null. I’d bet they are using some sort of outbox model that only sends data updates at the end of the function execution.

2021-05-19

Advanced Web Application Firewall Rules in Azure with Terraform

If you’re creating an Application Gateway in Terraform for Azure you’re using this resource azurerm_application_gateway. This resource allows for some basic configuration of the Web Application Firewall through the waf_configuration block. However the configuration there is very limited and basically restricted to turning it off and on and choosing the base rule set. If you want a custom rule then you need to break off the rules into a separate azurerm_web_application_firewall_policy. This can then be referenced back in the azurerm_application_gateway through the firewall_policy_id

You can use the advanced rules to set up things like Geographic restrictions. For instance this set of rules will block everything but requests from Canada and the US.

### Web application firewall settings
resource "azurerm_web_application_firewall_policy" "appfirewall" {
  name                = local.basename
  resource_group_name = var.resource_group_name
  location            = var.resource_group_location

  custom_rules {
    name      = "OnlyUSandCanada"
    priority  = 1
    rule_type = "MatchRule"

    match_conditions {
      match_variables {
        variable_name = "RemoteAddr"
      }
      operator           = "GeoMatch"
      negation_condition = true
      match_values       = ["CA", "US"]
    }
    action = "Block"
  }

  policy_settings {
    enabled = true
    mode    = "Detection"
    # Global parameters
    request_body_check          = true
    max_request_body_size_in_kb = 128
    file_upload_limit_in_mb     = 100
  }
}
2021-05-18

Importing an Encrypted Backup into Azure Managed SQL

Let’s say you’re moving an encrypted backup into Azure. The encryption was set up like this

CREATE CERTIFICATE BackupKey   
   ENCRYPTION BY PASSWORD = 'a password that''s really strong here'  
   WITH SUBJECT = 'test1backup',   
   EXPIRY_DATE = '20220101';  
GO  

Now we need to export this certificate which can be done with

BACKUP CERTIFICATE BackupKey TO FILE = 'c:\temp\backupkey.cer'
WITH PRIVATE KEY (
    FILE = 'c:\temp\backupkey.pvk',
    DECRYPTION BY PASSWORD = 'a password that''s really strong here',
    ENCRYPTION BY PASSWORD = 'A strong password for the certificate' )

Now we have two file which contain the public and private keys. We need to combine these into something that Azure Key Vault can understand and this something is a .pfx file. There is a tool called pvk2pfx which can be used for this task and it is found in the Windows Enterprise Driver Kit https://docs.microsoft.com/en-us/windows-hardware/drivers/download-the-wdk. It is also installed as part of visual studio. On my machine it was in C:\Program Files (x86)\Windows Kits\10\bin\10.0.17763.0\x86\pvk2pfx.exe

Run this command to combine them

& "C:\Program Files (x86)\Windows Kits\10\bin\10.0.17763.0\x86\pvk2pfx.exe" -pvk C:\temp\backupkey.pvk -pi 'A strong password for the certificate' -spc C:\temp\backupkey.cer -pfx c:\temp\backupkey.pfx

Next up we need to import this key into azure keyvault. This can be done using the GUI or the command line tools. Everybody likes a pretty picture so let’s use the Portal. Click into the key vault and then under certificates

Then click on Generate/Import and fill in the form there selecting the .pfx file created above.

The password will be the same one you used when exporting from SQL server. Once the certificate is imported it should be available to anybody or any application with access to certificates in key vault.

You can open up SQL Server Management Studio and in there add a new certificate selecting the certificate from the Key Vault connection

2021-05-11

JQ

This is a really nice tool for manipulating JSON on the command line. The syntax is, however, esoteric like you would not believe. Here are some cheats to help out

If you have an array and want to take just the object at a specific index

.[3]

which returns the 3rd element

If you want to extract a value from an array of objects then you can use

.[].LicensePlate

This works for multiple levels too so if you have nested objects you can

.[].LicensePlate.Province

Given an array where you want to filter it then you can use this

[ .[] | select( .LicensePlate | contains("PM184J")) ] 

To select a single field you could then do

[ .[] | select( .LicensePlate | contains("PM184J")) ] |  map( .LicensePlate)

If you want multiple fields built back into an object do

{LicensePlate: .[].LicensePlate, EndTime: .[].EndTime}
2021-05-10

Creating a Shortcut in Powershell

You can’t really create a shortcut in powershell directly but you can using the windows script host from powershell. For instance here is how you would create a new desktop icon to log the current user off.

$WshShell = New-Object -comObject WScript.Shell
$Shortcut = $WshShell.CreateShortcut("$home\Desktop\LogOff.lnk")
$Shortcut.TargetPath="C:\Windows\System32\shutdown.exe"
$Shortcut.Arguments="/l"
$Shortcut.IconLocation="C:\windows\system32\Shell32.dll,44"
$Shortcut.Save()

The icon here is taken from the long list of icons in Shell32.dll in this case it is the little orange key icon. These icons are going to be refreshed soon so your mileage may vary on them. I found the right icon by just google image searching shell32.dll icon and found a picture of some of the index numbers. They were 1 indexed so I had to subtract 1

2021-05-10

Setting Timezone from Powershell

This is pretty easy.

Set-Timezone -Id "US Eastern Standard Time"

You need to know the id of the timezone and you can figure that out using

Get-Timezone -ListAvailable
Id                         : Dateline Standard Time
DisplayName                : (UTC-12:00) International Date Line West
StandardName               : Dateline Standard Time
DaylightName               : Dateline Daylight Time
BaseUtcOffset              : -12:00:00
SupportsDaylightSavingTime : False

Id                         : UTC-11
DisplayName                : (UTC-11:00) Coordinated Universal Time-11
StandardName               : UTC-11
DaylightName               : UTC-11
BaseUtcOffset              : -11:00:00
SupportsDaylightSavingTime : False
...

You can also see the current timezone by running

Get-Timezone
Id                         : Mountain Standard Time
DisplayName                : (UTC-07:00) Mountain Time (US & Canada)
StandardName               : Mountain Standard Time
DaylightName               : Mountain Daylight Time
BaseUtcOffset              : -07:00:00
SupportsDaylightSavingTime : True