Can not access file share restoring to instance

simmonsj_98 · August 30, 2010 5:31PM

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

petey · August 30, 2010 11:23PM

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.

simmonsj_98 · August 31, 2010 12:21PM

I am using the same account for both instances.

simmonsj_98 · August 31, 2010 12:36PM

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.

petey · September 1, 2010 12:29PM

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.

simmonsj_98 · September 1, 2010 12:38PM

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.

petey · September 1, 2010 1:01PM

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.

simmonsj_98 · September 1, 2010 1:14PM

Default: 6.4.0.56

Named Instance 6.4.0.56

petey · September 1, 2010 1:18PM

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.

simmonsj_98 · September 1, 2010 1:25PM

Both are present but neither has the data value set.

petey · September 1, 2010 1:30PM

And you were logged on to both instances using the same Windows account when you ran the 'sqbdir' test earlier?

Thanks.

simmonsj_98 · September 1, 2010 1:33PM

Yes. My AD account is in the sysadmin role on both.

petey · September 1, 2010 1:41PM

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.

simmonsj_98 · September 1, 2010 1:58PM

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 1, 2010 2:19PM

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.

petey · September 1, 2010 2:26PM

I'm sorry, but it should have been run sqbdir_d instead of sqbdir e.g.

EXEC master..sqbdir_d '\\<remote share>\<folder name>\'

simmonsj_98 · September 1, 2010 2:35PM

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

petey · September 1, 2010 2:38PM

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

Thanks.

simmonsj_98 · September 1, 2010 2:43PM

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

petey · September 1, 2010 2:47PM

Could you also please grant your logon account rights to write to that folder, and rerun the sqbdir_d test?

Thanks.

simmonsj_98 · September 1, 2010 2:51PM

I am an administrator on the machine and the Administartors group has full control.

petey · September 1, 2010 2:56PM

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.

simmonsj_98 · September 1, 2010 3:00PM

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

petey · September 2, 2010 5:09AM

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.

simmonsj_98 · September 2, 2010 12:18PM

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

petey · September 2, 2010 12:40PM

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

Can not access file share restoring to instance

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk