Azure SQL Database Query Editor

3

June 24, 2020 by Kenneth Fisher

I was working on a couple of Azure databases the other day. One was an Azure SQL DB and the other Synapse (the database formerly known as Azure SQL DW). Now where this got interesting was that (as I understood it) the IP addresses used by Azure for these DBs was outside of our normal range so a new firewall rule was going to have to be put in place. Because of the processes we have in place this meant that it was going to be a couple of days before we could access these databases through SSMS. Not a huge issue for me, but the development team really kind of wanted to get started. Their Azure apps could connect but they needed a number of scripts run.

So what did we do? Well it turns out that there is a new Query Editor in preview that can be used with Azure SQL DB and Synapse. Although fair warning the location in the tasks list is different for each. (Took me a minute to find the Synapse one).

Azure SQL DB

Azure Synapse


Note: This is about half way down the list instead of near the top like with the Azure SQL DB.

When you select the Query Editor you have the option to connect as the account you are connected to Azure with, or to use a SQL Id. I’m selecting to continue as a SQL account. In large part because I couldn’t get my regular Microsoft account to work. Probably an AAD (Azure Active Directory) problem but I’ll figure it out later.

And once connected you see a very trimmed down query editor. It says for full capability please open SSDT. Probably because this is directed more at developers than DBAs?

Regardless, this is a useful little tool. It’s not overly complicated and it doesn’t have the features that a full blown SSMS (SQL Server Management Studio), SSDT (SQL Server Data Tools), or ADS (Azure Data Studio) has but it works. You can have multiple tabs open, it has basic intellisense, you have a very basic object explorer and the option to download the output. Even better, because this is part of the portal you can run it from a browser. I know most of us have SSMS or some other tool installed on the machines we work from, but every now and again you may need to run a quick query and not be on one of your normal machines.

As far as the firewall goes, I’m not 100% certain how it worked but as long as the firewall on the Azure side had rules that allowed me to connect, I didn’t seem to have to worry about the local firewall rules.

3 thoughts on “Azure SQL Database Query Editor

  1. Reitse Eskens says:

    If you can add your account as Admin to the Azure sql database “server”, you can login with your own credentials. If you want to add an Azure AD user, you’ll need the following, horrifying, code:

    CREATE USER [firstname.lastname_mailadress_extension#ext#@nameofsubscripition.onmicrosoft.com] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA=[dbo]
    GO

    I can give you a real example through dm if you need it.

    • I appreciate the advice 🙂 And I’ve used that command on more than one occasion. I had tried that unfortunately I don’t have AAD set up so it won’t work.

  2. k3baz says:

    your local firewall just sees the normal web traffic, probably on port 443 (HTTPS). The portals website is what connects to the Azure database.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,155 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: