Query Editor – Azure Portal

An interesting new tool (in preview) added to Azure SQL database. Now Azure portal provides flexibility to query azure sql database from a browser!! Interesting! you can simply connect to Azure portal from any browser, view the database and then launch this tool to get connected to the database.  When you are in support this is nice to have feature. If you are not in front of your system you can connect to the database to perform health, connectivity check etc.

There are few good things about this tool. Result pane works similar to SSMS, it allows to split query window and result window even to a full page. The next good one is it allows to search (case sensitive) the result set across all the columns which I don’t find even in SSMS grid mode.

How to Connect using Query Editor in Azure portal?

  • Connect to Azure Portal using your subscription email Id and Password
  • Browse to SQL Servers or to the SQL databases you want to query
  • Once you clicked on the database , in the top you can find a menu called ‘Tools’, click on it.

  • Now portal prompts you couple of tools out of which “Query Editor” is one of them. Click on it and it allows you the ability to query the database.

  • At this time it will authenticate you to the database. If the logged in account is part of Azure active directory admin for that SQL server then it authenticate automatically using AD credentials or else it allows to connect with any one of the following approach.
    • Active Directory single sign on
    • SQL server authentication
    • Active Directory password authentication

In my case my ID is part of AAD group so it authenticates automatically with my id and connects to the database.

  • I ran couple of statements to get the data . I expected two result set however it ended up with only one result set and the other one is vanished.  Since its in preview I assume this is a bug with the tool.

  • For success it shows the taskbar in blue color and for errors it shows the taskbar in red color which I liked it.

As of now it supports most of the commands. Don’t expect intellisense 🙂

Permission

Minimum permissions required to access this tool is as below.

Portal Access
The user need to part of Reader role either at subscription level or to the individual SQL server level. This is required to read the object details (in this case the SQL Server) to validate the names etc.
Database Access
The user should have minimum Connect and Select permission to the database

Pre-requisite

This tool requires “Allow Azure Services” firewall rule to be turned on or else it will fail to connect and end up with the below error.  By default this firewall rule is turned on however if you turned off for security reasons then this tool won’t work.

Possible Security Concern

Most of the organizations follow security standards and they don’t want the infrastructure folks to access their database content especially if the database has PCI data. Lets assume a scenario, as part of security standards the Azure co-administrators don’t have access to SQL Server so they cant connect to the database. However this tool allows co-administrators (even if someone compromises co-admin access) to change the Azure active directory administrator to their name or to a group and they can gain access instantaneously to the database, pull the required data. Once they are done obtaining the data they can revert back to the actual AAD admin which will go unnoticed (unless an auditing is in place and a review is done periodically). I felt this might be a possible security threat.

Limitations \ Expecting Feature

  • Currently this tool doesn’t allow to export the result set. It will be nice to have feature
  • Should provide flexibility to choose who can access this tool

Leave a Reply

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

*