none
Operating system error 5(Access is denied.)

    Question

  •  I have SQL 2005 running in windows server 2003, I access this through a domain account with windows authentication. Recently someone tried updating to SP2, along with doing other windows updates, and it failed.  Now I get the following error when i try to backup any database in sql

     

    Cannot open backup device 'XXXXXXX'. Operating system error 5(Access is denied.). 

    I can logon as 'sa' and still i get the error. 
    Also all the maintainence i was doing with sql Agent was stuck showing 'Executing' and never finished or errored. For days it did that until I caught it and stopped them.
    Right now the server is running normally as far as the service, but I cant back it up with code as 'sa' or with management studio.

    Any help on this would be greatly appreciated. 

    Wednesday, December 17, 2008 11:23 PM

Answers

All replies

  • Please check and ensure that the SQL Service account and your windows login has modify permissions to the directory where backup is taken. The error is pretty obvious - access denied. Anything in the errorlog or sql agent log when it hanged ?

    - Deepak

    Deepak | Mark the answers if it helps to solve your problem |
    • Proposed as answer by Papy NormandModerator Thursday, December 18, 2008 3:35 PM
    • Unproposed as answer by Zaden Friday, January 09, 2009 6:26 PM
    Thursday, December 18, 2008 12:05 AM
    Moderator
  •  Thanks for your reply, and yes logically it would seem to be a simple access permissions problem. Though I am about to start losing some hair because I have given 'service'(i hope this is the sql service account) all access...my login all access...network authority all access( I think sql is logging in as network service but it was at local system login before when everything was working fine).   And so far I have had no luck. There is nothing in the error logs except the same error message im getting from management studio "OS error 5 (Access is denied)".

    Any other thoughts or ideas would be greatly appreciated.
    Thursday, December 18, 2008 6:10 PM
  •  (I have SQL 2005 running in windows server 2003, I access this through a domain account with windows authentication.)

    The domain account needs admin permission in the server running SQL Server so you can access the SQL Server Backup folder where backups are saved by default.  If your Backup is to other drive not C then you need to change SQL Server back to local systems because this was a bug introduced in SQL Server 2005 when Windows Admins can remove SQL Server Admins from Admins of the server running SQL Server.  It is fixed in 2008 SQL Server Admins can remove Windows Admins from the relational engine.  And when Windows authentication is used SA is disabled by default and if you change to both Windows and SQL in some cases you may have to enable the SA account.


    (Also all the maintainence i was doing with sql Agent was stuck showing 'Executing' and never finished or errored. For days it did that until I caught it and stopped them.)

    If your jobs are within your network and not using xp_cmdshell you need to put the Agent back as Local systems but all executions through applications must use Proxy accounts.


    These will fix some of your problems post back with what you need resolved again.


     


    Asp.net MVP, MCPD Web C#, MCTS TFS, MCITP BI and DBA
    Thursday, December 18, 2008 8:41 PM
    Moderator
  • Thanks Caddre for your reply, I will post back with results tomarrow...also ...I can back up the database by right clicking the DB in Managemnet studio and click back up, put the same folder that i cant back up to in code or agent and it works.
    My boss thinks he messed something up with the updates\update failure and wants to reinstall sql server....so im hoping to prove that theory wrong.

    Thursday, December 18, 2008 10:07 PM
  • There is no need to reinstall go into configuration manager see the account SQL Server Service and the Agent are using because SQL Server Service uses Local system by default and the Agent uses network service which is a limited permissions account.  But most complex tasks the Agent needs local system account.

    Howver if you have slow time you can re start SQL Server service only as needed.




    Asp.net MVP, MCPD Web C#, MCTS TFS, MCITP BI and DBA
    Thursday, December 18, 2008 10:17 PM
    Moderator
  • I have it backing up now with code or otherwise manually. But any Sql Agent task fails and i have it set to local system

    The only error message the sql log has is a message telling me i havent yet setup my mail, but it would only try to send mail if the job already failed.

    This is the extent of my error message from the job: Job 'NewTestTest.Subplan_1' failed. (SqlManagerUI)

    Thanks in advance to any shared thoughts or ideas.

     

    Friday, December 19, 2008 10:33 PM
  • I found that if you added another component to SQL Server after you apply SP2 you need to reapply SP2 again.  So here is how to find the errors and if you need it SP3 is out please run tests before applying it.

    http://www.mssqltips.com/tip.asp?tip=1054

    http://blogs.technet.com/dataplatforminsider/archive/2008/12/10/sql-server-2005-sp3-now-available-for-download.aspx


    Asp.net MVP, MCPD Web C#, MCTS TFS, MCITP BI and DBA
    • Marked as answer by Zaden Friday, January 09, 2009 6:27 PM
    Friday, December 19, 2008 11:02 PM
    Moderator
  • Sorry about the late reply, but the answer to my problem was to install SP3. There must have been some failed component installs from when my co-worker updated to SP2.

    Thanks Caddre for all your help.
    Friday, January 09, 2009 6:29 PM