Interacting with Azure SQL Using All Command Line Tools

Tuesday, January 23, 2018

Microsoft's collection of open source command line tools built on Python continues to expand. Let's take the scenario where I need to execute a query against an Azure SQL database. The first step is poking a hole in the firewall for my current IP address. I'll use the Azure CLI 2.0:

λ az login
To sign in, use a web browser to open the page https://aka.ms/devicelogin 
 and enter the code DRAMCY103 to authenticate.
[
  {
     ...subscription 1 ...
  },
  {
     ... subscription 2 ...
  }
]

For the firewall settings, az has firewall-rule create command:

λ az sql server firewall-rule create -g resourcegroupname 
   -s mydbserver -n watercressip --start-ip-address 173.169.164.144 
   --end-ip-address 173.169.164.144
{
  "endIpAddress": "173.169.164.144",
  ...
  "type": "Microsoft.Sql/servers/firewallRules"
}

Now I can launch the mssql-cli tool.

λ mssql-cli -S mydbserver.database.windows.net 
            -U appusername  -d appdbname

Auto-complete for columns works well when you have a FROM clause in place (maybe LINQ had it right after all).

mssql-cli at work

If I'm in transient mode, I'll clean up and remove the firewall rule.

λ az sql server firewall-rule delete 
   -g resourcegroupname -s mydbserver -n watercressip               

The mssql-cli has a roadmap, and I'm looking forward to future improvements.


Comments
Your Comment