Partilhar via

Why can't I attach a database to SQL Server 2008 R2?

  • Artigo

Recently, a problem was presented to me by a DBA using SQL Server 2008 R2.  He could not attach a database whose files
had been copied from another server. This blog talks about the situation and the solution. 

Here are the steps he went through to arrive at his situation.

  1. DBA #1 logs into a remote server #1 using SSMS from his desktop and detaches a database.
  2. DBA #2 copies the files to SQL Server #2 and using SSMS on his desktop attempts to attach that database.
  3. DBA #2 gets an error saying he does not have permissions to attach the database. (Did not capture the exact error message).

Other pertinent information:

  1. Both DBAs are in the sysadmin role on both SQL Server machines. 
  2. Both DBAs are in the local admin group on both SQL Server machines.
  3. Neither person is a domain admin.
  4. Did not run SSMS with the “run as administrator” option.
  5. UAC is turned on for the Windows 7 client running SSMS on both of the DBA machines.
  6. Both SQL Servers are running Windows 2008 R2 with latest service pack and SQL Server 2008 R2 with latest service pack.
  7. The SQL Servers are in different domains (big clue #1).
  8. The domains are not trusted. (big clue #2).
  9. There are no certificates involved.
  10. SQL Server service accounts are Local accounts but are not in the local administrators group.

My first thought is that because both DBAs are local admins and sysadmins the attach database command should succeed.  A quick
check of Books on Line (BOL) brings this explanation of why it did not work: https://msdn.microsoft.com/en-us/library/ms189128.aspx.

The reason this did not work initially is because two different people did the detach and attach operations as explained in the BOL link. So the files were owned by DBA 1. However, DBA 2 was a local administrator so he had access to the directory
where the files were stored, so he could copy the files.  But the attach failed on server 2 because DBA 2 was not the file owner.  

To get this to work, we went into the copied file properties for the .mdf and .ldf files and changed them to be owned by the local SQL Server service account.  Then the attach worked.  It would also have worked to change the file owner to DBA 2, the one issuing the attach command.

To test the scenario where the same person does the entire process, we created a sample database on server 1 and used the same person to detach, copy and attach the databases.  The same servers involved in the original problem were used.  This time the attach operation worked.

We noticed something interesting during this test.  After creating the sample database, the mdf and ldf files (while still attached) are owned by the SQL Server service account.  Use Windows Explorer and properties of the file to see the owner.   Then we detach the files, the owner becomes the person who did the detach command.  After copying the files to server 2, the ownership remains the same; i.e. the person who issued the detach command.  After the attach command is issued on server 2, the file owner becomes the new SQL Server service account. 

Impersonation is important

This test worked because the same person did the entire process.  Whether you use SQL authentication or Windows authentication to log into the database via SSMS, the process works as long as the same person does the entire process and as long as the connecting account can be impersonated.

Conclusion

File ownership can be a bit confusing.  It makes a difference whether the two servers are in the same or different domains and it makes a difference if the same person does the entire process.  It all comes down to knowing whether your
account is being impersonated on either server involved. 

Author:
Kevin Cox (SQLCAT), Cindy Gross (Microsoft Premier Field Engineering)

Contributors: Chuck Spencer (Pearson/eCollege)

Reviewers: Sanjay Mishra, Shawn Tinline-Jones, James Podgorski

Comments

  • Anonymous
    February 25, 2012
    Awesome post.  Here’s a tool that lets you build your cloud database apps without coding http://www.caspio.com/

  • Anonymous
    September 06, 2012
    I just rant into a similar NTFS problem with SQL Server 2012 and it goes without saying that granting permissions and/or ownership to the 'NT SERVICEMSSQL$SQLEXPRESS' fixed things right on up.

  • Anonymous
    October 15, 2013
    Try using the CREATE DATABASE with attach to this file ? See msdn.microsoft.com/.../ms176061.aspx