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
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'
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 } }
Happy Automating
NOTE – The major 1.0 release of dbatools due in the summer 2017 may have breaking changes which will stop the above code from working. There are also new commands coming which may replace this command. This blog post was written using dbatools version 0.8.942 You can check your version using
Get-Module dbatools
and update it using an Administrator PowerShell session with
Update-Module dbatools
You may find that you get no output from Update-Module as you have the latest version. If you have not installed the module from the PowerShell Gallery using
Install-Module dbatools
Then you can use
Update-dbatools