In this article, I'll be showing you how to setup all prerequisites necessary to query an Azure SQL database in PowerShell. For demonstration purposes, I will be using PowerShell version 4 on a Windows 8.1 client.

Adam Bertram

Adam Bertram is a 20-year IT veteran, Microsoft MVP, blogger, and trainer. Adam is the founder of the e-learning tech screencast platform TechSnips. Catch up on Adam’s articles at adamtheautomator.com, or follow TechSnips on Twitter at @techsnips_io.

One of the many features of Microsoft Azure is its SQL database offering. It's now super-easy to bring up a SQL database without having to worry about bringing up a whole server and managing it.

You can query these databases via the SQL Server Management Studio software, just as you would an on-premise SQL database. But maybe you're working on an automation script. In this case, you're going to need to learn how to do this in PowerShell.

The prerequisites to execute SQL queries against your Azure SQL database in PowerShell consist of three software installations and opening up a firewall port on your Azure SQL server. Here you'll see how this is done.

Installing SQL Server Management Studio ^

If you already have the SQL Server Management Studio software installed, you can skip this step. To query any SQL database in PowerShell, regardless of it being in Azure or not, requires three software packages:

First, download these packages, accepting all defaults during the installation, and verify that they are installed. Once these are installed, you must also ensure that a firewall port is open, to allow your client to query the SQL database. You can do this via the Microsoft Azure portal, but since we'll be focusing on PowerShell in this article, we'll do this with the New-AzureSqlDatabaseServerFirewallRule cmdlet inside of the Microsoft Azure PowerShell module.

Installing Azure PowerShell ^

If you don't already have the Microsoft Azure PowerShell module, you can download it from Microsoft Github's repository. Once you have downloaded and installed its module, bring up your PowerShell console and run Add-AzureAccount.

This will associate your Azure subscription with your PowerShell session. Once you provide your Azure credentials, you're ready to begin running PowerShell commands against Azure.

Configuring the firewall ^

Creating the SQL database firewall rule in PowerShell requires using a few parameters of the New-AzureSqlDatabaseServerFirewallRule cmdlet. You'll need to know the Azure SQL Server on which you'd like to create the firewall rule, a name for the rule, and a range of IP addresses to which the rule should be open. Let's get each of these parameter values via PowerShell.

First, in this case, I want to open up the firewall rule only for the public IP address of the client on which I'm currently running. Since my IP might be NATed, I can't rely on the IP address of my current computer. Fortunately, there are a few websites that can return this information to me. One of them is http://myexternalip.com. We can either visit this URL via our browser, or we can just stay in PowerShell can get it via a script. Let's stick with PowerShell.

This small script snippet goes to myexternalip.com and scrapes the public IP address from the page. One parameter down! Next, we'll need to know the name of the Azure SQL Server on which the rule will be created.

If you don't know the name of your Azure SQL Server offhand, you can easily get this with the Get-AzureRmSqlServer cmdlet.

Getting the name of your Azure SQL Server

Getting the name of your Azure SQL Server

You can see that my SQL Server is in my westresourcegroup Azure resource group, and its name is adamssql. Two parameters down! Finally, I'll need to decide on a rule name. I'll choose to use a rule named MyFirewallRule.

The last thing to do is put them all together and pass them to the New-AzureSqlDatabaseServerFirewallRule cmdlet.

Querying the Azure SQL database ^

By this point, the hard work is done, and you're ready to send queries to your Azure SQL database. To do this, we'll use the Invoke-SqlCmd cmdlet in the SQLPS PowerShell module. This cmdlet requires five parameters: Database, ServerInstance, Username, Password, and Query.

The database parameter is the name of your database. To find the database name, you can use the Get-AzureRmSqlDatabase cmdlet.

Next, we need a server instance. This is an instance that comes from the output of the Get-AzureRmSqlServer you saw earlier. Simply assign a variable to the output, and you've got ServerInstance.

You'll now need the username and password you created at database creation time, as well as the T-SQL query you'd like to pass to it. Once you've got the values of all of these parameters, you'll then need to call Invoke-SqlCmd with the parameters to initiate the query:

If all goes well, this will send the query to your SQL database!

Join the 4sysops PowerShell group!

0
Share
6 Comments
  1. David 3 years ago

    Useful article Adam thanks. I'm new to SQL via PS and trying to understand the "best practice" way of querying SQL from PS. How does using these cmdlets differ from the following method where I pass my query to this function?

    Function LogToSQL($QueryInput)
    {
    $connectionString = "######.database.windows.net,1433;Data Source=######.database.windows.net;Initial Catalog=#####;Persist Security Info=False;User ID={#####};Password={#####};Pooling=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
    $connection = New-Object -TypeName System.Data.SqlClient.SqlConnection($connectionString)
    $query = $QueryInput
    $command = New-Object -TypeName System.Data.SqlClient.SqlCommand($query, $connection)
    $connection.Open()
    $command.ExecuteNonQuery()
    $connection.Close()
    }

    0

  2. Author
    Adam Bertram 3 years ago

    Hi David,

    That method works too. Although, as you can see using Invoke-SqlCmd eliminates the need for a lot of that code. It's a little easier to understand.

    0

  3. John Langston 3 years ago

    I have the need to run a single query on four hundred databases on separate sql servers in Azure.  So I am looking at a writing a PS script that builds an array of those four hundred database names, loops through them building and executing my query as it goes... Oh the joy!

    0

  4. Simon 11 months ago

    Hi Adam,

    I did everything as you described above and... it has failed :/ Although, my $serverInstance and @database objects are not null I receive something like this error:

    Invoke-Sqlcmd : Value cannot be null.
    Parameter name: ServerInstance

    Do you have any idea what could be wrong?

    1+

  5. Paolo 10 months ago

    Hey Simon and Adam:

    I had the same issue. It appears as though we require the addition of the specific FQDN to $params, please see my modification highlighted in green below.

    $params = @{
    'Database' = MyDatabase
    'ServerInstance' = $ServerInstance.FullyQualifiedDomainName
    'Username' = 'UsernameHere'
    'Password' = 'PasswordHere'
    'Query' = 'SELECT * FROM Users'
    }
    Invoke-Sqlcmd @params

    2+

Leave a reply

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

*

© 4sysops 2006 - 2019

CONTACT US

Please ask IT administration questions in the forums. Any other messages are welcome.

Sending

Log in with your credentials

or    

Forgot your details?

Create Account