UpdatedSeptember 13, 2023

Overview

Speakeasy have two databases installed to Azure:

Speakeasy – this contains a copy of the contents of all the SharePoint lists used by Speakeasy, so that they can be queried in a more complex manner using Structured Query Language (SQL). This database is populated using a scheduled Azure Runbook job – see Exporting SharePoint Lists to an SQL Database for more information.
SpeakeasyBankTransactions – this contains copies of bank and Xero transactions which have been generated using a PowerShell script. See Exporting RBS and Xero Bank Transactions to an SQL Database for more information

The databases were created by following instructions freely available on the internet. e.g.

Microsoft’s guide to creating an Azure database

Special care should be taken to create the database so as to minimise usage charges!!!

Connecting to the Azure Database

Connecting from a Laptop

Having configured the SQL database in Azure, a user can connect to it from SQL Enterprise Manager on a laptop or desktop computer.

To achieve this, the database ‘s server name is required. To find this, navigate to the Azure Home Page, then click on “SQL databases”. Pick one of the already configured databases, e.g. “Speakeasy” or “SpeakeasyBankTransactions”, then click “Properties”. The server name for the database can be found on this page. It will typically have a name which ends with “.database.windows.net”. Now from SQL Enterprise Manager, select “Connect…Database Engine”. A dialog will appear. See the image below:

In the server name text box, enter the server name obtained from the “Properties” page as described above. Enter your speakeasy user name. The database will use your account to authenticate with the database server in a similar way to the technique used to logon to Microsoft Teams. Once connected, the user should be able to view SQL tables and execute SQL queries or stored procedures in exactly the same way as if the database was installed locally.

Connecting From an Azure Runbook

The following excellent article demonstrates how to configure a database so that it can be accessed by entities which use an Azure automation account. This article was followed in order to configure the Speakeasy database, which stores an export of all the Speakeasy SharePoint lists, so that an Azure runbook could be executed to perform the export.

Executing an SQL Stored Procedure from an Azure Runbook

Basically, the process is quite simple – the SpeakeasyAutomation account, which has already been created, was granted access using the following SQL commands, exceuted against the Speakeasy database:

— Execute this against the Speakeasy database in the Azure db server
CREATE USER [SpeakeasyAutomation] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [SpeakeasyAutomation];
ALTER ROLE db_datawriter ADD MEMBER [SpeakeasyAutomation];
ALTER ROLE db_ddladmin ADD MEMBER [SpeakeasyAutomation];
GO

EXEC sp_addrolemember ‘db_datareader’, ‘SpeakeasyAutomation’
EXEC sp_addrolemember ‘db_datawriter’, ‘SpeakeasyAutomation’
EXEC sp_addrolemember ‘db_ddladmin’, ‘SpeakeasyAutomation’
GRANT EXECUTE ON SCHEMA::dbo TO [SpeakeasyAutomation]

These commands can also be found in the SQL script GrantAzureAutomationAccountAccessToSQLDatabase.sql which is located in the following repository: SpeakeasyScripts Repository

Connecting from local Powershell Scripts

If using Powershell on a local computer to connect to the Azure database, a connection string is required. Azure makes this easy by providing a set of connection strings. Navigate to the Azure Home Page, then click on “SQL databases”. Pick one of the already configured databases, e.g. “Speakeasy” or “SpeakeasyBankTransactions”, then click “Connection Strings”. The page can also be accessed from the “Propeties” page by clicking on the “Show database connection strings” link.

Example code can be found in the getDbConnection function which can be found in several of the PowerShell scripts (see the following repository: SpeakeasyScripts Repository). The function provides all the code to connect to an Azure database via a runbook or via a desktop/laptop computer. The connection makes use of the connection string plus some authentication token logic.

Installing SQL Client Components

It may be necessary to install SQL client components on a local desktop computer, though these weren’t required to be installed on one of the test laptops. If it does need to be installed, the following PowerShell commands may help:

From PowerShell:

confirm that nuget.org for v2 and v3 is in the list of package sources which are searched. This can be done using the following command:

Get-PackageSource

V2 and V3 nuget packages can be registered as a source using the following commands:

Double check to see we can find the SQL client package is available in nuget v2:

Find-Package Microsoft.Data.SqlClient -Source https://www.nuget.org/api/v2

Install SQL Client from nuget v2

Install-Package Microsoft.Data.SqlClient -SkipDependencies
Install-Package Microsoft.Identity.Client -Provider Nuget -SkipDependencies -Destination ~/PackageManagement/NuGet/Packages -Force