5

I'm attempting to attach an mdf file to an instance of SQL Server running on Amazon's RDS service. Using Microsoft SQL Server Management Studio 2016, I follow the following steps:

  1. After connecting to the amazon instance, I right click on the Databases directory and select Attach.
  2. Up comes the Attach Databases dialogue
  3. I click the Add button to select the local .mdf
  4. I get the following error message:

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476


ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)


The EXECUTE permission was denied on the object 'xp_fixeddrives', database 'mssqlsystemresource', schema 'sys'. The EXECUTE permission was denied on the object 'xp_fixeddrives', database 'mssqlsystemresource', schema 'sys'. The EXECUTE permission was denied on the object 'xp_fixeddrives', database 'mssqlsystemresource', schema 'sys'. The EXECUTE permission was denied on the object 'xp_fixeddrives', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)

I've seen this error message on several forums but always when trying to modify the database (at this point I believe I'm only bringing up a file browser to locate the mdf). I've also tried running it as admin but no luck. I'm quite new to SQL Server and am much more at home in a Linux environment, so I wouldn't rule out something obvious. Any help would be appreciated.

  • I haven't used RDS, but I did some Google searching- I saw some posts about RDS file storage being different than regular disk space,perhaps you could script out what you think the attach would look like in a non RDS server and make any changes necessary to point to RDS specific drives - maybe you could script and existing RDS database attach and use that as a template- this is a wild guess – Scott Hodgin Sep 10 '16 at 10:27
  • @ScottHodgin thanks for the response. I've read something similar as well but don't think I'm up to something quite so far outside the box yet. However, I gather from your response that you think this error is caused by the RDS server and not my local machine that's running SSMS? If that's the case I might want to try a different service. – neanderslob Sep 10 '16 at 10:48
  • I would try a different sever that isn't using cloud storage- it is my GUESS that your problem is related to cloud storage and not SSMS – Scott Hodgin Sep 10 '16 at 10:58
3

With Amazon RDS, you don't get direct access to the underlying filesystem.

If you have an existing database that you want to migrate into RDS SQL Server, rather than using the MDF/LDF files, you'll need to do a restore instead.

Amazon's instructions to restore a database are pretty good, but since you're asking this question, I'm guessing I probably need to fill you in on a background concept. Remember how I said you don't get access to the filesystem? Well, you can get access to OTHER file systems, kinda like how mapped drives work in Windows. S3 is Amazon's simple storage service where you can create buckets and put files in 'em. You're going to need to:

  • Create an S3 bucket
  • Put your database backup (not MDF/LDF) in the bucket
  • Give your RDS instance permission to read from that S3 bucket (that's where Amazon's instructions come in)
  • Use Amazon's custom stored procedure rds_restore_database to restore the backup
-1

I had the same issue and this solution worked for me in SQL Server 2013 and 2017

  1. start
  2. run
  3. type (services.msc) in run open it
  4. go to SQL Server (MSSQLSERVER)
  5. click on proprieties Select Log on
  6. Select "Local System Account" (the default was some obtuse Windows System account)
  7. stop SQL Server (MSSQLSERVER) then start it
  • Thats not relevant to Amazon RDS, you don't have access to the underlying OS or services. – Wayne Phipps Apr 26 at 16:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.