Art of the DBA Rotating Header Image

October 6th, 2015:

A Month of SQLPS: Of Drives and Shortcuts

When we think of file systems and drives, we know that we can navigate to almost anything in a network by using the Universal Naming Convention (UNC) path. This usually takes the following form: \\SERVERNAME\SharedFolder. Now, may be cumbersome to always type in this path or perhaps you want to always make use of this share, but commonly we will map these drives to a local drive letter. To do this in Powershell, we would use the New-PSDrive cmdlet:

#Mount a remote share to the Z drive 
New-PSDrive -Name Z -PSProvider FileSystem -Root '\\PICARD\C$\Backups'

Now here’s where things get cool. If providers are meant to give us an interface that behaves like a file system, doesn’t it stand to reason that we can use the provider to mount parts of SQL Server as their own drives? This opens up a new area of management of SQL Server using Powershell.

I’ve blogged before about using the SQL Provider and Central Management Server as a driver for running my Powershell scripts. I love this trick as it gives me a lot of flexibility for managing my environments. Unfortunately, it can get a little cumbersome always browsing down to that path. So let’s mount it as a drive and make our lives a little easier:

New-PSDrive -Name CMS -PSProvider SqlServer -Root ‘SQLSERVER:\SQLRegistration\Central Management Server Group\PICARD’
dir CMS:\
Get-PSDrive | Format-Table Name,Root -AutoSize

SQLPS-7-1
Now we have a handy and easy way to reference the servers in our CMS. As with any other mapped drive, this mapping is only good for as long as we are in this current Powershell session, but there are options in the New-PSDrive cmdlet that will allow us to persist this mapping globally, as well as define other options.

One of those other options is using a different credential to connect to the mapped target. Remember how yesterday we talked about the security context of the provider and how it passed through your current user? Well, by creating a new mapped PSDrive for a SQL component, we can connect to a database in the provider using a different context, including a SQL login.

Doing this takes a little more work, but doable. What we need to do is create a credential object for our SQL login, then create the New-PSDrive with that credential. Let’s try it out:

#Create a SQLTest2 login on the server and add it to AdventureWorks2012 db_datareader role
$pw = Read-Host 'Enter sqltest2 password' | ConvertTo-SecureString -AsPlainText -Force 
$cred = New-Object System.Management.Automation.PSCredential ('sqltest2',$pw) 
New-PSDrive -Name SQLTest2 -PSProvider SqlServer -Root 'SQLSERVER:\SQL\PICARD\DEFAULT\' -Credential $cred

Now if we do a lookup on the databases and check our extended events info, we should see exactly how we’re connecting to the information:

dir SQLTest2:\Databases

SQLPS-7-2
This clearly shows that any work we do through this new mapped drive is done via the context of the SQL login we created the drive with. While the default behavior is going to be using objects in the provider with our default Windows login, it is necessary to know that there you can connect to SQL assets using other logins and contexts. Hat tip to Shawn Melton(@wsmelton) for showing me this technique. His blog covers it in a little more detail along with some other tips and tricks.

Mapping drives can really help us customize our Powershell environment for how we want to manage SQL Server. Mapping components we use frequently can aid us in scripting and give us short cuts to the things we use frequently. It is also good to know that these tools also provide different paths for security management while maintaining the integrity of our SQL servers.

Next up, we’ll look at some practical applications for using the provider. We’ll dip our toe a little into the SMO, but we should be able to keep it pretty basic. There’s a lot of depth to the provider, so it is easy to get lost. The focus here should be understanding the foundation, allowing you to build on it for your own scripts and processes.