locked
Cannot backup SQL databases using System Account in Powershell RRS feed

  • Question

  • Hello Experts,

    I get an error "Invoke-Sqlcmd : The server principal "SERVERNAME\ACCOUNT$" is not able to access the database "<DBName>" under the current security context." when I try to backup SQL databases under the system account (using psexec). I am using powershell on a default installation of server 2019 and SQL. How can I backup the databases using powershell? What am I doing wrong?

    OS: Windows 2019 standard
    SQL: 2017 RTM

    Thanks!


    AJ

    Wednesday, April 24, 2019 1:52 PM

All replies

  • Without a script we cannot help.

    For issues with PsExec post in the PsExec forum.

    Note that the system account does not have access to a SQL database.  You must use a SQL login that has SQL backup rights on the target database.


    \_(ツ)_/

    Wednesday, April 24, 2019 2:20 PM
  • Hello,

    Thank you for your reply. It is working in earlier versions of SQL (e.g. 2008R2) and Windows (e.g. 2012R2). Script as follows:

    function Get-SQLPercentCheck{
        param([string]$db,[string]$hostname)
        try{
            $sqlPerf = Invoke-Sqlcmd -Query "use [$db] Dbcc sqlperf (logspace)" -ServerInstance $hostname
            [decimal]$dbpercent = $sqlPerf | ForEach-Object $_ {if($_."Database Name" -eq $db){$_."Log Space Used (%)"}}
            return $dbpercent
        }
        catch{
            Write-Host $_.Exception.Message
        }
    }


    AJ

    Wednesday, April 24, 2019 2:37 PM
  • And what is the full error message?


    \_(ツ)_/

    Wednesday, April 24, 2019 2:40 PM
  • If you are running this with PsExec then there is no way it will work.  You must use a correct account and access the database directly.


    \_(ツ)_/

    Wednesday, April 24, 2019 2:41 PM
  • Invoke-Sqlcmd : The server principal "SERVERNAME\ACCOUNT$" is not able to access the database "<dbName>" under the
    current security context.
    At C:\Users\<User>\Desktop\SQL_Log_File_New.ps1:200 char:20
    + ...  $sqlPerf = Invoke-Sqlcmd -Query "use [$db] Dbcc sqlperf (logspace)"  ...
    +                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
        + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand



    AJ

    Wednesday, April 24, 2019 2:46 PM
  • I use this function to back SQL

    function Backup-Databases
    {
        [CmdletBinding()]
        Param(
            [Parameter(Mandatory=$false)]
            [string]$ServerInstance,
            [Parameter(Mandatory=$false)]
            [string]$BackupPath,
            [Parameter(Mandatory=$false)]
            [string[]]$DatabaseNames
        )
    
        if(-Not $BackupPath.EndsWith("\"))
        {
            $BackupPath += "\"
        }
    
        foreach($dbName in $DatabaseNames)
        {
            $fileName = $dbname + ".bak"
    
            Backup-SqlDatabase -ServerInstance $ServerInstance -Database $dbName -BackupFile ($BackupPath + $dbName + ".bak") | Out-Null
        }      
    }

    Wednesday, April 24, 2019 2:54 PM
  • Hello,

    The databases are backed up correctly if I run without PsExec i.e. other than a local system account. It is only while using PsExec that I am getting the above error.

    My backup code as below:

    if($sqlVer -ge "11.0")
                            {
                                Write-Host "Preparing to backup $dbName on $hostname to $targetPath"
                                Backup-SqlDatabase -ServerInstance $hostname -Database $dbName -BackupFile $targetPath
                                Write-Host "Backed up $dbName on $hostname to $targetPath"
                            }


    AJ


    • Edited by Rex7 Wednesday, April 24, 2019 3:33 PM Correction
    Wednesday, April 24, 2019 3:30 PM
  • Hello,

    The databases are backed up correctly if I run without PsExec i.e. other than a local system account. It is only while using PsExec that I am getting the above error.

    My backup code as below:

    if($sqlVer -ge "11.0")
                            {
                                Write-Host "Preparing to backup $dbName on $hostname to $targetPath"
                                Backup-SqlDatabase -ServerInstance $hostname -Database $dbName -BackupFile $targetPath
                                Write-Host "Backed up $dbName on $hostname to $targetPath"
                            }


    AJ


    You cannot use PsExec and the system account o backup a SQL database.

    This is not a scripting issue.  TO understand how to use SQLServer and backups please post in the SQLServer forum.


    \_(ツ)_/

    Wednesday, April 24, 2019 8:38 PM