27 Working in the cloud

To answer your burning cloud question: as of this writing, dbatools has limited support for cloud database services, and the support that we do provide is focused on Microsoft Azure. This is primarily because most of the core programmers on the dbatools team have access to Microsoft Azure, whereas our access to other cloud providers is limited. We are, however, open to community contributions for other cloud providers.

If you have SQL Server installed on a virtual machine on any cloud provider, including Amazon Web Services (AWS), Google Cloud Platform (GCP), Microsoft Azure, or other leading clouds, SQL Server will work pretty much like it does on-premises. Specialized database services such as Amazon’s Relational Database Service (RDS) or Microsoft’s Azure SQL Database and Managed Instances, however, do not behave in entirely the same manner as on-premises and require specialized programming to support them within PowerShell.

We do have plans to provide more in-depth Azure SQL Database support, and we’ll likely see this in an entirely new module that makes Microsoft’s Az.Sql as easy and fun to use as dbatools. Until then, this chapter will outline some of the ways that dbatools is currently being used in Microsoft Azure, and what we’ve done to ensure you can use a few fundamental commands within our toolset.

27.1 Connecting to Azure

Azure SQL Database and Managed Instances support a number of ways to authenticate with the target SQL Server instance, which you can see in the SQL Server Management Studio (SSMS) Connect to Server (shown in figure 27.1) and Azure Data Studio (ADS) dialog boxes.

You use not only Active Directory and SQL Server Authentication but also Azure Active Directory (AAD) Universal with multifactor authentication (MFA), AAD password, and AAD integrated, giving you an incredible amount of authentication flexibility when managing servers in Azure. What makes this extra useful is that these authentication methods open up the possibility of managing SQL Server beyond interactive GUIs like SSMS. With MFA, for instance, you can use managed identities and dbatools to securely interact with a SQL Server instance within Azure Functions or a CI/CD pipeline.

Let’s start with one of the most straightforward authentication methods: using Azure Active Directory password. As seen in the next listing, connecting using Azure Active Directory password just requires that you pass the instance address, target database (if not master), and your AAD credential.

Listing 27.1 Connecting to a SQL Server instance using an AAD password

PS> $params = @{
  SqlInstance = "myserver.database.windows.net"
  Database = "mydb"
  SqlCredential = "[email protected]"
}
PS> $server = Connect-DbaInstance @params
PS> Invoke-DbaQuery -SqlInstance $server -Query "select 1 as test"

One thing you may notice that’s a little different is that, when connecting to Azure, we create a $server object first and reuse that over and over. This is the recommended way because it’ll save you from having to reauthenticate, and the commands will run faster. We don’t tend to do this on-premises because SQL Server pooling keeps connections fast enough. The one exception is when we connect to SQL Server containers, which require alternative credentials, so we reuse $server objects with containers simply for convenience.

Get-Help Connect-DbaInstance -Examples

Much like when using integrated authentication on-premises, if you’re using Azure Active Directory integrated authentication, you don’t have to pass a -SqlCredential at all. Just pass servername.database.windows.net using the -SqlInstance parameter and your database name to -Database, and you’re set.

27.2 Service principals and access tokens

Another way you can log in to Azure SQL Database and Managed Instances is to use service principals and access tokens. Service principals and access tokens are useful when SQL Authentication is not allowed, and they are used most often within CI/CD pipelines, as no user account passwords are exposed.

In terms of on-premises functionality, service principals can be thought of like service accounts, and access tokens can be thought of as one-time passwords. To learn more about service principals, you can visit Microsoft’s documentation using our shortlink at sqlps.io/sqlapp.

After creating your SQL service principal, you can use our New-DbaAzAccessToken command to generate an access token as shown in the next listing. The -Credential username is your application ID, and the password will be your application secret.

Listing 27.2 Connecting to a SQL Server instance using an access token

PS> $params = @{
  Type = "RenewableServicePrincipal"
  Tenant = "mytenant.onmicrosoft.com"
  Credential = "ee590f55-9b2b-55d4-8bca-38ab123db670"
}
PS> $token = New-DbaAzAccessToken @params
PS> $params = @{
  SqlInstance = "myserver.database.windows.net"
  Database = "mydb"
  AccessToken = $token
}
PS> $server = Connect-DbaInstance @params
PS> Invoke-DbaQuery -SqlInstance $server -Query "select 1 as test"

Again, note that we’ve created a reusable $server object, which is then used as the -SqlInstance target.

27.2.1 Using Az.Accounts

If your current workflow includes the use of Az.Accounts and Get-AzAccessToken, we support that scenario as well, as demonstrated next.

Listing 27.3 Using an access token generated by Get-AzAccessToken

PS> $azureAccount = Connect-AzAccount
PS> $azureToken = Get-AzAccessToken -ResourceUrl
➥ https://database.windows.net
 
PS> $params = @{
  SqlInstance = "myserver.database.windows.net"
  Database = "mydb"
  AccessToken = $azuretoken
}
 
PS> $server = Connect-DbaInstance @params
PS> Invoke-DbaQuery -SqlInstance $server -Query "select 1 as test"

There is no benefit to one method over the other—it is simply a matter of preference and convenience.

27.3 Supported commands

We’ve touched on Azure throughout the book and have seen how we can perform backups and restores using Azure Blob Storage. Although we don’t have an exhaustive index of commands that work in Azure at this time, we do plan to compile one at an unknown date in the future. This list will then be used to denote Azure support on our docs site, docs.dbatools.io, shown in figure 27.2, similar to the way we currently show which commands are supported by Windows, Linux, and macOS.

After reviewing our GitHub issues and polling the community on Twitter, we’ve found that dbatools is most often used to work directly with data in Azure. These commands, shown in table 27.1, work within Azure Automation Workbooks, Azure Functions, Azure DevOps Agents, and GitHub Actions.

Table 27.1 Non-exhaustive list of commands that work in Azure

Note that this is not an exhaustive list, but rather, the most commonly used commands at this time. With 600+ commands and counting within dbatools, it’s highly likely that many more work, especially in Managed Instances.

Import-DbaCsv is one of our favorite commands, so we’ve chosen this one to use as an example. In the following code listing, a connection is made to the mydb database on myserver.database.windows.net. Using that connection, customers.csv is imported into an automatically created table named customer. Then a query is performed to ensure that the data has been imported properly.

Listing 27.4 Importing a CSV file to Azure SQL Database

PS> $params = @{
  Type = "RenewableServicePrincipal"
  Tenant = "mytenant.onmicrosoft.com"
  Credential = "ee590f55-9b2b-55d4-8bca-38ab123db670"
}
PS> $token = New-DbaAzAccessToken @params
PS> $params = @{
  SqlInstance = "myserver.database.windows.net"
  Database = "mydb"
  AccessToken = $token
}
PS> $server = Connect-DbaInstance @params
PS> $params = @{
  SqlInstance = $server
  Database = "mydb"
  Path = "C:	empcustomers.csv"
  AutoCreateTable = $true
}
PS> Import-DbaCsv @params
PS> $params = @{
  SqlInstance = $server
  Database = "mydb"
  Query = "select * from customers"
}
PS> Invoke-DbaQuery @params

This is the basic setup for all commands used against Azure SQL Database and Azure Managed Instances.

27.4 The future

Soon, we plan to introduce an interactive pop-up window that will make it even easier to connect to Azure SQL Database using MFA.

27.5 Hands-on lab

• Try a number of commands against Azure SQL Database, and let us know which ones work for you at dbatools.io/issues.