First things first, we need a Windows Azure subscription. Presently, you can register for a free trail at www.windowsazure.com.

Note: If you would like to use the management portal for Windows Azure to create the database, you can use the following tutorial.

Pre-Requisites

We need a few tools and a little bit of setup on the management portal before we can start with the automation part.

1. We need to rename our Windows Azure subscription from the default name it has. This makes it easier to manage your subscriptions, if you have multiple services.
2. We need to upload a certificate to the windows azure subscription.
3. Download latest version of Windows Azure PowerShell, from here. The link points to the standalone installer.
4. Download the latest Windows Azure SDK for .NET from here.

1. Renaming the subscription

The Windows Azure services have two kinds of accounts.

1. Windows Azure Account, through which resource usage is reported and services are billed. Each account is identified by an email account, and is associated with at least one subscription. The account owner monitors usage and manages billings through the Windows Azure Account Center.
2. Subscriptions, which governs access to and use of Windows Azure subscribed service. The subscription holder uses the Management Portal to manage services.

The reason for this kind of setup is because in a corporate enrollment, an account owner might create multiple subscriptions to give IT folk’s access to services. Since resource usage within an account is reported for each subscription, an organization can use subscriptions to track expenses for projects, departments, regional offices, and so forth.

This is explained in detail here.

You change your subscription details by using the following steps:

1. Sign into the Windows Azure Management Portal.
2. Click on ‘Account’ → ‘View my bill’; you maybe prompted to login as you will be accessing sensitive details.
   

   How to: Change Subscription details

    

3. Click on ‘Edit Subscription Details’, to change the name of your subscription.

HowTo: Change Subscription Name – 1

HowTo: Change SubscriptionName-2

2. Creating and uploading a management certificate

Certificates used in Windows Azure are x.509 v3 certificates and can be signed by another trusted certificate or they can be self-signed. For the purposes of this blog we will be creating a self-signed certificate using ‘makecert.exe’. Before we go further, let us check how different certificates are used in Windows Azure.

Windows Azure has 3 types of certificates:

1. Management certificates – Stored at the subscription level, these certificates are used to enable Windows Azure using various tools. These certificates are independent of any hosted service or deployment.
2. Service certificates – Stored at the hosted service level, these certificates are used by your deployed services.
3. SSH Keys – Stored on the Linux virtual machine, SSH keys are used to authenticate remote connections to the virtual machine.

Windows Azure uses certificates to identify a trust relationship i.e. the party to be trusted has the private key. Here is how the certificates are used:

• Management certificates (.cer certificate files): the client connecting the service needs to be trusted and has the private key. These permit clients access to resources in your Windows Azure subscription.
• Service certificates (.pfx certificate files): the service needs to be trusted by the client connecting to the service. For example, in an SSL secured service scenario they provide secure interactions for users of your web application or service.

Enough talk, let us start by creating a self-signed certificate. To create your own self-signed management certificates, open a Visual Studio command prompt as an administrator, and then run the following command:

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

If you are like me, you would get a doubt about what the parameters mean. Especially what ‘pe’ and ‘sky’ mean?

To put simply, unless we specify the -pe command line option to MakeCert, the certificate created can only be used to decrypt data on that machine. This is not an ideal scenario because, this would prevent any other machine from being able to decrypt the payload encrypted using the public key.

The option ‘sky’ specifies the type of the subject key. It accepts two arguments (signature|exchange). What these arguments mean is:

• ‘signature’ the certificate can only be used to sign a payload.
• ‘exchange’ the certificate can be used to sign AND/OR encrypt a payload.

Now that we have created our certificate, we need to export it in order to be able to upload the file to Azure. We can do this in a few easy steps:

• Start / run / certmgr.msc
• Navigate the tree hierarchy until you find the certificate you just created. (usually under Personal→Certificates).
• On the left pane right click on the certificate and select / all tasks / export.
• Select “No, do not export the private key”
• Click on next until you are asked for the Export path. Enter the desired export path.
• Follow the prompts and finish the export.

Uploading the certificate:

Management and service certificates can be uploaded through the Windows Azure Management Portal.

HowTo: Upload Certificate

3. Download latest version of Windows Azure PowerShell, from here. The link points to the standalone installer.

    Please download and install from the link given above.

4. Download the latest Windows Azure SDK for .NET from here.

    Please download and install from the link given above.

This is it. We are now setup to work with our azure subscription from within PowerShell.

References:

1. SQL Database – Documentation.
2. Manage Certificates in Windows Azure.
3. Create and upload a certificate for Windows Azure.
4. makecert option -pe.
5. makecert option -sky.

1. Part-1: Getting ready
2. Trapping the IP Address for creating firewall rule
3. Part-2: Getting to use PowerShell

If you are looking for really cool ways to interact with, or use ‘Invoke-RestMethod’ cmdlet then, I would suggest reading through Doug Finke’s (B|T) post on getting the newest powershell questions from stackoverflow.

• Getting newest powershell questions from stackoverflow

Or, my own post on getting google search results using the custom search API

• Google search using custom search api using REST API

Note: Both Doug and Max are PowerShell MVPs and have great blogs that have a ton of information on them.

If you are already set with a Windows Azure subscription and have your details at hand, let’s start the journey.

While I was reading the first part of Max’s blog, I was reading through msdn to catch up on some of the fundamentals of Windows Azure, why we need to upload a certificate, what are the different types of certificates and what SQL database was etc… you know, basic stuff; so that I do not make silly mistake and have to rework from scratch. So, anyways while I was url-hopping I found Management REST API reference. Since, I was fresh out of writing blog about REST API and Invoke-RestMethod, this piqued my interest a lot. But, I had to resist the temptation to branch out. After finishing through Max’s blog and exploring on my own for a couple of hours, I was able to setup a SQL database server and SQL database.

Although, it is easier to use the ‘Windows Azure PowerShell’ to setup and manage your Azure services; the allure doing it using web API finally caught up with me.

The Database Management API is a REST API. All operation requests are sent encrypted over the Secure Sockets Layer (SSL) and authenticated using X.509 v3 certificates. Database Management API requests can be initiated from within Windows Azure, or directly over the internet from any application that can send HTTPS requests and receive HTTPS responses. All requests are sent to the SQL Database Management Service on port 8443 using the following base URL: https://management.database.windows.net:8443

Since, I had the REST API URL reference I started working through the URLs they had given. I was naive to think that I could get away easily :-). One thing to remember however is that, ‘The SQL Database Management Service does not support asynchronous REST API calls’; do not know what it means but, it seemed important to point out (actually, just saw it on the reference page and was trying to be funny).

The approach is straight forward, there were a few gotchas and there is risque behavior with blatant disregard for security; flame shield on as this a proof-of-concept implementation. Keeping that in mind, let us start with:

Creating a new SQL Database Server.

The url for this is:

https://management.database.windows.net:8443/{SubscriptionID}/servers

Doesn’t look like much but, let us look at what the documentation says. It says, we need a pre-formatted request body that looks like this

<?xml version="1.0" encoding="utf-8"?>
<Server xmlns="http://schemas.microsoft.com/sqlazure/2010/12/">
  <AdministratorLogin>MyAdminAccount</AdministratorLogin>
  <AdministratorLoginPassword>MyAdminPassword</AdministratorLoginPassword>
  <Location>East US | North Central US | South Central US | West US | North Europe | West Europe | East Asia | Southeast Asia</Location>
</Server>

OK, not bad. We have here strings and we know PowerShell loves XML for more than one reason. Let us quickly hack a request togehter and throw it at the server.

#The setup
$subscriptionid = "xxxxxxxx-xxbx-xaxc-xxxx-xxxxxxxxxxxx"
$thumbprint = Get-ChildItem Cert:\CurrentUser\My  | Where-Object {$_.Subject -LIKE "CN=sqlchow80it032"} | Select-Object Thumbprint
$certificate = Get-Item Cert:\CurrentUser\My\$($thumbprint.Thumbprint)
$admindetails = Get-Credential -Message "What credentials do you want to use while accessing Windows Azure"
$location = "Southeast Asia"

#The request body, uri and headers
$xmlBody = @"
<?xml version="1.0" encoding="utf-8"?>
<Server xmlns="http://schemas.microsoft.com/sqlazure/2010/12/">
  <AdministratorLogin>$($admindetails.UserName)</AdministratorLogin>
  <AdministratorLoginPassword>$($admindetails.GetNetworkCredential().password)</AdministratorLoginPassword>
  <Location>$location</Location>
"@

$resturi = "https://management.database.windows.net:8443/$subscriptionid/servers"
[byte[]]$bytebody = [System.Text.Encoding]::UTF8.GetBytes($xmlBody)
$contenttype = "application/xml;charset=utf-8"
$headers = @{"x-ms-version" = "1.0"}

#Lift-off
[xml]$createresponse = $(Invoke-RestMethod -Method POST -Uri $resturi -Certificate $certificate -Headers $headers -Body $bytebody -ContentType $contenttype -Verbose).Remove(0,1)

$createresponse.ServerName
xmlns                                                       #text
-----                                                       -----
http://schemas.microsoft.com/sqlazure/2010/12/              vo0iyrxe4i

It looks like we have a lot going on in this one request. So, let us break it down by region.

• In the setup:
  • We are getting the subscription ID that uniquely identifies our subscription.
  • We are also getting the certificate that is used to authenticate our requests to the management service.
  • We then get the service administrator credentials and set the location where our server will be created.
• In the request body, uri and headers:
  • we are creating a request body as a here-string.
  • we are preparing the URL and then encoding the request body.
  • we are then setting the content type and headers.
    • One important thing to note is that the header is mandatory and it’s value is always ‘1.0’.
• In Lift-Off, we just send the request to the server and check the response.
  • yes, the lift off is not quite straight-forward you may wonder why the hell is this guy doing it like this. I will bring this up in a little bit.

As expected (lucky, there were no errors :)), we have a response that conforms with the expected response body:

<?xml version="1.0" encoding="utf-8"?>
<ServerName xmlns="http://schemas.microsoft.com/sqlazure/2010/12">
    TheNewServerName
</ServerName>

Our new server name is “vo0iyrxe4i”. As much as I would love to control the naming, at this point it is pretty random. The question that needs to be answered now is, how do we know if the database server was actually created. Let us find out.

Getting the SQL Database Servers

The request URL is the same as, the one we used for creating the Server.

https://management.database.windows.net:8443/{SubscriptionID}/servers

The major difference is that we do not have a request body and the response is going to look different.

The expected response is in the following format:

<Servers xmlns="http://schemas.microsoft.com/sqlazure/2010/12/">
  <Server>
    <Name>ServerName</Name>
    <AdministratorLogin>AdminAccount</AdministratorLogin>
    <Location>North Central US | South Central US | North Europe | West Europe | East Asia | Southeast Asia</Location>
  </Server>
</Servers>

The code for this would be:

$subscriptionid = "xxxxxxxx-xxbx-xaxc-xxxx-xxxxxxxxxxxx"
$thumbprint = Get-ChildItem Cert:\CurrentUser\My  | Where-Object {$_.Subject -LIKE "CN=sqlchow80it032"} | Select-Object Thumbprint
$certificate = Get-Item Cert:\CurrentUser\My\$($thumbprint.Thumbprint)
Invoke-RestMethod -Method Get -Uri "https://management.database.windows.net:8443/$subscriptionid/servers" -Certificate $certificate

VERBOSE: GET https://management.database.windows.net:8443/03xxxxxx-xxbx-xaxc-xxxx-xxxxxxxxxxxx/servers with 0-byte payload
Invoke-RestMethod : 
<Error xmlns="http://schemas.microsoft.com/sqlazure/2010/12/">
	  <Code>40643</Code>
	  <Message>The specified x-ms-version header value is invalid.</Message>
	  <Severity>16</Severity>
	  <State>1</State>
	</Error>

Oh! no. But, this was expected and you trudge back a ‘page-up’ you will see that the header was a mandatory parameter and had to have a specific value of 1.0 assigned to it. Let us behave and send the request in proper format.

$subscriptionid = "xxxxxxxx-xxbx-xaxc-xxxx-xxxxxxxxxxxx"
$thumbprint = Get-ChildItem Cert:\CurrentUser\My  | Where-Object {$_.Subject -LIKE "CN=sqlchow80it032"} | Select-Object Thumbprint
$certificate = Get-Item Cert:\CurrentUser\My\$($thumbprint.Thumbprint)
$headers = @{"x-ms-version" = "1.0"}

Invoke-RestMethod -Uri "https://management.database.windows.net:8443/$subscriptionid/servers" -Certificate $certificate -Headers $headers -Method "GET"

VERBOSE: GET https://management.database.windows.net:8443/xxxxxxxx-xxbx-xaxc-xxxx-xxxxxxxxxxxx/servers with 0-byte payload
VERBOSE: received 354-byte response of content type application/xml; charset=utf-8
?<Servers xmlns="http://schemas.microsoft.com/sqlazure/2010/12/">
  <Server>
    <Name>gobbl3go0k</Name>
    <AdministratorLogin>sqlchow</AdministratorLogin>
    <Location>West US</Location>
    <Features>
      <Feature>
        <Name>Premium Mode</Name>
        <Value>false</Value>
      </Feature>
    </Features>
  </Server>
</Servers> 

We got our response but, if you look closely the response has a ‘?’ on it. I did not find a reason why the response content has a ‘?’ (in this case it was 65279) in it. Reading through a few stackoverflow questions I thought adding the ‘@{“Accept”=”application/xml”}’ would solve it but, that results in an error:

Invoke-RestMethod : The 'Accept' header must be modified using the appropriate property or method.
Parameter name: name
At line:1 char:1
+ Invoke-RestMethod -Uri "https://management.database.windows.net:8443/$subscripti ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

So, as a temporary workaround; I put the response into a text file and then read from it. This time there is no ‘?’ and I get xml object.

Invoke-RestMethod -Uri "https://management.database.windows.net:8443/$subscriptionid/servers" -Certificate $certificate -Headers @{"x-ms-version"="1.0"} -OutFile "C:\temp\response.txt"
$data = [xml](get-Content C:\temp\response.txt)

$data.Servers.GetElementsByTagName('Server')
Name                          AdministratorLogin            Location                      Features
----                          ------------------            --------                      --------
gobbl3go0k                    sqlchow                       West US                       Features

$data.Servers.GetElementsByTagName('Server').features.feature
Name                                                        Value
----                                                        -----
Premium Mode                                                false

But, again we could put the response into a variable and used Remove() to get rid of the ‘?’, like we did for the create server part.

Note: if anyone knows why we have a ? in the response please let me know. I would really appreciate it.

After running the create scripts a couple of times, I ended up with three servers:

Invoke-RestMethod -Uri "https://management.database.windows.net:8443/$subscriptionid/servers" -Certificate $certificate -Headers @{"x-ms-version"="1.0"} -OutFile "C:\temp\response.txt"
$data = [xml](get-Content C:\temp\response.txt)
$data.Servers.Server

Name                          AdministratorLogin            Location                      Features
----                          ------------------            --------                      --------
gkbbwrq8ew                    sqlchow                       West US                       Features
hk5r8andyn                    sqlchow                       Southeast Asia                Features
vo0iyrxe4i                    sqlchow                       Southeast Asia                Features

The only other operation we can do with these database servers are delete and update. Both of them do not have response body :). Let us check these out.

Update administrator password

The URL for managing the password of a database server is a little different than the previous two we saw:

https://management.database.windows.net:8443//servers/?op=ResetPassword

The request body also differs a bit, as it only has the AdministratorLoginPassword node in it.

<?xml version="1.0" encoding="utf-8"?>
<AdministratorLoginPassword xmlns="http://schemas.microsoft.com/sqlazure/2010/12/">
  TheNewPassword
</AdministratorLoginPassword>

The drill is pretty much the same, with the only difference being that we do not have a response body. If we do not get any errors then we are good to go.

$subscriptionid = "xxxxxxxx-xxbx-xaxc-xxxx-xxxxxxxxxxxx"
$thumbprint = Get-ChildItem Cert:\CurrentUser\My  | Where-Object {$_.Subject -LIKE "CN=sqlchow80it032"} | Select-Object Thumbprint
$certificate = Get-Item Cert:\CurrentUser\My\$($thumbprint.Thumbprint)
$headers = @{"x-ms-version" = "1.0"}

$password = "v3ed!kip)wEr$hellP1cchi" #it just means that this guy is mad about powershell.

$servername = "vo0iyrxe4i"
$resturi = "https://management.database.windows.net:8443/$subscriptionid/servers/$servername" + "?op=ResetPassword"
$contenttype = "application/xml;charset=utf-8"
$xmlBody = @"
<?xml version="1.0" encoding="utf-8"?>
<AdministratorLoginPassword xmlns="http://schemas.microsoft.com/sqlazure/2010/12/">
    $password
</AdministratorLoginPassword>
"@
[byte[]]$bytebody = [System.Text.Encoding]::UTF8.GetBytes($xmlBody)
[xml]$delresponse = Invoke-RestMethod -Method POST -Uri $resturi -Certificate $certificate -Headers $headers -Body $bytebody -ContentType $contenttype -Verbose

VERBOSE: POST https://management.database.windows.net:8443/xxxxxxxx-xxbx-xaxc-xxxx-xxxxxxxxxxxx/servers/vo0iyrxe4i?op=ResetPassword with 165-byte payload
VERBOSE: received 0-byte response of content type

That’s it snap! the password is changed. But, if we want a response code then we can use Invoke-WebRequest which gives a status code for each request. We shall check how, in a bit after we look at deleting the server.

Delete SQL Database Server

The url for deleting a server is very simple.

https://management.database.windows.net:8443/{SubscriptionID}/servers/{ServerName}

The code:

$servername = "hk5r8andyn"
$resturi = "https://management.database.windows.net:8443/$subscriptionid/servers/$servername"
$contenttype = "application/xml;charset=utf-8"

$deleteresponse = Invoke-RestMethod -Method DELETE -Uri $resturi -Certificate $certificate -Headers $headers -ContentType $contenttype -Verbose
VERBOSE: DELETE https://management.database.windows.net:8443/xxxxxxxx-xxbx-xaxc-xxxx-xxxxxxxxxxxx/servers/hk5r8andyn with 0-byte payload
VERBOSE: received 0-byte response of content type

Remove-Item "C:\temp\response.txt"
Invoke-RestMethod -Uri "https://management.database.windows.net:8443/$subscriptionid/servers" -Certificate $certificate -Headers @{"x-ms-version"="1.0"} -OutFile "C:\temp\response.txt"
$data = [xml](get-Content C:\temp\response.txt)
$data.Servers.Server
Name                          AdministratorLogin            Location                      Features
----                          ------------------            --------                      --------
gkbbwrq8ew                    sqlchow                       West US                       Features
vo0iyrxe4i                    sqlchow                       Southeast Asia                Features

Using Invoke-WebRequest

Using Invoke-WebRequest is not much different than using Invoke-RestMethod. One important advantage of Invoke-WebRequest is that you get the status code and the raw content actually contains x-ms-request-id, which will come in handy in case there is an issue you have to contact ms support.

Invoke-WebRequest -Uri $resturi -Certificate $certificate -Headers @{"x-ms-version"="1.0"} -Method GET -Verbose
VERBOSE: GET https://management.database.windows.net:8443/xxxxxxxx-xxbx-xaxc-xxxx-xxxxxxxxxxxx/servers with 0-byte payload
VERBOSE: received 636-byte response of content type application/xml; charset=utf-8

StatusCode        : 200
StatusDescription : OK
Content           : <Servers xmlns="http://schemas.microsoft.com/sqlazure/2010/12/">
                      <Server>
                        <Name>gkbbwrq8ew</Name>
                        <AdministratorLogin>sqlchow</AdministratorLogin>
                        <Location>West US</Location>
                        ...
RawContent        : HTTP/1.1 200 OK
                    x-ms-request-id: be318ebc-bf8a-4e6d-bfdd-e204dd01efff
                    Content-Length: 636
                    Content-Type: application/xml; charset=utf-8
                    Date: Sun, 01 Sep 2013 14:51:11 GMT
                    Server: Microsoft-HTTPAP...
Forms             : {}
Headers           : {[x-ms-request-id, be318ebc-bf8a-4e6d-bfdd-e204dd01efff], [Content-Length, 636], [Content-Type, application/xml; charset=utf-8], [Date, Sun, 01 Sep 2013 14:51:11 GMT]...}
Images            : {}
InputFields       : {}
Links             : {}
ParsedHtml        : mshtml.HTMLDocumentClass
RawContentLength  : 636

If you reached here, I appreciate your patience and time. I would like to let you know that I am working on a new post using the HttpClient to manage these services. It may take sometime before it will be ready.