Josiah Siegel JosiahSiegel

Function App Identity-based Authentication

Access keys are disabled in a storage account that a function app will read/write to.

It is important to make sure all the necessary files to test authentication locally are provisioned.

Key differences for Types of Shared Access Signatures	User Delegation SAS creation flow

	-- REQUIRES: https://gist.github.com/JosiahSiegel/2195295eb20bd865f3b8543d098f0c4d
	-- Keep two tables synchronized based on the primary key
	-- and compares all columns for updates.
	-- Use if unable to identify a suitable update column
	ALTER PROCEDURE [sync].[sync_tables_all_columns_sp]
	@source_db SYSNAME, -- The name of the source database
	@source_schema SYSNAME, -- The name of the source schema
	@source_table SYSNAME, -- The name of the source table
	@target_db SYSNAME, -- The name of the target database
	@target_schema SYSNAME, -- The name of the target schema

	-- Create a table variable to store the table names and synonyms
	DECLARE @TableSynonyms TABLE (
	TableName NVARCHAR(128),
	TableSchema NVARCHAR(128),
	SynonymName NVARCHAR(128),
	SynonymSchema NVARCHAR(128)
	);

	-- Insert the table names and synonyms into the table variable
	-- You can change the logic for generating the synonym names and schemas as per your requirement

	-- Used by: https://gist.github.com/JosiahSiegel/ff740303e462da2c25e53cd97cc4d380
	-- Keep two tables synchronized based on the primary key
	ALTER PROCEDURE [sync].[sync_tables_sp]
	@source_db SYSNAME, -- The name of the source database
	@source_schema SYSNAME, -- The name of the source schema
	@source_table SYSNAME, -- The name of the source table
	@target_db SYSNAME, -- The name of the target database
	@target_schema SYSNAME, -- The name of the target schema
	@target_table SYSNAME, -- The name of the target table
	@update_column SYSNAME = NULL, -- The name of the column that determines if the source row was updated

	SELECT
	'CREATE synonym [' + s.name + '].[' + t.name + '] for [' + DB_NAME() + '].[' + s.name + '].[' + t.name + ']'
	from sys.objects t
	inner join sys.schemas s
	ON t.schema_id = s.schema_id
	where t.type IN ('U', 'V', 'P')

	-- https://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519
	if exists (select * from dbo.sysobjects
	where id = object_id(N'[dbo].[F_TABLE_DATE]')
	and xtype in (N'FN', N'IF', N'TF'))
	drop function [dbo].[F_TABLE_DATE]
	GO
	SET QUOTED_IDENTIFIER OFF
	GO
	SET ANSI_NULLS OFF
	GO

	USE [master]
	CREATE SERVER ROLE [rw_all] AUTHORIZATION [sa]
	ALTER SERVER ROLE [bulkadmin] ADD MEMBER [rw_all]
	GRANT ALTER ANY DATABASE TO [rw_all]
	GRANT CONNECT ANY DATABASE TO [rw_all]
	GRANT CONNECT SQL TO [rw_all]
	GRANT VIEW ANY DATABASE TO [rw_all]
	GRANT VIEW ANY DEFINITION TO [rw_all]
	GRANT VIEW SERVER STATE TO [rw_all]
	GRANT SELECT ALL USER SECURABLES to [rw_all];

	org="myorg"
	repo="myrepo"
	workflow_name="My Workflow"

	runs="repos/$org/$repo/actions/runs"
	query=".workflow_runs[] \| select(.name == \"$workflow_name\") \| (.id)"
	gh api --paginate $runs --jq "$query" \
	\| xargs -n1 -I % gh api $runs/% -X DELETE