Backup Permissions Issue

• 
  Could you please check if the SQL Backup Agent service startup account has the SQL Server sysadmin role assigned to it?

  petey March 04, 2015 21:57

  - 0 +
• 
  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

  nagac March 05, 2015 05:59

  - 0 +
• 
  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.

  petey March 05, 2015 07:32

  - 0 +
• 
  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

  nagac March 06, 2015 05:15

  - 0 +
• 
  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?

  petey March 06, 2015 07:35

  - 0 +
• 
  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

  nagac March 06, 2015 10:14

  - 0 +
• 
  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.
  
  

  petey March 06, 2015 11:56

  - 0 +
• 
  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.

  nagac March 06, 2015 17:24

  - 0 +
• 
  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 .<your instance name> -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 = [<AUTO>]"'
  

  What is the result?

  petey March 08, 2015 12:12

  - 0 +
• 
  Hi Petey,
  
  Below are the outputs from the given commands
  osql -E -S .<your instance name> -Q "SELECT IS_SRVROLEMEMBER('sysadmin')"
  

  [SQL Server Native Client 10.0]SQL Server Network Interfaces: Error Locating
  Server/Instance Specified [xFFFFFFFF].
  [SQL Server Native Client 10.0]Login timeout expired
  [SQL Server Native Client 10.0]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].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. [Db name=C:ProgramDataRed GateSQL BackupData(local)data.sdf]
  
  
  SQL Backup exit code: 1000
  Warning 402: Failed to open local data store: Database already opened by a different user. [Db name=C:ProgramDataRed GateSQL BackupData(local)data.sdf]
  

  
  
  Thanks

  nagac March 09, 2015 10:03

  - 0 +
• 
  Could you pls run this instead?
  

  osql -E -Q "SELECT IS_SRVROLEMEMBER('sysadmin')"
  

  petey March 09, 2015 10:09

  - 0 +
• 
  osql -E -Q "SELECT IS_SRVROLEMEMBER('sysadmin')"
  

  1
  

  nagac March 09, 2015 10:37

  - 0 +
• 
  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?

  petey March 09, 2015 11:06

  - 0 +
• 
  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?

  nagac March 09, 2015 11:26

  - 0 +
• 
  Yes, that would be another item to check for.

  petey March 09, 2015 11:35

  - 0 +
• 
  The User is under Administrators group, So it has rights on "Create Global Objects"

  nagac March 12, 2015 08:00

  - 0 +
• 
  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.

  petey March 13, 2015 12:26

  - 0 +
• 
  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

  nagac March 26, 2015 08:20

  - 0 +
• 
  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?

  petey March 26, 2015 21:33

  - 0 +
• 
  Hi Petey,
  
  You are absolutely right.
  
  Thanks
  Naga

  nagac April 08, 2015 05:20

  - 0 +
• 
  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?

  petey April 08, 2015 23:11

  - 0 +
• 
  It will create database backup file without any exceptions.

  nagac April 10, 2015 05:03

  - 0 +
• 
  Glad to hear that worked.

  petey April 10, 2015 07:24

  - 0 +
• 
  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

  nagac April 11, 2015 15:26

  - 0 +
• 
  What is the reason for using the SQL Backup command line interface instead of the SQL Backup extended stored procedures to run the backups?

  petey April 12, 2015 08:55

  - 0 +
• 
  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.

  nagac April 16, 2015 05:04

  - 0 +
• 
  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 = [<AUTO>]""'"
  

  
  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.

  petey April 20, 2015 22:29

  - 0 +

Add comment

Please sign in to leave a comment.