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

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    And what is the full error message?

    \_(ツ)_/

    Wednesday, April 24, 2019 2:40 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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