In PowerShell you know that you can import the module SQLPS when the tools are installed for use on the machine. When you import the module you are dumped to a folder of SQLSERVER:\ > and it automatically uses SSPI or Windows Authentication. Many users would like to be able to use SQL Authentication to access the provider. I ran across this question at a SQL Saturday in OKC #SQLSATOKC.  When you right click on an item in SSMS (Management Studio) you can almost always click “Start PowerShell” which will load SQLPS.EXE and dump you in the context that you are right clicking on.

Right click on Database and click Start PowerShell

Puts you in the context that you right clicked on.

Simple enough, you are now in the provider and in a pathlike structure to get to the items you want.  But you are still using Windows Authentication.  Let’s see what it looks like for the Drives as you can see it says SQLSERVER:\ indicating that it is a new drive.  Get-PSDrive will let you see what drives PowerShell knows about and which providers it is using.  FileSystem is the typical provider when thinking of drives, but there are other providers as you can see in the graphic below.

One thing to keep in mind when you use this provider as above, is that the objects and directories that you see in this provider may seem special or maybe they don’t, but they are the same objects if I were to use Management Studio to right click on the Server or the Database and choose properties. The objects are just represented in path like structures instead of objects in an object explorer.  Although when you look at the view of the Object Explorer, you can see a directory like tree. So not really that much different except there is less mouse clicking in the provider window of PowerShell.

Now lets get to the part where we find out how you can use the SQLPS provider with SQL authentication instead of Windows authentication. To do this little trick you will need to import the module as below in the graphic. The next step is to create a new drive with New-PSDrive and it’s parameters. See that we specify the name, PSProvider, Root and the Credential that we will use to SQL authenticate. One thing to note, you MUST go to the point of the Instance if you use PSDrive with SQLServer Provider.  You can go further than just the instance, but you must specify at least to the instance level in order to use the New-PSDrive to create a drive. Then you will use the Get-Credential in ( ) to execute the cmdlet to have you type in a credential that will be used to connect you to SQL Server. Once that is typed in and authenticated, it will create a drive with the credentials you specified if they are valid.

When you have created the drive with New-PSDrive, you can then CD to it or Set-Location (CD is an alias) to the drive by using the entire drive sequence (CD TEST:\).  This will put you at the root of the drive that we created with New-PSDrive. You will also notice in my case that when you use Get-PSDrive you will see that the drive has a +sa on the end, which indicates to you that it is using SQL authentication.

I hope that was helpful and we will see you out there and later I will post a way to save credentials in a file that no one else can use except the user that created it.