How To Connect to SQL Server Database From PowerShell

PowerShell is a flexible tool that can be used for both data analytics and data orchestration. Of course, that data must come from somewhere. In this two-part article, I want to demonstrate how to use PowerShell to interact with SQL Server data.

Reading SQL Server data into PowerShell can be useful in any number of scenarios. For instance, if your organization maintains a SQL-based HR database, you could use PowerShell to orchestrate the employee onboarding process. Similarly, PowerShell can function as a reporting tool for business data.

• Part 1: Create a Sample SQL Server Database
• Part 2: Access the SQL Server Database From PowerShell

Related: Getting Started With REST APIs in PowerShell

As I’m sure you can imagine, each SQL Server deployment is unique, hence there is no one-size-fits-all technique for connecting PowerShell to SQL Server. To illustrate the connectivity process, I am going to begin by guiding you through the steps of setting up a SQL Server Express deployment and adding sample data to it. Once that data is in place, I will explain how to access it with PowerShell – and even use PowerShell to save new data to the database.

Create a Sample SQL Server Database

For this article, I have decided to use SQL Server 2022 Express. For those unfamiliar with SQL Server Express, it is a specialized edition designed for desktop use or small server applications. I opted for this edition purely for the sake of simplicity, as I installed it on the same desktop where I will run my PowerShell code. However, with minor adjustments, the techniques I am going to demonstrate should apply to any currently supported SQL Server Edition.

Step 1. Deploy SQL Server

If you want to follow along with my steps, you can download SQL Server from this link: SQL Server Downloads. Choose the option for a basic installation, which provides a straightforward and automated installation process. This is the easiest way to get SQL Server up and running.

After completing the installation process, you will see a summary screen, similar to the one shown in Figure 1 below. As a best practice, I recommend taking a screenshot of this screen because it contains important information. If you can’t capture all the information within a single screenshot, consider copying the information to your clipboard and pasting it into a Word document.

Figure 1. Make note of the information provided after the installation has completed.

Once you have documented the information, click the Install SSMS button. This will prompt the Setup Wizard to open a web page where you can download the SQL Server Management Studio. The SSMS installation process is completely automated.

Step 2. Create a database

Now that SQL Server is up and running, our next step is to create a database that we can use with PowerShell. In real-world scenarios, you would typically connect to an existing database that belongs to one of your applications. However, for the sake of teaching you how to connect to and interact with SQL Server from PowerShell, I think it’s better to set up a sample database rather than practice on a production database.

To get started, launch the SQL Server Management Studio. When the tool starts, use the information provided to you at the end of your SQL Server deployment to connect to your SQL Server. You can see what this connection process looks like in Figure 2.

Figure 2. You will need to input the required information to connect to your SQL Server.

Once connected to the SQL Server, the SQL Server Management Studio will display a series of tabs (called the Object Explorer) on the left side of the screen. Right-click on the Databases tab and select the New Database command from the shortcut menu. You will need to provide a name for the new database you are creating. For this example, I am naming the database MyAppDB, as shown in Figure 3. Click OK to accept all default options and create the database.

Figure 3. Provide a name for the new database and click OK.

Step 3. Create a database table

With the database now created, expand the MyAppDB object (or whichever name you chose for your database) in the Object Explorer. Right-click on Tables and then select the New > Table commands from the shortcut menus, as shown in Figure 4.

Figure 4. Right-click on Tables and choose the New > Table commands.

At this stage, you will be prompted to specify the columns you wish to include in the table. For each column, you will need to provide a column name and a data type. Since the goal here is to demonstrate the fundamentals of accessing SQL Server from PowerShell, I don’t want to create anything overly complex. As such, I am going to create a text column named Month and an integer (Int) column named ItemsSold. You can see how this looks in Figure 5.

Figure 5. I have added two columns to my table.

After specifying the columns, close the table, and when prompted, provide a table name. In my case, I will simply name the table Table1.

Step 4. Add data to the table

The final step before we can move on to use PowerShell is to populate the table that we just created with some data.

First, refresh the Object Explorer. Right-click on the table you created (e.g., Table1) and select the Edit Top 200 Rows command from the shortcut menu. Now you can begin entering data into the table, as shown in Figure 6.

Normally, you would use an application to populate a database table, but since no application exists for this particular database, using SSMS is the next best option.

Figure 6. You can use SSMS to add data to the database.

With data now in the database, we can begin using PowerShell to analyze it. I will show you how in the second part of this article.

Access the SQL Server Database From PowerShell

Connecting to SQL Server from Windows PowerShell has long been considered a difficult task. The reason for this arose from having to use a .NET connection string to establish the connection. While this process does work, it is far from being intuitive. Thankfully, Microsoft has greatly simplified the process with the introduction of the Invoke-SQLCmd cmdlet.

Step 1. Allow remote connections

Before you can connect to the database with PowerShell, you must configure SQL Server to allow remote connections. To do so, follow these steps:

1. Open the SQL Server Management Studio.
2. Right-click on the server name (within Object Explorer).
3. Select the Properties command from the shortcut menu. This will open the Server Properties window.
4. Select the Connections tab.
5. Check the "Allow Remote Connections to this Server" checkbox.
6. Click OK.

 

Step 2. Install the PowerShell module

To access the sample database using PowerShell, you must install the SQL Server module. If you are running SQL Server locally on the system, the module might already be installed.

To install the module, open an elevated PowerShell session and enter the Install-Module cmdlet, followed by the module’s name, which in this case is SQLServer. You can see what this looks like in Figure 7. The error shown in Figure 7 occurred because the module was already installed on my system.

Figure 7. This is how you install the SQL Server module for PowerShell.

Depending on your operating system and where you are running SQL Server, you might also need the SQLPS module. You can import the module with the following command:

Import-Module SQLPS

If you receive an error message while importing the module, you may need to temporarily set the system’s execution policy to be less restrictive. You can see an example of this in Figure 8.

Figure 8. I had to set my execution policy to unrestricted before I could import the SQLPS module.

Step 3. Perform a SQL Query

Now that the SQL Server module is installed, you can access SQL Server from PowerShell. The old .NET-based technique required you to set up a connection string, establish connectivity to the SQL Server, and then enter a SQL Server query. However, the Invoke-SQLCmd cmdlet simplifies things. When using this command, you connect to the SQL Server and issue a query with a single command.

To demonstrate how this works, let’s read the contents of the table we created in the first part of this article. Here is the basic command structure:

$Data = Invoke-SQLCmd -ServerInstance  -Database  -Query “

To make this command work, you only need to plug in the details from your SQL Server instance. However, before you do, there are two potential “gotchas” you must know about:

1. Credentials for Remote Instances: If you are connecting to a remote SQL Server instance (one not installed on your local machine), you might need to supply credentials. The Invoke-SQLCmd accepts credentials from PowerShell using the Username and Password parameters. You can see how these parameters work by typing: Get-Help Invoke-SQLCmd. Alternatively, you can refer to the documentation on Microsoft’s official page.

2. Instance Name Format: The instance name might not be as it seems. If you think back to Part 1, SQL Server Setup reported the instance name as SQLEXPRESS. However, if you use SQLEXPRESS as the instance name, PowerShell will return an error saying that a network-related or instance-specific error occurred and that the server was not found or is not accessible. To resolve this, enter the instance name in the format \. For example, in my case, the instance name would have to be entered as Win11SQL\SQLEXPRESS, as shown in Figure 9. In that figure, you can also see that the data from the table was written to a PowerShell variable named $Data.

Figure 9. I have retrieved data from anSQL Server table.

Write data to SQL Server

You can also use PowerShell to write data to your database table using the Invoke-SQLCmd cmdlet. The key difference is that instead of using a Select statement, you would use an Insert Into statement. For example, if I wanted to insert a numerical value for July into my table, which is named Table1, the command would look like this:

$Data=Invoke-SQLCmd -ServerInstance Win11SQL\SQLEXPRESS -Database MyAppDB -Query “Insert Into Table1 (Month, ItemsSold) Values (‘July’,35)”

This command inserts the values July and 35 into the Month and ItemsSold columns, respectively. You can see the command in action in Figure 10.

Figure 10. You can insert data into a SQL table from PowerShell.

Keep in mind you can insert not only literal values but also values stored in variables into your database table.

What we have covered so far only scratches the surface of what’s possible with PowerShell and SQL. In fact, you can use PowerShell to build comprehensive database-driven applications and perform advanced data analytics. I will explore additional techniques in future articles.

About the author

Brien Posey is a bestselling technology author, speaker, and 21x Microsoft MVP. In addition to his ongoing work in IT, Posey has trained as a commercial astronaut candidate in preparation to fly on a mission to study polar mesospheric clouds from space.