Backup Permissions Issue

nagac · March 4, 2015 12:38PM

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

petey · March 4, 2015 9:57PM

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

nagac · March 5, 2015 5:59AM

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

petey · March 5, 2015 7:32AM

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 = &#91;&lt;AUTO&gt;&#93;"'

Thanks.

nagac · March 6, 2015 5:15AM

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

petey · March 6, 2015 7:35AM

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?

nagac · March 6, 2015 10:14AM

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

petey · March 6, 2015 11:56AM

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.

nagac · March 6, 2015 5:24PM

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.

petey · March 8, 2015 12:12PM

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?

nagac · March 9, 2015 10:03AM

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

petey · March 9, 2015 10:09AM

Could you pls run this instead?

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

nagac · March 9, 2015 10:37AM

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

petey · March 9, 2015 11:06AM

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?

nagac · March 9, 2015 11:26AM

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?

petey · March 9, 2015 11:35AM

Yes, that would be another item to check for.

nagac · March 12, 2015 8:00AM

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

petey · March 13, 2015 12:26PM

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.

nagac · March 26, 2015 8:20AM

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

petey · March 26, 2015 9:33PM

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?

nagac · April 8, 2015 5:20AM

Hi Petey,

You are absolutely right.

Thanks
Naga

petey · April 8, 2015 11:11PM

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?

nagac · April 10, 2015 5:03AM

It will create database backup file without any exceptions.

petey · April 10, 2015 7:24AM

Glad to hear that worked.

nagac · April 11, 2015 3:26PM

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

petey · April 12, 2015 8:55AM

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

nagac · April 16, 2015 5:04AM

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.

petey · April 20, 2015 10:29PM

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.

Backup Permissions Issue

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk