2021-09-17

Filtering Datatables

Years back there was this crazy way of dealing with data in .NET called a DataSet. DataSets contained DataTables which contained a combination of DataRows and DataColumns. It was all loosely typed and keyed with strings. Basically a database inside of your process. Even when they were cool I felt uncomfortable using them. Because I sometimes maintain legacy code I run into these monstrosities.

Today’s problem was that I needed to filter the contents of a table before bulk loading it. You can actually do simple filtering using a quasi-SQL like

   var dataRows = existingDataTable.Select("UserName = 'simon'")

This gives you back a collection of DataRows which I guess you could inset back into the table after clearing it of rows. To make this useful there is an extension method called CopyToDataTable in System.Data.DataExtension (be sure to include the dll for this). Using that you can get back a full data table

var dataTable = existingDataTable.Select("UserName = 'simon'").CopyToDataTable();

In that same package, though, is a much better tool for filtering: converting the table to an IEnumerable. You still need to use some magic strings but that’s somewhat unavoidable in data tables.

var dt = existingDataTable.AsEnumerable()
                    .Where(r => r.Field<String>("UserName") == "Simon").CopyToDataTable();

You can also do more advanced things like checking to see if something is a GUID

var dt = existingDataTable.AsEnumerable()
                    .Where(r => Guid.TryParse(r.Field<String>("Id"), out var _)).CopyToDataTable();
2021-08-31

SQL Mail

Did you know you can hook up your SQL server (or Managed SQL on Azure) to an SMTP server and use it to send email. Terrible idea? Yes, probably. I really encourage people not to build business logic that might require creating an email into stored procs. Required for legacy code? Yes, certainly.

You first need to tell SQL server how to talk to the mail server. This is done using the sysmail_add_Account_sp

EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'Database Mail Account',
    @description = 'SQL Server Notification Service',
    @email_address = 'SQLServer@somedomain.com',
    @replyto_address = 'SQLServer@somedomain.com',
    @display_name = 'Database Mail Profile',
    @mailserver_name = 'smtp.office365.com',
    @port = 587,
    @username = 'SQLServer@somedomain.com',
    @password = 'totallynotourpassword',
    @enable_ssl = 1;

With that in place you can set up a mail profile to use it

SELECT @sequence_number = COALESCE(MAX(profile_id),1) FROM msdb.dbo.sysmail_profile;

-- Create a mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'Database Mail Profile',
    @description = 'Sends email from the db';

-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'Database Mail Profile',
    @account_name = 'Database Mail Account',
    @sequence_number = @sequence_number;

-- Grant access to the profile to the DBMailUsers role
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = 'Database Mail Profile',
    @principal_id = 0,
    @is_default = 1 ;

You can then make use of sp_send_dbmail to send email

EXEC msdb.dbo.[sp_send_dbmail]
    @profile_name = 'Database Mail Profile',
    @recipients = 'simon.timms@somedomain.com',
    @subject = 'Testing db email',
    @body = 'Hello friend, I'm testing the database mail'
2021-08-31

Reauthenticate with Nuget

If you have a private nuget feed authenticated with a password chances are your password will eventually expire or change. For some reason Visual Studio and perhaps nuget under the covers aggressively caches that password and doesn’t prompt you when the password doesn’t work anymore. To change the password the easiest approach I’ve found is to use the nuget.exe command line tool and run

c:\temp\nuget.exe sources update -Name "Teamcity" -Source "https://private.nuget.feed.com/httpAuth/app/nuget/feed/_Root/SomeThing/v2" -UserName "simon.timms" -Password "Thisisactuallymypassword,no,really"
C:\temp\nuget.exe list -Source teamcity
2021-08-13

Which Key to use for Managed Identity in Keyvault

I have a terraform deployment which runs in azure pipeline. Azure pipelines is connected to Azure via a service connection. This service connection is registered as an application in the Azure AD of the Azure account. The problem I constantly run into is that I can’t remember which id from the application should be granted keyvault access so the build pipeline can read and write to keyvault.

resource "azurerm_key_vault_access_policy" "terraformaccess" {
  key_vault_id = azurerm_key_vault.keyvault.id

  tenant_id = local.tenant_id
  object_id = ???????????????????

  key_permissions = [
    "Get",
    "Create",
    "List",
    "Update",
    "Verify",
    "Delete",
    "WrapKey",
    "UnwrapKey"
  ]

  secret_permissions = [
    "Get",
    "List",
    "Set"
  ]

  storage_permissions = [
    "Get",
    "List",
    "Set",
    "Update"
  ]
}

Maybe the value is in the portal somewhere:

Nope.

It seems to be findable by doing either

Login-AzureRmAccount -SubscriptionId <your subscription id>;
$spn=(Get-AzureRmADServicePrincipal -SPN <the application id>);
echo $spn.Id

or

 az ad sp list --spn <the application id>

Then look for ObjectId

2021-07-30

Stop Terraform Managing State for a Resource

Say you want to keep a resource but you want to stop terraform from managing it. You can ask terraform to update its state to forget about it. In my case I want terraform to forget it managing my Azure Static Web App because Terraform doesn’t support all the options I need and will clobber the app.

I can run this

terraform state rm "azurerm_static_site.agentportal"

If I decide to start managing the state again I can just run a terraform import to manage it again.

2021-07-29

Storybook IFrame 404

If you’re running into a 404 on the IFrame on storybook the mostly likely cause is that your directory path to where storybook is contains some encoded characters. In this case we had a project called Agent%20Portal on disk. Renaming it to just AgentPortal fixed up storybook right away.

2021-07-26

Building an SQL Azure Connection String using terraform

If you provision a database using terraform you often find that you need to get that connection string into app settings or key vault or something like that. To do that you first need to build it because the outputs from the database resource don’t include it.

From the database you want to export

output "database_name" {
  value = azurerm_sql_database.database.name
}

Then when you actually build the string you want something like this:

database_connection_string    = "Server=tcp:${module.database.name}.database.windows.net,1433;Initial Catalog=${module.database.database_name};Persist Security Info=False;User ID=${var.database_user};Password=${var.database_password};MultipleActiveResultSets=True;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

Then you can push this to KeyVault or an App Service directly.

2021-07-26

Deploying App Settings to an Azure Static Web App

Static web apps are pretty cool but certain parts of them feel like they are still a little raw. It is a newish product so I can understand that. I just wish the things that didn’t get attention were something other that devops things. That’s mostly because I’m so big on builds and repeatable processes. Being able to set app setting is one of the things I think falls through the cracks.

The docs for statics web apps suggests two different ways of setting app settings. First through the portal which we can ignore right out the gate because it is manual. The second is through an az command that actually just exercises a REST endpoint. No Arm support, no terraform support, no bicep support, no azure powershell support… a long way to go.

The az command takes in a specially formatted json file. My databse and connection string variables are set up as outputs from my terraform. Once I have them imported into my Azure DevOps build pipeline I use powershell to build the template file like so:

$temp = gc api/api/local.settings.template.json | ConvertFrom-Json
$temp.properties.STORAGE_CONNECTION_STRING = "$(terraformOutput.storage_connection_string)"
$temp.properties.DATABASE_CONNECTION_STRING = "$(terraformOutput.database_connection_string)"
$temp | ConvertTo-json > api/api/local.settings.template.json

Now this needs to be passed up to azure using the AzureCLI task

  - task: AzureCLI@2
    inputs:
    azureSubscription: 'Azure Sub'
    scriptType: 'pscore'
    scriptLocation: 'inlineScript'
    inlineScript: |
        gc api/api/local.settings.template.json
        az rest --method put --headers "Content-Type=application/json" --uri "/subscriptions/6da8d6e6-41f1-xxxx-xxxx-xxxxxxxx/resourceGroups/dev-portal/providers/Microsoft.Web/staticSites/dev-portal/config/functionappsettings?api-version=2019-12-01-preview" --body @api/api/local.settings.template.json
2021-07-24

Vetur Warnings in Azure Static Web App

I have a static web app that has directories for the front end written in Vue and the back end written in Azure functions. When I open it in VS Code I get warnings that Vetur can’t find the package.json or tsconfig.json. This is because the Vue project isn’t at the project root. This can be fixed by adding, at the root, a vetur.config.js containing a pointer to the web project. With my web project being in web (creative I know) the file looks like

// vetur.config.js
/** @type {import('vls').VeturConfig} */
module.exports = {
    // **optional** default: `{}`
    // override vscode settings
    // Notice: It only affects the settings used by Vetur.
    settings: {
      "vetur.useWorkspaceDependencies": true,
      "vetur.experimental.templateInterpolationService": true
    },
    // **optional** default: `[{ root: './' }]`
    // support monorepos
    projects: [
      './web'
    ]
  }
2021-07-08

Enable TeamCity Symbol Server

First off a symbol server is a server which stores the symbols from a built binary so you don’t have to ship out PDB files with your compiled code to be able to debug it. You can hook up visual studio to search a symbol server when you’re debugging so that you can drop into code for something like a shared nuget package. Teamcity, as it turns out, has a plugin to support being a symbol server. Here is how you get started with it:

  1. Install the symbol server plugin by going to Administration > plugins > Browse plugins repository and search for symbol
  2. On your build agents install the windows debugging tools which are shipped as part of the Windows SDK. For windows 10 you can grab it here: https://developer.microsoft.com/en-us/windows/downloads/windows-10-sdk/ During the install you’ll be prompted for which components you want to install and you can just turn off everything but the debugging tools.
  3. Remember to restart your build agents so they can register the debugging tools as being installed. You can check by going to the build agent in teamcity. Click on parameters

    In there, at the bottom, you should find an entry for the debugger
  4. In the projects you want symbols for enable the symbol server feature
  5. In the build artifacts you need to ensure that both the PDB and the associated EXE or DLL are selected as artifacts.

That’s pretty much it. In your build now you should see a few log messages to let you know that the symbol server indexing is working

Now you can hook up Visual Studio to use this by going into settings and searching for symbols then paste the URL of the teamcity server with /app/symbols at the end of it into the box

Now when you’re debugging in visual studio you’ll have access to the symbols.