What are the challenges you face when working across database platforms? Take the survey
Options

Can not access file share restoring to instance

simmonsj_98simmonsj_98 Posts: 18
edited September 2, 2010 12:40PM in SQL Backup Previous Versions
I am trying to restore a database to a different instance on the same server. My backup is on a file server that I have access to. On step one of the wizard when I click add files I can add the file server from the default instance and browse my backups. However when I change the SQL server to the instance I want to restore to and try to add the same file server I get Cannot access resource.

Has anyone else seen this? I wasn’t able to find anything on the support site.
Thanks

Comments

  • Options
    peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    I am using the same account for both instances.
  • Options
    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.
  • Options
    peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    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.
  • Options
    peteypetey Posts: 2,358 New member
    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&#40;16&#41;
    EXEC master..sqbutility 1030, @version OUTPUT
    SELECT @version
    
    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Default: 6.4.0.56

    Named Instance 6.4.0.56
  • Options
    peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Both are present but neither has the data value set.
  • Options
    peteypetey Posts: 2,358 New member
    And you were logged on to both instances using the same Windows account when you ran the 'sqbdir' test earlier?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Yes. My AD account is in the sysadmin role on both.
  • Options
    peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    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.
  • Options
    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.
  • Options
    peteypetey Posts: 2,358 New member
    I'm sorry, but it should have been run sqbdir_d instead of sqbdir e.g.

    EXEC master..sqbdir_d '\\<remote share>\<folder name>\'
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Still not geting the output file. Do I need to adjust permissions on the Binn directory?
  • Options
    peteypetey Posts: 2,358 New member
    Yes, please try that. The SQL Server service startup account would need write rights to that folder to create the log file.

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    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
  • Options
    peteypetey Posts: 2,358 New member
    Could you also please grant your logon account rights to write to that folder, and rerun the sqbdir_d test?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    I am an administrator on the machine and the Administartors group has full control.
  • Options
    peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    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
  • Options
    peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    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
  • Options
    peteypetey Posts: 2,358 New member
    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
    
    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.