Query SQL Server using Powershell to output a CSV

Introduction

The reason I’m writing this is because I was in a situation where I needed some information and couldn’t find it anywhere on Google.

TLDR: if your Windows account/user has access to the SQL Server, you do not need to specify username/password in your script (as long as your script is running under that Windows account/user).

Problem

I needed to pull data from an onsite SQL Server, however, there was one major limitation: I couldn't access the server directly.

Unbenounced to me, the SQL Server I was trying to access was only accessibly through one machine. I had to run a Powershell script on that machine to query the SQL Server and output the data to a CSV file. From there, I would move the CSV file to a virtual machine where my project was running.

I’m not the best when it comes to Powershell, nor am I looking to be. At the time, I just needed a script to query the SQL Server and output a CSV file, simple enough, right?

But as things go, everything I searched up seemed to be the answer and when I went to run it, it failed. Even worse, I didn't know why. Luckily I ran across the following error:

The term ‘Invoke-Sqlcmd’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

and after Googling it, I knew I was on the right track.

Solution

I found a Stack Overflow page that explained that the module I was trying to use wasn’t installed on my machine. DUH!

Install-Module sqlserver

The next piece of information that I found was on a random forum. It mentioned that you only need to include the Username and Password in the script if your account is a SQL Server account. My service account was just a Windows account/user. So I made the adjustment and ended up with the script below.

It might seem simple, but it was a struggle to figure it out.

'Invoke-Sqlcmd' is not recognized as the name of a cmdlet