Two Small Tips for Working with Azure

Very quick blog post about two tips for working with Azure (hence the title of course). I recently started up a project where the data warehouse needed to be fully in the cloud. This was an Azure SQL Database, not Azure Data Warehouse. The size of the DWH was just not big enough (although Microsoft sales persons were apparently very keen to convince the client otherwise). Anyway, here goes:

1. When researching on the Web, make sure you are reading up-to-date information.

The cloud is changing. Fast and continuously. Chances are big that if you stumble upon a blog post, it’s already out-of-date. So check date of publication and maybe find multiple sources that confirm your research. For example, I did a quick research on how to connect to an Azure SQL Database with SSIS. On a Stack Overflow thread (with the exact same question), someone replied OLE DB doesn’t work and you need to use ADO.NET. Which means no lookup components. OK, ADO.NET it is and performance is acceptable. Until someone on Twitter mentioned OLE DB is supported for quite some time now. Whoops.

2. Make sure your client tools are up-to-date.

I was working on my own laptop on this project. In other words, I was not in the same domain as the client. A quick search (and thoroughly this time) learned me I could easily log into Azure Analysis Services using Windows Authentication where you specify your username and password. However, the option was missing in SQL Server Management Studio (the dropdown was disabled). Rage on.

no windows authentication with password for Azure SSAS

After a few frustrating moments I decided to update SSMS to the latest version. And lo and behold, the dropdown was enabled!

Azure SSAS with password in SSMS

A colleague of mine needed to log into the Azure SQL DB. I scripted his login, assigned a password et cetera (yes, scripted, because Azure SQL DB is still stuck in 2005 when it comes to user interfaces). I told him he needed to select the database in the SSMS because you can’t connect directly to the master database in Azure SQL DB (unless you’re an admin).

select database in connection SSMS

However, he still couldn’t log in. After some troubleshooting, we found out he was still using SSMS 2008. Whoops. You can probably guess it: upgrading to the latest version of SSMS solved the issue.

Koen Verbeeck

Koen Verbeeck is a Microsoft Business Intelligence consultant at AE, helping clients to get insight in their data. Koen has a comprehensive knowledge of the SQL Server BI stack, with a particular love for Integration Services. He’s also a speaker at various conferences.

Leave a Reply

Your email address will not be published. Required fields are marked *