Using PowerShell to Manage Windows Azure SQL Databases

In our recent articles published on this forum we have been discussing characteristics of the Microsoft Azure SQL Database offering. So far, we have been dedicating our attention to the process of its deployments (mainly by importing schema and data from an on-premises environment) and applying initial settings critical from the security standpoint (controlling, in particular, authentication, authorization, and firewall access). While we have occasionally employed Windows PowerShell to facilitate some of the relevant configuration tasks, we have not yet provided a comprehensive overview of its capabilities in regard to managing SQL Databases (or, in general, cloud-resident resources). Now it is time to address this unintended oversight.

Windows PowerShell primary support for managing Microsoft Azure is implemented in the form of three Azure PowerShell modules, namely:

  • Azure – the primary focus of our presentation. It currently (i.e. in version 0.8.3) contains 393 cmdlets (as you can verify by examining the outupt of Get-Command -Module Azure cmdlet or by browsing through Azure Cmdlet Reference).
  • AzureResourceManager – relevant when dealing with Azure Resource Groups, which represent arbitrarily defined, logical collections of interdependent cloud services. At this point, this feature is in the preview stage and offers relatively limited functionality, so we will be deferring its coverage to one of our future articles.
  • AzureProfile – intended for handling subscriptions and accounts, as well as module management (more specifically, facilitating switching between the other two modules, since they are not supposed to be used simultaneously within the same PowerShell session).

One of the benefits of using PowerShell cmdlets is the layer of abstraction, shielding non-developers from having to deal with relatively complex Microsoft Azure Service Management APIs (which cmdlets rely on in order to produce the desired outcome). In general, these take the form of REST (Representational State Transfer) operations, performed over SSL with mutual X.509 v3 certificate based authentication. However (as we will discuss in more detail later), it is also possible to invoke Azure REST API directly (by leveraging the Invoke-RestMethod cmdlet) in scenarios where a particular programmable function does not have an equivalent PowerShell wrapper.

Microsoft Azure PowerShell modules are available via Web Platform Installer (currently in version 5.0), which also automatically downloads and installs all necessary prerequisites including Azure SDK and .NET Framework 4.5 (whenever applicable). Once the setup completes, launch a PowerShell session in the security context of an administrative account. This allows you (before you proceed with Azure specific tasks) to modify PowerShell script execution policy to a less restrictive (from the default) setting. The most common choice is RemoteSigned (permitting you to run your own custom scripts but requiring that all scripts originating from the Internet are signed by a trusted publisher), which can be assigned by running Set-ExecutionPolicy RemoteSigned –Force (it is possible to limit its scope to the current session by adding the -Scope parameter with the Process value). Alternatively, you have the option of enforcing the desired behavior via Group Policy, as described in the about_Execution_Policies TechNet article. Note that starting with Windows PowerShell 3.0, you no longer need to explicitly import individual modules by running the Import-Module cmdlet, although this behavior can be disabled if desired by modifying the $PSModuleAutoloadingPreference variable (refer to about_Preference_Variables for more detail).

In general, there are two ways of authenticating connections to an Azure subscription. The first one (referred to as the Azure AD method) relies on the Azure Active Directory to validate explicitly stated credentials, which can take the form of either your Microsoft account (formerly Windows LiveID) or an Organizational account (defined in Azure AD). The account you choose must be designated as either the Azure Service Administrator or a Co-administrator for the target subscription (you can manage these assignments via the Administrators tab of the Settings page within the management portal). It involves invoking the Add-AzureAccount cmdlet that triggers the display of a separate dialog box prompting for your username and the corresponding password. The obvious drawback of this approach is its lack of support for automation, since not only both values must be typed in interactively, but they need to be provided again after a 12-hour time window has passed. The alternative, which eliminates both of these deficiencies leverages certificate-based authentication and can be implemented in either of the following ways:

  • A subscription (formerly referred to as publishSettings) file – relies on the Azure’s built-in ability to generate management certificates, which become available for download in the form of XML formatted files with the PublishSettings extension that subsequently must be imported into the certificate store on a local computer that needs to establish secure communication with the cloud. This process consists of two separate steps. First you invoke the Get-AzurePublishSettingsFile cmdlet and specify an administrative account for a target subscription (along with the subscription if you manage multiple ones), which triggers the creation of a new certificate in the cloud (as you can easily verify by viewing the Management Certificates tab of the Settings page within the management portal) as well as prompts you to download the PublishSettings file containing the certificate’s private key (along with metadata identifying your subscription). At that point, you are ready to launch the Import-AzurePublishSettingsFile cmdlet with the -PublishSettingsFile parameter (designating the name and location of the downloaded PublishSettings file), which imports the certificate into your personal certificate store. In addition, the subscription settings (along with the certificate thumbprint) are automatically stored within the roaming portion of the user profile (by default, %AppData%RoamingWindows Azure PowerShell folder, although you have the ability to specify a different target by assigning its path to the -SubscriptionDataFile parameter) in the subscription data file WindowsAzureProfile.xml.

    This approach has two main benefits – it is very straightforward to follow and it provides persistent authentication mechanism that validates subscription access as long as the management certificate remains valid. Unfortunately it also has some drawbacks. Even though its original behavior (which with every invocation of Get-AzurePublishSettingsFile cmdlet automatically generated a new management certificate in every subscription administrated by the current user) has been changed (you are prompted to select a single subscription), there are some security and auditing implications that need to be taken into account. Subscription files constitute a potential vulnerability (since they essentially are equivalent to administrative credentials) and should be properly managed. In addition, tracking certificate generation is challenging considering that each administrator triggers it independently without any oversight, frequently leading to certificate sprawl, and meaningful analysis involves sifting through Azure Management Services operational logs and looking up certificate thumbprints. There is also a limit on the total number of certificates in each subscription (as well as the total number of certificates per administrative account) forcing you to perform occasional cleanup, although this has been a considerably less significant factor since its value was raised to 100. However, you have an option to further mitigate these issues by employing the second certificate-based authentication method (described below).

  • A custom management certificate – rather than relying on Azure to generate management certificates, you might want to create them within your organization and then upload them to your subscription. This can be accomplished either by employing your internal Public Key Infrastructure (PKI) or, in its absence, by leveraging the makecert.exe command-line utility. (In either case, you have to use X.509 version 3 certificates with exchange key type and 2048 bit key length). Such approach gives you the ability to control subscriptions and administrative accounts associated with each certificate. At the same time, you benefit from its automation friendly characteristics and arbitrarily set expiration date. You also do not have to deal with logistics around protecting files containing private keys, since by default they remain in the personal certificate store throughout the whole provisioning process.

    In order to obtain the makecert.exe utility, download and install Microsoft Windows SDK. Once the setup is completed, you will find the executable in the %ProgramFiles(x86)%Windows Kits8.1binx64 folder (assuming you are running a 64-bit operating system). From the Command Prompt (or PowerShell) window, run:

    makecert -sky exchange -r -n "CN=" -pe -a sha1 -len 2048 -ss My ".cer"

    where CertificateName is an arbitrary (preferably descriptive) character string that will be assigned to the certificate subject name and the corresponding .cer file hosting its public key. This will generate a self-signed certificate with signature hash algorithm set to sha1, RSA (2048 bits) public key and validity of 25 years, residing in the Personal certificate store of the user account in which security context this command is executed (if you copy and paste this command – rather than typing it yourself – and are presented with an error message complaining about Too many parameters, ensure that “-” characters in the command line are actually the minus signs). Before establishing an authenticated connection to your subscription based on the newly created certificate, the corresponding .cer file must be uploaded to Azure (by using the Upload button on the Management Certificates tab of the Settings page of the management portal).

    Once the matching private and public keys of the new management certificate are stored, respectively, in the user’s personal store and in the user’s Azure subscription, it is time to configure your PowerShell environment. This is accomplished by running the Set-AzureSubscription cmdlet, with the SubscriptionName, SubscriptionId, and Certificate parameters. The first of them is an arbitrary name that you can use afterwards to reference your Azure subscription (you might want to consider choosing a descriptive name if you manage multiple ones). The remaining two can be obtained directly from the Management Certificates tab of the Settings page of the management portal, by looking up values in the Subscription ID and Thumbprint columns, respectively (note though that you will need to derive the certificate object from its thumbprint, as we will demonstrate next). You will also find there the actual name of your subscription (the Subscription column). Assuming that these values are stored in the $subscriptionName, $subscriptionID and $thumbprint PowerShell variables, you will need to execute the following short script:

    $mgmtCert = Get-Item cert:\CurrentUserMy$thumbprint
    Set-AzureSubscription –SubscriptionName $subscriptionName `
    	–SubscriptionID $subscriptionID -Certificate $mgmtCert
    Select-AzureSubscription -Current $SubscriptionName

    The last cmdlet in our sample script identifies the subscription to be used for the reminder of the PowerShell session (which, as we assume in this example, is the one we just uploaded the management certificate to). You can also designate it to be the default subscription by leveraging Set-AzureSubscription cmdlet followed by the DefaultSubscription parameter with the value set to its name (you can accomplish the same outcome by adding the Default parameter to the Select-AzureSubscription cmdlet). Such subscription automatically becomes the current one at the beginning of each PowerShell session, eliminating the need for repetitive execution of the Select-AzureSubscription cmdlet (this is reflected by the change in the value of the <IsDefault> element in the aforementioned subscription data file %AppData%RoamingWindows Azure PowershellWindowsAzureProfile.xml from false to true). You can remove the default designation by executing the Select-AzureSubsciption cmdlet with the NoDefault parameter. Remove-Subscription allows you to remove references to the existing subscription from the current user’s PowerShell environment (this is reflected by removal of subscription metadata from WindowsAzureProfile.xml).

Keep in mind that switching from Azure AD to the certificate-based authentication might result in a conflict between the two (with the second one taking precedence and preventing successful connection due expiration of its 12-hour lifetime). Executing Remove-AzureAccount should help you resolve such issues. In our upcoming article, we will explore additional considerations relevant when managing Azure SQL Databases via PowerShell.

See all articles by Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles