23 Ocak 2010

SQL Server 5(Access is denied) Problemi

ile Yunus KALDIRIM

Problem:

The operating system returned the error ‘5(Access is denied.)’ while attempting ‘RestoreContainer::ValidateTargetForCreation’
System.Data.SqlClient.SqlError: The operating system returned the error ‘5(Access is denied.)’ while attempting ‘RestoreContainer::ValidateTargetForCreation’

şeklinde yazan problem genellikle restore yaparken karşımıza çıkmaktadır. SQL Server 2005 te ayarlardan dolayı olmaktadır.

Symptoms:

  • I’m setting up a test system using MS SQL 2005 Express, and I’m trying
    to restore one of my MS SQL 2000 Server databases to the 2005 Express
    box, which is running Windows XP Pro.

    I have local admin rights and am logging into SQL 2005 Express as
    ‘sa’, but when I try to restore the SQL 2000 database, I get the
    following error:

    TITLE: Microsoft SQL Server Management Studio Express
    Restore failed for Server ‘TESTBox\TESTDB’.
    (Microsoft.SqlServer.Express.Smo)
    ADDITIONAL INFORMATION:
    System.Data.SqlClient.SqlError: The operating system returned the
    error ‘5(Access is denied.)’ while attempting
    ‘RestoreContainer::ValidateTargetForCreation’ on ‘c:\Program Files
    \Microsoft SQL Server\MSSQL.2\MSSQL\TestReport.mdf’.
    (Microsoft.SqlServer.Express.Smo)

    I’m not sure if this is saying access denied to c:\Program Files
    \Microsoft SQL Server\MSSQL.2\MSSQL\ or if this is a security issue
    with MS SQL 2005. On the Restore Database window it shows ‘sa’ under
    Connection, and my logged in user account is local admin on the box.

    Any ideas or suggestions?

  • Restoring a database fails with an access denied error along the lines of ‘Restore failed for Server’ or System.Data.SqlClient.SqlError: The operating system returned the error ‘5(Access is denied.)’

    After installing SQL Express and trying to restore a SQL Server 2000 database to a new database I got the following error:
    Restore failed for Server ‘<SERVERNAME>\SQLEXPRESS’. (Microsoft.SqlServer.Express.Smo)

  • I recently ran into a problem trying to restore a SQL Server 2005 database to a Windows XP machine.  The database back up was created on a Windows Vista machine and I thought that the different OS versions was the culprit.  However, it turned out to be related to SQL server instances and folder paths.  Here is full error I received when I attempted to restore the database using SQL Server Management Studio:

    Restore failed for Server ‘localhost\sqlexpress’.  (Microsoft.SqlServer.Express.Smo)

    Additional information:
    System.Data.SqlClient.SqlError: The operating system returned the error ‘5(Access is denied.)’ while attempting ‘RestoreContainer::ValidateTargetForCreation’ on ‘c:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\[my database name].mdf’. (Microsoft.SqlServer.Express.Smo)

Solution1:

  • I went to SQL Server Configuration Manager > SQL Server 2005 Services > double cliked SQL Server (SQL EXPRESS) > and changed network service to LocalSystem.

Solution2:

  • This problem seemed to be caused by the SQL Express service running as NT AUTHORITY\NetworkService. Change the service to run as the local system account, restart and the SQL Express restore should work properly.
    After doing that it should just be a case of restoring a SQL Server 2000 database as normal straight into SQL Server Express