Max Trinidad (B|T) blogged a wonderful series of 2 blogs and a related post about how to setup and work with Windows Azure SQL Database. If you are new to Windows Azure and would like to know how to setup your subscription and start using PowerShell to manage your Azure services, I would advice to read his blogs. At the very least you need to read part-1 of the series, before continuing.
- Part-1: Getting ready
- Trapping the IP Address for creating firewall rule
- 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.
Or, my own post on getting google search results using the custom search 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.
By profession, I’m a SQL Server Database Administrator. I love to poke my nose into different corners and see how stuff looks in there. I keep looking for new things to do as my mind refuses to settle on one topic.
SqlChow 
Leave a comment