How to Manage Microsoft Azure SQL Database Using PowerShell with REST APIs

In our previous article published on this forum, we have presented a somewhat unorthodox (from the perspective of a typical DBA or IT Pro – but certainly not that of a developer) method of managing Azure SQL Database that involves directly invoking REST APIs, providing an alternative to PowerShell-based scripting, which relies on Azure module cmdlets to run the underlying managed code. While considerably more challenging, such an approach offers more flexibility, facilitating functionality that is not directly exposed via Azure Portal or Azure PowerShell cmdlets. We have illustrated the principles of this methodology through a couple of fairly straightforward examples, enumerating all SQL Database servers in a given subscription and, subsequently, creating a new server (effectively, emulating Get-AzureSqlDatabaseServer and New-AzureSqlDatabaseServer cmdlets). Now, it is time to show some of the unique benefits of REST APIs by demonstrating their unique capabilities, not available via PowerShell.

In order to validitate our premise, we will start by comparing Azure SQL Database Management REST API Reference against Azure SQL Database Cmdlets to ensure that the operations we choose do not have their PowerShell equivalents, focusing for the time being on server level tasks (in particular, setting server administrator password and getting server event logs). As before, we will start by identifying individual components of a REST API call for each of them, including:

  • Request Headers: assigned the value of 2012-03-01 (translating into x-ms-version header, which represents the minimum API Service Management version that is necessary to carry out the intended operation).
  • Request: consisting of the following subcomponents:
    • Method: designating the HTTP verb, which dictates the action to be taken when invoking the API.
    • URI: taking the form of a parameterized URL (with individual parameters enclosed by braces), which is targeted when invoking the designated method.
    • HTTP Version: indicating compatiblity with a specific version of HTTP protocol specifications (1.1 in our case).
    • Body: representing the payload intended for data pertinent to the requested operation.

Let’s start with setting an administrative password (note that this task can also be carried out by clicking on the Reset Administrator Password link on the Dashboard page of each individual server in the Azure Management Portal). As per Microsoft Azure Online Documentation, our individual components will take the following format (where NewPassword is the one which you want to apply):

  • Request Headers: 2012-03-01
  • Request Method: POST
  • Request URI: https://management.core.windows.net:8443/{subscriptionId}/services/sqlservers/servers/{serverName}?op=ResetPassword
  • Request HTTP Version: 1.1
  • Request Body:
    <?xml version="1.0" encoding="utf-8"?>
    <AdministratorLoginPassword >
    NewPassword
    </AdministratorLoginPassword>
    

With these settings identified, we are ready to put together a PowerShell script that will reset the administrative password of an existing Azure-resident SQL Server instance within our current subscription. As before, we will assume that we already have configured certificate-based authentication (for details regarding this configuration, refer to one of our earlier articles). We will use individual variables to store values of parameters of the Invoke-RestMethod cmdlet representing components of the REST API operation (i.e. $method, $headers, $URI, and $body) as well as auxiliary parameters such as $serverName, $newPassword, $subscriptionID and $certificateThumbprint:

$method = "POST"
$headerDate = '2012-03-01'
$headers = @{"x-ms-version"="$headerDate"}
$subscriptionID = (Get-AzureSubscription -Current).SubscriptionId
$serverName = 'yourservername'
$newPassword = 'N3wpA$$w0rd'
$URI = "https://management.core.windows.net:8443/$subscriptionID/services/sqlservers/servers/$serverName?op=ResetPassword"
$certificateThumbprint = (Get-AzureSubscription -Current).Certificate.Thumbprint
$body = @"
<?xml version="1.0" encoding="utf-8"?>
<AdministratorLoginPassword >
$newPassword
</AdministratorLoginPassword>
"@
$contentType = "application/xml;charset=utf-8"
$setPassword = Invoke-RestMethod -Uri $URI -CertificateThumbprint $certificateThumbprint `
	-Headers $headers -Method $method -Body $body -ContentType $contentType -Verbose

We can track the progress of execution by observing verbose messages (representing the POST and receive operations).

Now, let’s turn our attention to the second operation, which allows you to extract content of the SQL Server event logs (note that you can obtain this data via Azure Portal, by logging on to the server and switching to the Events tab of its Administration workspace). As per Microsoft Azure Online Documentation, the request must include subscription ID, target SQL Server name, startDate and intervalSizeInMinutes, as well as an event type you are interested in. Effectively, this yields the following set of REST API invocation components:

  • Request Headers: 2012-03-01
  • Request Method: GET
  • Request URI: https://management.core.windows.net:8443/{subscriptionId}/services/sqlservers/servers/{serverName}/events?startDate={startDate}&intervalSizeInMinutes={intervalSizeInMinutes}&eventTypes={eventTypes}
  • Request HTTP Version: 1.1
  • Request Body: not applicable, since we are invoking the GET method

Here as well, in our PowerShell-based implementation, we will assume that we already have configured certificate-based authentication and use individual variables to store values of parameters of the Invoke-RestMethod cmdlet representing components of the REST API operation (i.e. $method, $headers, and $URI) as well as auxiliary parameters such as $serverName, $subscriptionID, $certificateThumbprint, $startDate (2014-08-20T00:00:00 in our case), $intervalSizeInMinutes (60), and $eventTypes (we will look for connection_successful):

$method = "GET"
$headerDate = '2012-03-01'
$headers = @{"x-ms-version"="$headerDate"}
$subscriptionID = (Get-AzureSubscription -Current).SubscriptionId
$serverName = 'yourservername'
$startDate = "2014-08-20T00:00:00"
$intervalSizeInMinutes = "60"
$eventTypes = "connection_successful"
$URI = "https://management.core.windows.net:8443/$subscriptionId/services/sqlservers/servers/$serverName/events?" +
        "startDate=$startDate&intervalSizeInMinutes=$intervalSizeInMinutes&eventTypes=$eventTypes"
$certificateThumbprint = (Get-AzureSubscription -Current).Certificate.Thumbprint
$contentType = "application/xml;charset=utf-8"
$events = Invoke-RestMethod -Uri $URI -CertificateThumbprint $certificateThumbprint -Headers $headers `
							-Method $method -Verbose -ContentType $contentType

As long as the operation is successfully initiated and completed, you should see messages indicating its progress (reflecting a 0-byte GET operation payload and non-zero application/xml response payload), which should resemble the following:

VERBOSE: GET https://management.core.windows.net:8443/subscripionID/services/sqlservers/servers/yourservername/
events?startDate=2014-08-20T00:00:00&intervalSizeInMinutes=60&eventTypes=connection_successful with 0-byte payload
VERBOSE: received 12126-byte response of content type application/xml; charset=utf-8

As indicated by the message above, the response body is delivered as a System.Xml.XmlDocument object. We can extract its content by querying the value of the OuterXml property of the XML-based $events object ($events.OuterXml), which should yield the output in the form:

<?xml version="1.0"?>
-<ServiceResources xmlns_i="http://www.w3.org/2001/XMLSchema-instance" >
   -<ServiceResource>
	<Name>60master2014-08-20T23:00:00connectivityconnection_successful0</Name>
	<Type>Microsoft.SqlAzure.EventLog</Type>
	<State>Normal</State>
	<SelfLink>https://management.core.windows.net:8443/subscripionID
		/services/sqlservers/servers/yourservername/events/60master2014-08-20T23:00:00
		connectivityconnection_successful0</SelfLink>
	<ParentLink>https://management.core.windows.net:8443/subscripionID
		/services/sqlservers/servers/yourservername</ParentLink>
	<DatabaseName>master</DatabaseName>
	<StartTimeUtc>2014-08-20T23:00:00</StartTimeUtc>
	<IntervalSizeInMinutes>60</IntervalSizeInMinutes>
	<EventCategory>connectivity</EventCategory>
	<EventType>connection_successful</EventType>
	<EventSubtype>0</EventSubtype>
	<EventSubtypeDescription>connection_successful</EventSubtypeDescription>
	<NumberOfEvents>61</NumberOfEvents>
	<Severity>0</Severity>
	<Description>Connected successfully to database.</Description>
	<AdditionalData i_nil="true"/>
   </ServiceResource>
</ServiceResources>

This concludes our overview of the managing SQL Server instances hosting Microsoft Azure SQL Databases by using Windows PowerShell and REST APIs. In our upcoming articles, we will explore more common database-level administrative tasks that can be implemented in the similiar manner.

See all articles by Marcin Policht

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles