Home > Archive

Using excel to access your Mobile Services data

If you have Microsoft Excel it’s easy to connect it to your Mobile Service SQL database to take a look at your data. This is great if you use Mobile Services for error reporting etc and want to perform some analysis on those numbers.

First, you need to get the server name for your Mobile Service. Login to the Windows Azure portal and locate your Mobile Service. Go the CONFIGURE tab and you’ll see the name of your SQL Database and it’s Server.


Server names are autogenerated random strings so you may want to make a note.

Now that you have the name of your server, we need to open up the server to accept connections from your machine. Navigate to the SQL Databases tab in the portal:


Once in the SQL Databases part of the portal, click on the SERVERS tab to list all your servers. Now locate the name of your server from earlier, and click on the first column (it’s name) to select the item.


Next, click on the CONFIGURE tab to configure your server. This will show you a list of allowed IP addresses. The SQL guys have made it easy for you, and provided a quick option to add your own IP to the list.

You should see your current IP (CURRENT CLIENT IP ADDRESS) and they have even provided a button that you can click to enable this IP.


Click the ADD TO ALLOWED IP ADDRESSES button and then press SAVE at the bottom of the screen.


We’re almost done with the portal. The last thing we need to do is get the full server name. Click the DASHBOARD tab


On the right hand side of the DASHBOARD you should see your administrator login name, the option to reset your password (in case you can’t remember it) and the MANAGE URL. This


If you already know you admin login and password (you do, right?) then all you need from here is the manage URL. Note that we don’t want the ‘https://’ scheme at the start of the name. Now, in excel open a new workbook. Click the Data tab and the From Other Sources dropdown and choose From SQL Server.


You’ll now be prompted for the server name.  Enter the name from the MANAGE URL field above but without the ‘https://’ scheme at the start.


Click Next; this will connect to your SQL database server. Now you need to choose the database (this is the first part from the SQL Databse field in the first screen shot above)


And now you can choose which tables you want to view.


When you’re happy with your choice click Finish (or you can click Next to configure this connection if you’d like to reuse it again in the future, I’ll save that as an exercise for the reader).

Finally, decide how you’d like the data to be imported, I’m going to go for a good old fashioned Table:


And finally you’ll be prompted for those credentials once more…


et Voila!


And don’t worry, any changes you make aren’t synced back to the server so you won’t break your live service.


Josh Post By Josh Twist
11:53 PM
28 Nov 2012

Got kids? Have an iPhone or iPad? Read this.

So you have children and you have an iPhone or iPad. Chances are you let your kids use the iOS device for some children’s games or you’re lying.

We’re in the former category. Recently I’d noticed that my children had been requesting permission to buy a lot of in app purchases. It’s usually for silly things like a new topping for this gingerbread making application or some new accessory for a virtual puppy. I always say no. In fact, I think the kids market in the iOS app store is becoming increasingly aggressive in this monetization approach.

Anyway, I was comfortable that there would be no in-app purchases going on without my prior approval because I am always prompted for my Apple ID and my kids do not know the password. Obviously.

All was well until, at the weekend, my son asked if he could have a free game he liked the look of? “Sure” I said, “but don’t ask me to pay for any in app purchases”. And I installed the game for him, entered my Apple ID and handed it over.

Then, later that evening, I received about 8 e-mails from the App Store with details of all my wonderful purchases I’d made that day. Totalling $167 in 15 minutes, my son had managed to break the in-app purchasing record. And all because of my false assumption that you had to enter your Apple ID with each purchase. Oh no. By default, you’re good for 15 minutes afterwards… enough time to splurge $167 on accessories for your new game.

At least he kept his promise to “not ask me to pay for any in app purchases”.

It turns out I should have enabled Restrictions which are off by default.

If you have children, go enable these now. Go to Settings > General > Restrictions

Photo Nov 19, 2 36 02 PM 

And set Require Password to ‘Immediately’

Photo Nov 19, 2 36 29 PM

Maybe even disable In-App purchases altogether. And remember, you should do this for all of your devices.

Gladly, I got in touch with Apple and they agreed that, since this was such an extraordinary burst of purchases unlike anything I’d done before, it was a suitable exception from their usage agreement (all sales are final) and they refunded the $167.



Josh Post By Josh Twist
12:10 AM
20 Nov 2012

BUILD 2012 – the week we discovered ‘kickassium’

[tl;dr – the code for my sessions is available further down in this post on my github]

It’s been a very long week, but a very good one. Windows Azure Mobile Services got it’s first large piece of airtime at the BUILD conference and the reaction has been great. Here’s just a couple of my favorite quotes so far from the week:

“Mobile Services is the best thing at BUILD, and there’s been a lot of cool stuff at BUILD” – Attendee in person

“I'm tempted to use Windows Azure #mobileservices for the back end of everything from now on. Super super awesome stuff. #windowsazureAndy Cross

“Starting #Azure #MobileServices with @joshtwist. I heard that in order to make it they had to locate the rare mineral Kickassium. #bldwin”- James Chambers


The BUILD team also hosted a hackathon and Mobile Services featured prominently. In fact two of the three winners of the hackathon was built on Mobile Services and you can watch the team talk about their experience in their live interview on Channel 9 (link to come when the content goes live). Again, some favorite quotes from the winning teams (some of which were mentored by the incredible Paul Batum):

“I was watching the Mobile services talk on the live stream, and as I was watching it I started hooking it up. By the time he finished his talk, I got the backend for our app done” – Social Squares, winner

“We got together on Monday and we did a lot of work – he did a service layer, I did a web service layer, we did bunch of stuff that would help [our app] to communicate, and then we went to Josh’s session… and we threw everything away and used Mobile Services. What took us roughly 2000 lines of code, we got for free with Mobile Services” – QBranch, winner


I had three presentations at BUILD, including a demo at the beginning of the Windows Azure Keynote – check it out. Mobile Services is 10 minutes in: http://channel9.msdn.com/Events/Build/2012/1-002 

I also had two breakout sessions and I’m pleased to announce that the code for these is now available (links below each session):

Developing Mobile Solutions on Windows Azure Part I

We take a Windows Phone 8 application that has no connectivity and uses no cloud services, to building out a whole connected scenario in 60 minutes. There’s a lot of live coding, risk and we even get (entirely by coincidence) James Chambers up on stage for some audience interaction that doesn’t quite go to plan! The code for this is up on github here (download zip).

Also, be sure to checkout my colleagues Nick and Chris’ awesome session which follows on from this: Developing Mobile Solutions on Windows Azure Part II.

Windows 8 Connectathon with Windows Azure Mobile Services

In this session, I build a Windows 8 application starting from the Mobile Services quickstart, going into some detail on authentication, scripts and push notifications including managing channels. The code for is up on github here (download zip) and – due to popular demand I created a C# version of the Windows 8 client. The Windows Phone client was pretty easy – I’ll leave that as an exercise for the reader.

Channel 9 Live

Paul and I were also interviewed by Scott Hanselman on Channel 9 Live – right after the keynote. We had a blast talking to Scott about Mobile Services and got to answer some questions coming in from the audience.

One of the outcomes of the Channel 9 interview was we promised to setup a Mobile Services UserVoice. We never want to break a promise on Mobile Services so here you go: http://mobileservices.uservoice.com – so please log your requests and get voting! Don’t forget about our forums and always feel free to reach out to me on twitter @joshtwist.

Josh Post By Josh Twist
3:16 AM
04 Nov 2012
© 2005 - 2018 Josh Twist - All Rights Reserved.