Microsoft Azure error - "Cannot open database "master" requested by the login"

I've been busy setting up a database for a TSQL programming class that I'll be teaching in a few weeks. I've run into a few things that made me scratch my head. Here's one of them.

I created a sample database, a new login, and a user to go with the login. Then I gave the user read only access to the database, via sp_addrolemember.

When I used that login to connect via SSMS (File, Connect Object Explorer, enter the new login, also click on the Options button to specify the correct database), it appears to connect fine, and I see the connection.

However, when I expand the databases tab, and try to right-click the sample database to get the New Query option, I get the message "Cannot open database "master" requested by the login. The login failed.". So even though I was trying to open a query in the sample database, it tried accessing the master database.

Workaround:
This is a strange one, but the workaround is to right click on the connection object, and not the database. This will allow you to open up a new query, and will automatically put you in the database you specified in the connection options.

Doing this involves a bit of rethinking, because most developers who have worked extensively with SSMS are accustomed to right clicking on a database and selecting New Query to open a connection. But this won't work with Azure, unless you're also a user in the Master database.

Hopefully this write-up will save a few people some time when they go searching for details on this issue. Feel free to comment with more information.

Error "The database entered is not valid" when trying to set a default database, connecting to SQL Server Azure

Are you getting the error "The database entered is not valid" when trying to set a default database in the ODBC Data Source Administrator, DSN configuration? I did too, and it took me a while to figure out the root cause.

It turns out I was using the SQL Server driver (version 6.01.7601.17514) , when I should have been using the SQL Server Native Client 11.0 driver (2011.110.3000.00)

There are apparently a few other causes of this error as well, but switching to the more recent driver took care of the issue for me.

Review - Microstrategy Analytics Express

I'm doing a review of Microstrategy Analytics Express, and thought it might be interesting to jot down a few things I run into as I'm working my way through it. I'm connecting with a SQL Server Azure 11 database, onto which I've loaded the AdventureWorks 2012 sample database.

A few notes, in no particular order:

- I'm getting the below error a lot when I try to view the available tables:

TypeError - Cannot read property 'n' of undefined

I've narrowed it down, and it appears to happen whenever the table I'm trying to view has a column with a data type of HierarchyID, or any user-defined data type. Which, unfortunately, is about 90 percent of the tables in the AdventureWorks sample database. This is quite problematic. So far I haven't found a workaround.

- In the manual, there's a distinction made between the "Quick, easy-to-use dashboard", and the "Customizable, detailed dashboard". These phrases are repeated over and over again. For the sake of clarity, they really should have figured out a name for these 2 types of dashboards - for instance, Lite Dashboard and Superuser Dashboard. It's a little confusing to read these phrases again and again when two types of objects like this would be given different names.

- It looks like for text files, only comma delimited files are supported. That's a little surprising - most products have supported multiple delimiters for decades.

- It's not possible to edit a the underlying source of a dataset in Analytics Express. A dataset is an imported (not linked) set of data. Once you've imported, it you can't edit the underlying SQL in any way. You can, however, do things like put an expression on one of the fields. You also can't name a dataset. It's automatically given a name which is the name of the underlying table or tables with the date/time appended to it.

- When creating a new dataset, the field names are interpreted for you - Microstrategy tries to guess whether it's a metric, or an attribute. Frequently, however, it guesses wrong, and I haven't found a way to correct it. For instance, a column name of BusinessEntityId is determined to be a metric, and there's no way to reclassify it.

- When creating a new dataset, once you've chosen your fields, you'd expect them to actually show up when you click "Continue". But it doesn't. The fields that show up are the fields from the first dataset you've ever created. I was scratching my head over this, wondering what incorrect button I clicked on, or option I picked incorrectly. But I've redone it a few times now, and this is really what happens. If you actually want to see the resultset from the dataset you just created, you have to close down the grid that shows up by default, and open up the one you just created. It's confusing.

I'm looking forward to trying out the Desktop Developer option, which I'm assuming doesn't have some of the limitations of the online Analytics Express.