SBMTD Agency card server setup

[thekaveman]

Depends on #296

Based on notes from when we did this for MST.

Azure

• For prod, create service principal for upload job
  • https://learn.microsoft.com/en-us/azure/active-directory/develop/howto-create-service-principal-portal#app-registration-app-objects-and-service-principals
  • https://learn.microsoft.com/en-us/cli/azure/authenticate-azure-cli#sign-in-with-a-service-principal
  • https://learn.microsoft.com/en-us/rest/api/storageservices/authorize-with-azure-active-directory#permissions-for-calling-blob-and-queue-data-operations
• Create client secret for principal
  • Username is the Application (client) ID
  • Password is the secret
  • Take note of expiration date on the client secret
• Give role assignment in Azure portal to ETL service principal in prod as documented in infrastructure README

Agency card server

• Install Azure CLI
• Confirm login command:

az login --service-principal -u <object id> -p <secret> --tenant <tenant id>

• Confirm upload command:

az storage blob upload -f <file.txt> --account-name <account> \
  -c <storage container> --name <file.txt> --auth-mode login --overwrite

• Write the above commands to a file named load.bat
• Determine table structure and query for Agency card database, e.g.

SELECT U.MobilityPassId as sub, U.LastName as name, 'mobility_pass' AS type
FROM Users U INNER JOIN UserCredentials UC ON U.MobilityPassId = UC.MobilityPassId 
WHERE UC.Active = 1 AND U.LastName <> ''
ORDER BY U.LastName, U.MobilityPassId

• Use bcp to extract records into UTF-8 encoded (codepoint 65001) CSV with a header row in a file extract.bat:

bcp "SELECT ..." queryout data.txt -T -c -C 65001 -d <DATABASE> -S <SERVER> -t ,

echo sub,name,type >> temp.txt
type data.txt >> temp.txt
del data.txt
ren temp.txt data.txt

• Install hashfields (and potentially Python as well)
• Run hashfields, confirm hashed output, then add to a file transform.bat:

hashfields --alg sha512 --skip type -i <file.csv> -o <file.hashed.csv>

• Create etl.bat file to run all 3 steps in sequence

extract.bat && transform.bat && load.bat

• Create scheduled task to run etl.bat nightly

[thekaveman]

We were seeing the following error in running the bcp extraction script:

SELECT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. 
Verify that SET options are correct for use with indexed views and/or indexes on computed columns 
and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

This bcp flag may be relevant: https://learn.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-2016#-q

See also: https://dba.stackexchange.com/a/310575

[thekaveman]

❗ File encoding matters ❗

We must ensure the bcp command outputs a file encoded in UTF-8, which is the encoding expected by hashfields and eligibility-server.

By default bcp outputs an ANSI encoded file!

Use the -C option to specify a code page, where 65001 is the code page for UTF-8:

bcp "SELECT ..." queryout data.txt -C 65001 ...

[thekaveman]

Waiting to hear back from SBMTD about a network/firewall issue blocking the Azure CLI.

[thekaveman]

SBMTD was able to get passed the network/firewall issue and the az login command succeeded!

[thekaveman]

We met with SBMTD today and got the full end-to-end process working to bring Mobility Pass data from their server into Azure!

Marking this ticket as complete 👍 ✅