Getting Started Querying your Azure SQL Database in PowerShell

By:   |   Comments (2)   |   Related: > Azure SQL Database


Problem

One way that organizations are storing data in the cloud is by transferring typical database services to cloud providers. What once meant building an entire physical server, patching it, installing software like Microsoft SQL Server, tuning it to meet performance SLAs and managing it now is as simple as a couple keystrokes or mouse clicks to get access to a high-performance SQL database. The organization can now focus on developing a high-quality software product without having to worry about doing much managing of the backend.

One of the most popular cloud services is Microsoft Azure. Azure, although #2 in the cloud market, has been catching up to AWS as of late and now has a full lineup of cloud services. One of those cloud services is called Azure SQL Database. As you might have guessed, this is Microsoft's solution to providing a SaaS option for Microsoft SQL Server environments. It allows an organization to simply provision a database; not an entire server or SQL instance. Once an Azure SQL database has been provisioned and data is stored in it's tables, it can then be managed just like an on-premises SQL database through tools like SQL Server Management Studio. This works, but what if you need to routinely extract data from this database or the data inside is part of some external process that you need to automate? Typical GUI access won't cut it. This is when you need to focus on automation tools like Windows PowerShell.

By creating a PowerShell script just one time to access your Azure SQL database, this script will allow you to then plug this functionality in anywhere you'd like. To do this, you'll need to get your PowerShell code written and tested. This is what we'll be working on in this tip.

Azure SQL databases are essentially treated as equal to your traditional SQL databases. You can manage them exactly as you can your own on-premises SQL databases through SQL Server Management Studio. To manage both traditional SQL databases and Azure SQL databases through PowerShell requires a PowerShell module; the sqlps module. Although included with the SQL Server Management Studio already, there are times when you can't depend on the full software of Management Studio being installed. In this case, you'll need to install the just the bits you need.

Solution

To query any SQL database from PowerShell you need to have the SQLPS module installed along with a few supporting packages. I'm going to be focusing on the SQL Server 2012 packages in this tip. These will allow you to query your Azure SQL database. You will need:

Once you've got all three SQL components installed, it's now time to setup the Azure PowerShell prerequisite; the Azure PowerShell module. I'm not going to go through the step-by-step in this tip. Refer to the links for exact instructions.

By this point, we now have the majority of the functionality available to query Azure SQL databases. It's now time to connect to our Azure subscription and associate our account in the current PowerShell session. To do this, we'll use the Add-AzureAccount cmdlet. Simply calling this by itself brings up a graphical box that prompts you to input your Azure email and password.

Sign in to Microsoft Azure PowerShell

Once successful, you should then receive output that displays your user ID, subscription ID and tenant ID. If you see this, you've authenticated successfully!

Your user ID, subscription ID and tenant ID

Although this next step might have been set for other reasons, you must have a server firewall rule setup to allow incoming SQL connections from your client to your Azure SQL database. Again, you can do this via the Azure portal, but a couple lines in PowerShell will do it just as easily.

To do it via PowerShell, you'll need your Azure SQL database server name. To get this, you can use the Get-AzureSqlDatabaseServer cmdlet. You can see here that I have a single server called adamazuresql that I will need to use.

Get-AzureSqlDatabaseServer

Get-AzureSqlDatabaseServer cmdlet results

Next, you'll need to create the rule to allow one or a block of IP addresses. If you'd like to simply allow the public IP address you're coming from (if the computer you're working on has a NATed IP address) you can use Invoke-WebRequest again to query an external web service and parse out the IP address.

$ipAddress = (Invoke-WebRequest 'http://myexternalip.com/raw').Content -replace "`n"

Next, we'll create the firewall rule using the New-AzureSqlDatabaseServerFirewallRule cmdlet. This is where you'll need to use the server name you found earlier, specify a rule name and specify the start and end IP address. In our case, they will be the same since we're just allowing a single IP address.

New-AzureSqlDatabaseServerFirewallRule -ServerName $serverName -RuleName 'ClientRule' -StartIpAddress $ipAddress -EndIpAddress $ipAddress

At this point, we have everything in place to begin querying data from our Azure SQL Server database. You'll now need your database name, the Azure SQL server instance name, the username and password with access to the database and the table name you'd like to query.

To find the database name you can pipe the output of Get-AzureSqlDatabasServer directly to Get-AzureSqlDatabase. You can see here that I'll be using a database name of myazuredatabase.

Output of Get-AzureSqlDatabasServer directly to Get-AzureSqlDatabase

You can find all of the other information required on the Azure portal. Once you have all of the criteria needed it's simply a matter of passing all of these parameters to the Invoke-SqlCmd cmdlet. This cmdlet comes from the SQLPS module we installed earlier.

$params = @{
      'Database' = 'myazuredatabase'
      'ServerInstance' = 'yoursqinstance.database.windows.net'
      'Username' = 'adam'
      'Password' = 'mysecretpassword'
      'OutputSqlErrors' = $true
      'Query' = 'SELECT * FROM Users'
}
Invoke-Sqlcmd @params

You can see from the above example that I'm passing the T-SQL query "SELECT * FROM Users" which is retrieving all rows from the Users table in the database myazuredatabase which is located on the yoursqlinstance.database.windows.net server instance using my pre-created username and password. This gives me an output that looks like the below screenshot.

Retrieve all of the rows from the users table

By using a single tool like PowerShell, you can access your data wherever it is stored. It doesn't matter if it's in the cloud in an Azure SQL database or in an on-premises MS-SQL server, MySQL or even inside an Oracle or Postgres database. Chances are there's a PowerShell module that allows you to hook into that data.

Next Steps
  • Check out all of the PowerShell tips on MSSQLTips.com to continue your learning.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Adam Bertram Adam Bertram is an independent consultant, technical writer, trainer, and presenter. Adam specializes in consulting and evangelizing all things IT automation.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Monday, September 26, 2016 - 7:37:13 PM - Adam Bertram Back To Top (43424)

 

Hmm..when you called Invoke-SqlCmd did you specify a database that exists?


Sunday, September 25, 2016 - 3:52:51 PM - Johan van den Brink Back To Top (43411)

 

Hi Adam,

 

I followed this for my own Azure database and get this error. Couldn't get around it. Any ideas?

Invoke-Sqlcmd : Database 'System' does not exist. Make sure that the name is entered correctly.

At line:1 char:1

+ Invoke-Sqlcmd $params

+ ~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException

    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

 

Thanks!

 

Johan















get free sql tips
agree to terms