Backup Permissions Issue

nagacnagac Posts: 18
edited April 20, 2015 10:29PM in SQL Backup Previous Versions
Hi

I have got issue while running the Red Gate Backup script which says

3/4/2015 5:28:50 PM: Warning 300: Backup failed. Retry attempt: 3
3/4/2015 5:28:54 PM: VDI error 1000: Access is denied.
You may need to run SQL Backup using an account that has administrator rights on this machine.

The user id which I am using has admin rights on Database and login to the system also has admin rights.

Can someone suggest what needs to be done to make this working?

Thanks

Comments

  • peteypetey Posts: 2,358 New member
    Could you please check if the SQL Backup Agent service startup account has the SQL Server sysadmin role assigned to it?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Thanks for the response petey,

    Yes, it has sysadmin role. Backup work's well when I manually do it through GUI. But only the issue with Script.

    I found an error in Application Event Log as below that might give you some more info to narrow the error.

    SQLVDI: Loc=CVDS::CreateEx. Desc=Create Memory. ErrorCode=(5)Access is denied.
    . Process=5356. Thread=1612. Client. Instance=. VD=GlobalSQLBACKUP_156C4973-E676-4BE9-B29A-66C355EA5276_SQLVDIMemoryName_0.

    Thanks
  • peteypetey Posts: 2,358 New member
    Could you please run the following command in SQL Server Management Studio when connected to the instance that has SQL Backup installed, and post the output:
    EXEC master..sqlbackup '-sql "BACKUP DATABASE model TO DISK = [<AUTO>]"'
    
    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Hi Petey,

    Sorry for the delayed response this is due to the licensing issue with the product.

    I have got the below output:

    Backing up model (full database) to:
    C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLBackupFULL_(local)_model_20150306_101021.sqb

    Database size : 2.750 MB
    Compressed data size: 198.000 KB
    Compression rate : 92.97%

    Processed 168 pages for database 'model', file 'modeldev' on file 1.
    Processed 1 pages for database 'model', file 'modellog' on file 1.
    BACKUP DATABASE successfully processed 169 pages in 0.052 seconds (25.390 MB/sec).
    SQL Backup process ended.

    exitcode 0
    sqlerrorcode 0
    filename001 C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLBackupFULL_(local)_model_20150306_101021.sqb


    Thanks
  • peteypetey Posts: 2,358 New member
    If you manually use SQL Server Management Studio to create a SQL Server Agent job step to run the exact same backup command, ran under the same user account you used to run the backup earlier, then run the job manually, does it back up successfully?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • I tried as you said, and the Backup is successful Petey, However the backup is failed when I tried using my script command.

    SQLBackupC.exe -E -SQL "BACKUP DATABASE [%%I] TO DISK='%DBBACKUP_PATH%!DBBACKUPFILENAME!' WITH PASSWORD = '<ENCRYPTEDPASSWORD>IxY6YeZHYWfKje1nc/a5Y97mc3XalU5piw==</ENCRYPTEDPASSWORD>', COMPRESSION = 4
  • peteypetey Posts: 2,358 New member
    Ok, now I understand that you are trying to run the backup via the command line interface.

    The account you are logged on as needs to have the SQL Server sysadmin server role assigned to it. Being the owner of the database you are trying to back up is not enough.

    sysadmin.jpg
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Hi Petey,

    I have checked the roles the user id i am using while connecting to command prompt has sysadmin role. Sql backup Agent is running by using the same user.
  • peteypetey Posts: 2,358 New member
    Could you please open a command prompt window when logged on to Windows using the same account you are trying to run the backup with, then run the following (adjust the instance name):
    osql -E -S .&lt;your instance name&gt; -Q "SELECT IS_SRVROLEMEMBER('sysadmin')"
    

    What is the returned value? After that, in the same command prompt window, try running the following:
    sqlbackupc -sql "BACKUP DATABASE model TO DISK = &#91;&lt;AUTO&gt;&#93;"'
    
    What is the result?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Hi Petey,

    Below are the outputs from the given commands
    osql -E -S .<your instance name> -Q "SELECT IS_SRVROLEMEMBER('sysadmin')"
    &#91;SQL Server Native Client 10.0&#93;SQL Server Network Interfaces: Error Locating
    Server/Instance Specified &#91;xFFFFFFFF&#93;.
    &#91;SQL Server Native Client 10.0&#93;Login timeout expired
    &#91;SQL Server Native Client 10.0&#93;A network-related or instance-specific error
    has occurred while establishing a connection to SQL Server. Server is not
    found or not accessible. Check if instance name is correct and if SQL Server
    is configured to allow remote connections. For more information see SQL Server
    Books Online.
    

    sqlbackupc -sql "BACKUP DATABASE model TO DISK = [<AUTO>]"'
    Backing up model (full database) to:
      C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLBackupFULL_(local)_model_20150309_140200&#93;.sqb
    
    VDI error 1000: Access is denied.
    You may need to run SQL Backup using an account that has administrator rights on this machine.
    
    Memory profile
    Type             Maximum     Minimum     Average     Blk count   Total
    ---------------- ----------- ----------- ----------- ----------- -----------
    Commit           14293213184 4096        9847213     1506        14829903872
    Reserve          51271106560 8192        301869827   170         51317870592
    Free             8723836436480 4096        41970890299 208         8729945182208
    Private          51271106560 4096        64652836    1021        66010546176
    Mapped           4079616     4096        258808      70          18116608
    Image            51929088    4096        203609      585         119111680
    
    
    Warning 300: Backup failed.  Retry attempt: 1
    VDI error 1000: Access is denied.
    You may need to run SQL Backup using an account that has administrator rights on this machine.
    
    Memory profile
    Type             Maximum     Minimum     Average     Blk count   Total
    ---------------- ----------- ----------- ----------- ----------- -----------
    Commit           14293213184 4096        9847213     1506        14829903872
    Reserve          51271106560 8192        301869827   170         51317870592
    Free             8723836436480 4096        41970890299 208         8729945182208
    Private          51271106560 4096        64652836    1021        66010546176
    Mapped           4079616     4096        258808      70          18116608
    Image            51929088    4096        203609      585         119111680
    
    
    Warning 300: Backup failed.  Retry attempt: 2
    VDI error 1000: Access is denied.
    You may need to run SQL Backup using an account that has administrator rights on this machine.
    
    Memory profile
    Type             Maximum     Minimum     Average     Blk count   Total
    ---------------- ----------- ----------- ----------- ----------- -----------
    Commit           14293213184 4096        9866833     1503        14829850624
    Reserve          51271106560 8192        303643896   169         51315818496
    Free             8723836436480 4096        41970900420 208         8729947287552
    Private          51271106560 4096        64905054    1017        66008440832
    Mapped           4079616     4096        258808      70          18116608
    Image            51929088    4096        203609      585         119111680
    
    
    Warning 300: Backup failed.  Retry attempt: 3
    VDI error 1000: Access is denied.
    You may need to run SQL Backup using an account that has administrator rights on this machine.
    
    Memory profile
    Type             Maximum     Minimum     Average     Blk count   Total
    ---------------- ----------- ----------- ----------- ----------- -----------
    Commit           14293213184 4096        9847213     1506        14829903872
    Reserve          51271106560 8192        301869827   170         51317870592
    Free             8723836436480 4096        41970890299 208         8729945182208
    Private          51271106560 4096        64652836    1021        66010546176
    Mapped           4079616     4096        258808      70          18116608
    Image            51929088    4096        203609      585         119111680
    
    
    Warning 300: Backup failed.  Retry attempt: 4
    VDI error 1000: Access is denied.
    You may need to run SQL Backup using an account that has administrator rights on this machine.
    
    Memory profile
    Type             Maximum     Minimum     Average     Blk count   Total
    ---------------- ----------- ----------- ----------- ----------- -----------
    Commit           14293213184 4096        9847213     1506        14829903872
    Reserve          51271106560 8192        301869827   170         51317870592
    Free             8723836436480 4096        41970890299 208         8729945182208
    Private          51271106560 4096        64652836    1021        66010546176
    Mapped           4079616     4096        258808      70          18116608
    Image            51929088    4096        203609      585         119111680
    
    Warning 402: Failed to open local data store: Database already opened by a different user. &#91;Db name=C:ProgramDataRed GateSQL BackupData(local)data.sdf&#93;
    
    
    SQL Backup exit code: 1000
    Warning 402: Failed to open local data store: Database already opened by a different user. &#91;Db name=C:ProgramDataRed GateSQL BackupData(local)data.sdf&#93;
    


    Thanks
  • peteypetey Posts: 2,358 New member
    Could you pls run this instead?
    osql -E -Q "SELECT IS_SRVROLEMEMBER('sysadmin')"
    
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • osql -E -Q "SELECT IS_SRVROLEMEMBER('sysadmin')"
    1
    
  • peteypetey Posts: 2,358 New member
    Could you please use SQL Profiler to trace the commands ran by SQLBackupC.exe, and check if the user account it is using to run the backup commands is the same account that you are logged in as?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • I did it, and I could see the same userid which I used to run the commands.

    I googled the error and it says user id should have rights on Create Global Objects. Is that the one I need to look now?
  • peteypetey Posts: 2,358 New member
    Yes, that would be another item to check for.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • The User is under Administrators group, So it has rights on "Create Global Objects"
  • peteypetey Posts: 2,358 New member
    Do you see the same entries in the event log as mentioned in this post? If so, could you try granting the user explicit rights to create global objects? I know you mentioned that the user account is already an Administrator, but at the moment, I don't know what else to try.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Our Windows System Administrators has confirmed that the User id is having access.

    I just found something weird here, I had scheduled the script through windows task scheduler and it worked fine without any errors. however it doesn't work only when I run it manually. I used same user in both these cases.

    Thanks
  • peteypetey Posts: 2,358 New member
    Ok, so if I understand it right, your situation is as follows:

    - you can run a backup using the SQL Backup extended stored procedure in Management Studio
    - you can run a SQL Backup backup job using SQL Server Agent
    - you cannot run a SQL Backup backup from the command prompt
    - you can run a SQL Backup backup task using Windows Task Scheduler

    Is this correct?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Hi Petey,

    You are absolutely right.

    Thanks
    Naga
  • peteypetey Posts: 2,358 New member
    What happens if you started the command prompt window using the 'Run as administrator' option, and tried running a backup using the SQL Backup command line interface?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • It will create database backup file without any exceptions.
  • peteypetey Posts: 2,358 New member
    Glad to hear that worked.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • But we are not supposed to execute the script as administrator in our organisation. As we have separate domain accounts which should be used to execute any batch processes or Agent jobs.which is why we are trying to solve this access related issues.

    thanks
  • peteypetey Posts: 2,358 New member
    What is the reason for using the SQL Backup command line interface instead of the SQL Backup extended stored procedures to run the backups?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • We can go with any one of these two methods. We are using SQLBackupC.exe as it is easy to use and we can directly execute it through cmd prompt.

    We have got temporary solution from Support for this solution but there is no solution for automation

    runas /netonly /user:domainuser cmd

    Backup works when I do this before I execute the actual Backup process. But this needs to be configured in system without using the above steps.
  • peteypetey Posts: 2,358 New member
    Since you have to use the command line interface to run the backup, have you considered using the SQL Server command line interface to run the SQL Backup extended stored procedure?

    E.g. from the command prompt, can you run the following successfully (change the -E and -S values to match your environment):
    sqlcmd -E -S .sql2005 -Q "EXEC master..sqlbackup '-sql ""BACKUP DATABASE model TO DISK = &#91;&lt;AUTO&gt;&#93;""'"
    

    This uses the SQL Server sqlcmd interface to run the backup. It uses trusted authentication (the -E option), though you can also use SQL Server authentication. Also, the Windows account you are currently logged on as needs to have rights to connect to the SQL Server instance, execute the sqlbackup extended stored procedure, and also rights to back up the model database.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.