Home > Configuration, SQL Server > SSMS Restore backup error

SSMS Restore backup error

September 14, 2007 Leave a comment Go to comments

We had a requirement to allow someone to create and restore databases on a test server today and i thought to myself "Thats easy, i`ll just grant the "Create Any Database" right to the appropriate user, thats when the pain began!

The user was using SSMS connecting using a SQL login to restore a database and when they went to specify the backup location they got an error to the effect "Cannot access the specified path or file on the server". After clicking ok the tree view in the locate backup dialog was empty and if you typed in the path and filename manually you still recieved an error.
So, i dug out the profiler and found that xp_fixeddrives was being called and decided to check it out. It turns out that when executing xp_fixeddrives using a SQL login it returns no results! Because of this the error is generated in SSMS and the tree view is not populated.
I spent some time investigating xp_fixeddrives and came to the following conclusions regarding its behaviour.

  • Currently the only way to get xp_fixeddrives to return results when using SQL authentication is to add it to the sysadmin role.
  • I extensively explored proxy account configurations to get xp_fixeddrives to work under SQL authentication and i could find no way to get it working.
  • If you use Windows authentication xp_fixeddrives works as expected. You do not need sysadmin privledges nor do you have to do any proxy configuration.
  • Some poking around with process monitor showed that when using windows authentication or the sysadmin process that SQLSERVR.exe spawns processes to gather the details using the service account. When using SQL Authentication with sysadmin SQLSERVR.exe does not spawn anything or generate any security errors so what ever is going on is beyond what i can work out (My skills with a debugger are to limited :)  ).

At the end of all this i went down the Windows authentication root to work around the issue. For some reason SSMS still generates the error but the tree view is then populated and you can select the file so i can live with that as i suspect exploring why its still generating an error may end up being a bottomless pit of time.

Finally, this only affects SSMS. If the user had done the restore using T-SQL it would have worked (despite a warning about updating the restore history in msdb).

Advertisements
Categories: Configuration, SQL Server
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: