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
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.