Can not access file share restoring to instance

• 
  Please check if the SQL Backup Agent service startup account for the instance you want to restore to, has rights to access the file server.
  
  Thanks.

  petey August 30, 2010 23:23

  - 0 +
• 
  I am using the same account for both instances.

  simmonsj_98 August 31, 2010 12:21

  - 0 +
• 
  Here is the security log entry from the file share server.
  
  An account was successfully logged on.
  
  Subject:
  Security ID: NULL SID
  Account Name: -
  Account Domain: -
  Logon ID: 0x0
  
  Logon Type: 3
  
  New Logon:
  Security ID: ANONYMOUS LOGON
  Account Name: ANONYMOUS LOGON
  Account Domain: NT AUTHORITY
  Logon ID: 0x356abeb1
  Logon GUID: {00000000-0000-0000-0000-000000000000}
  
  Process Information:
  Process ID: 0x0
  Process Name: -
  
  Network Information:
  Workstation Name: Production SQL
  Source Network Address: XXX.XX.XXX.XX
  Source Port: 58206
  
  Detailed Authentication Information:
  Logon Process: NtLmSsp
  Authentication Package: NTLM
  Transited Services: -
  Package Name (NTLM only): NTLM V1
  Key Length: 128
  
  This event is generated when a logon session is created. It is generated on the computer that was accessed.
  
  The subject fields indicate the account on the local system which requested the logon. This is most commonly a service such as the Server service, or a local process such as Winlogon.exe or Services.exe.
  
  The logon type field indicates the kind of logon that occurred. The most common types are 2 (interactive) and 3 (network).
  
  The New Logon fields indicate the account for whom the new logon was created, i.e. the account that was logged on.
  
  The network fields indicate where a remote logon request originated. Workstation name is not always available and may be left blank in some cases.
  
  The authentication information fields provide detailed information about this specific logon request.
  - Logon GUID is a unique identifier that can be used to correlate this event with a KDC event.
  - Transited services indicate which intermediate services have participated in this logon request.
  - Package name indicates which sub-protocol was used among the NTLM protocols.
  - Key length indicates the length of the generated session key. This will be 0 if no session key was requested.

  simmonsj_98 August 31, 2010 12:36

  - 0 +
• 
  SQL Backup uses the sqbdir extended stored procedure to retrieve the directory listings. Could you please try running sqbdir on both instances and let me know the output e.g.
  
  EXEC master..sqbdir '\\<remote share>\<folder name>\'
  
  Thanks.

  petey September 01, 2010 12:29

  - 0 +
• 
  Default instance
  Retuens a datasaet.
  
  
  
  Staging Instance
  
  Msg 1, Level 16, State 1, Line 0
  <WINERROR>5
  
  Both are using the same account for the SQL Backup Agent. This account has access to the share; I have been able to run backups. I am an administrator on the share, and my credentials are what the wizard claims it is passing when I try to add a server.

  simmonsj_98 September 01, 2010 12:38

  - 0 +
• 
  Could you please check the version of the SQL Backup extended stored procedure library installed on each instance to see if they are the same? The file is named xp_sqlbackup.dll, and is found in each SQL Server instance's Binn folder.
  
  Or you could just run the following on each instance:
  

  DECLARE @version varchar(16)
  EXEC master..sqbutility 1030, @version OUTPUT
  SELECT @version
  

  Thanks.

  petey September 01, 2010 13:01

  - 0 +
• 
  Default: 6.4.0.56
  
  Named Instance 6.4.0.56

  simmonsj_98 September 01, 2010 13:14

  - 0 +
• 
  Ok, we can rule that out.
  
  Could you please check in the registry, under
  
  HKLM\Software\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name>
  
  if there is data for a registry value named 'ServiceLogin', for both the instances?
  
  Thanks.

  petey September 01, 2010 13:18

  - 0 +
• 
  Both are present but neither has the data value set.

  simmonsj_98 September 01, 2010 13:25

  - 0 +
• 
  And you were logged on to both instances using the same Windows account when you ran the 'sqbdir' test earlier?
  
  Thanks.

  petey September 01, 2010 13:30

  - 0 +
• 
  Yes. My AD account is in the sysadmin role on both.

  simmonsj_98 September 01, 2010 13:33

  - 0 +
• 
  We are definitely narrowing things down ...
  
  Is there a 'BrowsingUserList' entry in the above registry nodes for both instances? Do they have any values?
  
  Could you also please activate the debug version of sqbdir i.e.
  
  sp_addextendedproc sqbdir_d, 'xp_sqlbackup.dll'
  
  and run the above 'sqbdir' tests again? This should generate a file named 'xp_sqlbackup_trace.txt' in each instance's 'Binn' folder, containing a detailed log of the internal processes of the browsing function. Could you please post the contents of both logs?
  
  Thanks.

  petey September 01, 2010 13:41

  - 0 +
• 
  I will get right on that. In the mean time I did just take a backup on the named instance and it did save to both the local drvie and copy to the file share.

  simmonsj_98 September 01, 2010 13:58

  - 0 +
• 
  I do not have a BrowsingUserList in the registry for the default or named instance.
  
  
  I ran sp_addextendedproc sqbdir_d, 'xp_sqlbackup.dll' and then re-ran sqbdir, but no text file was generated in the Binn directory.

  simmonsj_98 September 01, 2010 14:19

  - 0 +
• 
  I'm sorry, but it should have been run sqbdir_d instead of sqbdir e.g.
  
  EXEC master..sqbdir_d '\\<remote share>\<folder name>\'

  petey September 01, 2010 14:26

  - 0 +
• 
  Still not geting the output file. Do I need to adjust permissions on the Binn directory?

  simmonsj_98 September 01, 2010 14:35

  - 0 +
• 
  Yes, please try that. The SQL Server service startup account would need write rights to that folder to create the log file.
  
  Thanks.

  petey September 01, 2010 14:38

  - 0 +
• 
  Default Instance.
  
  Wed Sep 01 10:40:21 2010 RunSQBDir.Entry
  Wed Sep 01 10:40:21 2010 XProcDirBase.RunViaImpersonation
  
  
  Named Instance.
  Wed Sep 01 10:42:43 2010 RunSQBDir.Entry
  Wed Sep 01 10:42:43 2010 XProcDirBase.RunViaImpersonation

  simmonsj_98 September 01, 2010 14:43

  - 0 +
• 
  Could you also please grant your logon account rights to write to that folder, and rerun the sqbdir_d test?
  
  Thanks.

  petey September 01, 2010 14:47

  - 0 +
• 
  I am an administrator on the machine and the Administartors group has full control.

  simmonsj_98 September 01, 2010 14:51

  - 0 +
• 
  Let me think this over tomorrow. This is a typical output from running sqbdir_d:
  
  Thu Sep 02 02:53:59 2010 RunSQBDir.Entry
  Thu Sep 02 02:53:59 2010 XProcDirBase.RunViaImpersonation
  Thu Sep 02 02:53:59 2010 srv_impersonate_client:OK
  Thu Sep 02 02:53:59 2010 XProcDirEx:Execute.Entry
  Thu Sep 02 02:53:59 2010 XProcDirEx:Execute.m_dirMode: 1
  Thu Sep 02 02:53:59 2010 DisplayFolderContents.BeforeFindFirstFile
  Thu Sep 02 02:53:59 2010 IsSubstDrive.Entry
  Thu Sep 02 02:53:59 2010 IsSubstDrive.Result: FALSE
  Thu Sep 02 02:53:59 2010 IsSubstDrive.Exit
  Thu Sep 02 02:53:59 2010 XProcDirEx:Execute.Exit
  
  In your output, the entries stopped after the impersonation occurred. One reason may be that the impersonated account (the Windows account you are using to log on to SQL Server) does not have rights to write to the log file. Aside from that, I can't think of any other reasons at this point.

  petey September 01, 2010 14:56

  - 0 +
• 
  I tried to add my account on the binn direrotory and got this.
  
  Default.
  
  Wed Sep 01 10:40:21 2010 RunSQBDir.Entry
  Wed Sep 01 10:40:21 2010 XProcDirBase.RunViaImpersonation
  Wed Sep 01 10:58:35 2010 RunSQBDir.Entry
  Wed Sep 01 10:58:35 2010 XProcDirBase.RunViaImpersonation
  Wed Sep 01 10:58:35 2010 srv_impersonate_client:OK
  Wed Sep 01 10:58:35 2010 XProcDirEx:Execute.Entry
  Wed Sep 01 10:58:35 2010 XProcDirEx:Execute.m_dirMode: 1
  Wed Sep 01 10:58:35 2010 DisplayFolderContents.BeforeFindFirstFile
  Wed Sep 01 10:58:35 2010 IsSubstDrive.Entry
  Wed Sep 01 10:58:35 2010 IsSubstDrive.Result: FALSE
  Wed Sep 01 10:58:35 2010 IsSubstDrive.Exit
  Wed Sep 01 10:58:35 2010 XProcDirEx:Execute.Exit
  
  
  Named Instance.
  
  Wed Sep 01 10:42:43 2010 RunSQBDir.Entry
  Wed Sep 01 10:42:43 2010 XProcDirBase.RunViaImpersonation
  Wed Sep 01 11:00:24 2010 RunSQBDir.Entry
  Wed Sep 01 11:00:24 2010 XProcDirBase.RunViaImpersonation
  Wed Sep 01 11:00:24 2010 srv_impersonate_client:OK
  Wed Sep 01 11:00:24 2010 XProcDirEx:Execute.Entry
  Wed Sep 01 11:00:24 2010 XProcDirEx:Execute.m_dirMode: 1
  Wed Sep 01 11:00:24 2010 DisplayFolderContents.BeforeFindFirstFile
  Wed Sep 01 11:00:24 2010 IsSubstDrive.Entry
  Wed Sep 01 11:00:24 2010 IsSubstDrive.Result: FALSE
  Wed Sep 01 11:00:24 2010 IsSubstDrive.Exit
  Wed Sep 01 11:00:24 2010 XProcDirEx:Execute.Exit

  simmonsj_98 September 01, 2010 15:00

  - 0 +
• 
  All things being equal, I can't see what's going wrong. To recap:
  
  - you log on to both SQL Server instances, on the same box, using the same Windows account, using Windows authentication
  - you run a 'sqbdir' command to list the contents of a directory
  - internally, 'sqbdir' impersonates your log-on user prior to retrieving the directory contents
  - from the log, the impersonation was successful on both instances
  - 'sqbdir' then tries to retrieve the directory contents
  - on the 'standby' distance, this failed with windows error code 5, which means 'access is denied'
  
  Have you tried running 'sqbdir' to list other local and remote folders?
  
  Thanks.

  petey September 02, 2010 05:09

  - 0 +
• 
  That is all correct.
  
  I have only used the sqbdir in the code sample that you provided in this post.
  
  Should I open a support ticket on this?
  
  Until I get this resolved I can not put the backup in my production enviromnet and would really like to be using it.
  
  Thanks

  simmonsj_98 September 02, 2010 12:18

  - 0 +
• 

  I have only used the sqbdir in the code sample that you provided in this post.

  Have you tried using sqbdir to access other remote shares that you know you have access to? Do you always receive the same results i.e. succeed on the default instance, but fail on the standby instance?
  

  Until I get this resolved I can not put the backup in my production enviromnet and would really like to be using it.

  The problem lies only in how SQL Backup browses directories and display the results in the GUI. It does not affect backups in your case, since you are able to use the COPYTO option to copy the backup files to the remote share.
  
  There is a workaround for this, where the 'sqbdir' function does not attempt to impersonate and browse the folders, but instead passes the duty to the SQL Backup Agent service. The SQL Backup Agent service will then read the directory contents, assuming it has adequate rights, which depends on the SQL Backup Agent service startup account you are using.
  
  To use this workaround, you need to add a 'BrowsingUserList' entry to the instance's SQL Backup registry setting i.e.
  
  HKLM\Software\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name>
  
  Use the string value type if you only want to enter a single name, use the multi-string value type for multiple names. Enter the names of the Windows user accounts that will be allowed to browse directories using the SQL Backup Agent service startup account's credentials. Note that the name has to match what SQL Server recognises when you log on. You can use the SYSTEM_USER function to check the value e.g.
  

  SELECT SYSTEM_USER
  

  petey September 02, 2010 12:40

  - 0 +

Add comment

Please sign in to leave a comment.