Whilst having a conversation with Chrissy LeMaire last week about using the SQL Provider. She asked if it could use SQL Authentication. I had no idea but said I would find out. This is how and what I did.

I am going to demonstrate this in Visual Studio Code with the PowerShell extension installed but I have also tested this in PowerShell version 5.1.14393.576 on Windows 10 and PowerShell Version 5.0.10586.117 on Windows 8.1 but it will work across other versions I believe

First we need to import the sqlserver module

Import-Module sqlserver

Then if we run Get-PSDrive we can see our SQL Server PS Drive

A quick dir and we can see what is in that drive

We can navigate this like it is a file system. For example, below I have changed
to the ‘SQL directory’ run dir and found the machine name ROB-SURFACEBOOK and then
changed to that ‘directory’ and dir to find the instances, navigated to the default
instance and run dir to see what is available

This would enable me to do this to get the database names for example

You can connect to remote SQL instances in the same manner by running

CD SQLSERVER:\SQL\SERVERNAME

However I don’t have permission using this account

We need to create a new PS Drive so first, as always, start with Get-Help

So we can create a new PS Drive with  a credential

Parameters
-Credential Specifies a user account that has permission to perform this action. The default is the current user.

Does it accept SQL Server?

Notes
New-PSDrive is designed to work with the data exposed by any provider. To list the providers available in your session, use Get-PSProvider.

Yes it does 🙂

So we can add a new PSDrive using

 New-PSDrive -Name SQL2016N1-SQLAuth -PSProvider Sqlserver `
 -Root SQLSERVER:\SQL\SQL2016N1 -Description 'This is the SQL2016N1 SQL `
PSDrive using the  SQL Authenticated login DemoLogin' -Credential Get-Credential

Which will prompt us for a credential. Once we enter the credential we will have our SQL Server drive for that instance

Except we won’t !! This is because the account that VS Code (or PowerShell) is running does not have permissions on the SQL Server. So I need to run VS Code (or PowerShell) as a user with those permissions by right clicking on the icon whilst holding shift down and clicking run as different user. Obviously you won’t need to do this if the current logged on user has permission on the remote SQL Server

and then enter the credentials of the user

Now that we are running as the correct user

We repeat the code from above and we will have our PS Drive

Nope 🙂 This time we get the following error

New-PSDrive : SQL Server PowerShell provider error: The root path for a drive with credentials needs to include the instance name.

which makes sense if you think about it as the credentials will only be valid at instance level

So we will add the instance name to the command. As this is the default instance we add DEFAULT

New-PSDrive -Name SQL2016N1-SQLAuth -PSProvider Sqlserver `
-Root SQLSERVER:\SQL\SQL2016N1\DEFAULT -Description 'This is the SQL2016N1 SQL `
PSDrive using the  SQL Authenticated login DemoLogin' -Credential Get-Credential

Success!! So we will be able to see it in our list of PS Drives using Get-PSDrive

and we can see further details like this

and we can navigate to it using cd NAMEOFDRIVE

and perform our tasks

If you take a look at the SQL Server you will see that all that is being run is T-SQL

Unfortunately you cannot use the persist parameter with a SQLSERVER provider to persist the mapping across sessions

This post has shown you how to set up SQL Server PS Drives using SQL Authentication. The important points are

• Use Get-Help New-PSDrive -ShowWindow for all the help and examples
• You must be running PowerShell or VS Code as a user with Windows Permissions on the SQL Server
• You must specify the instance name
• Use “New-PSDrive -Name PSDRIVENAME -PSProvider SqlServer -Root SQLSERVER:\SQL\SERVER\INSTANCE -Description ‘DESCRIPTION’ -Credential Get-Credential”
• You can then navigate the remote SQL Server like a file directory from the command line using SQL Authentication
• It’s just running T-SQL against the instance