A good security practice is to backup our SQL Servers to a network share but not allow users to be able to browse the share. How can we ensure that our SQL Server has access or test it if it has been set up by someone else?

Lets set this up.

First lets create a share for our backups

$FileShareParams=@{
Name='SQLBackups'
Description='The Place for SQL Backups'
SourceVolume=(Get-Volume-DriveLetterD)
FileServerFriendlyName='beardnuc'
}
New-FileShare @FileShareParams

This will create us a share called SQLBackups on the D drive of the server beardnuc, but without any permissions, lets grant permissions to everyone

$FileSharePermsParams=@{
 Name = 'SQLBackups'
 AccessRight = 'Modify'
 AccountName = 'Everyone'}
Grant-FileShareAccess @FileSharePermsParams

The share is created and I can access it and create a file

and as we can see the permissions are granted for everyone

OK, that’s not what we want so lets revoke that permission.

Revoke-FileShareAccess Name SQLBackups AccountName 'Everyone'

Now lets add permissions just for our SQL Server Service Accounts

$FileSharePermsParams = @{
Name = 'SQLBackups'
AccessRight = 'Modify'
AccountName = 'SQL_DBEngine_Service_Accounts
}
Grant-FileShareAccess @FileSharePermsParams 

$BlockFileShareParams = @{
Name = 'SQLBackups'
AccountName = 'SQL_DBAs_The_Cool_Ones'
}
Block-FileShareAccess @BlockFileShareParams

So how can I check that I have access from my SQL Server? Sure I could get the password of the SQL Service account and run a process as that account, not saying that’s a good idea but it could be done. Of course it couldn’t be done if you are using Managed Service Accounts or Group Managed Service Accounts but there is a way

Enter dbatools to the rescue The dbatools module (for those that don’t know) is a PowerShell module written by amazing folks in the community designed to make administrating your SQL Server significantly easier using PowerShell. The instructions for installing it are available here It comprises of 182 separate commands at present

There is a command called Test-SqlPath As always start with Get-Help

Get-Help Test-SqlPath -Full

Test-SqlPath -SqlServer sql2016n1 -Path \\beardnuc\SQLBackups

That’s good I have access, lets back a database up

Backup-SqlDatabase -ServerInstance SQL2016N1 -Database DBA-Admin -CopyOnly -BackupAction Database -BackupFile '\\BeardNuc\SQLBackups\Test-DBA-Admin.bak'

Ah, I cant show you as I don’t have access. Better get in touch with the data centre admin to check Luckily, I am my own data centre admin and have another account I can use

So what if we want to test all of our servers for access to the new share? I tried this

$SQLServers = (Get-VM -ComputerName beardnuc).Where{$_.Name -like '*SQL*' -and $_.Name -notlike 'SQL2008Ser2008'}.Name
Test-SqlPath -SqlServer $SQLServers -Path '\\BeardNuc\SQLBackups'

$SQLServers=(Get-VM -ComputerName beardnuc).Where{$_.Name -like '*SQL*' -and $_.Name -notlike 'SQL2008Ser2008'}.Name
foreach($Server in $SQLServers)
{
$Test = Test-SqlPath -SqlServer $Server -Path '\\BeardNuc\SQLBackups'
[PSCustomObject]@{
Server = $Server
Result = $Test
}
}

 Get-Module dbatools

 Update-Module dbatools

Install-Module dbatools

Update-dbatools