Results 1 to 11 of 11

Thread: Running code against Sqlserver worked fine first time but now 'Access denied'

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Nov 2008
    Posts
    68

    Running code against Sqlserver worked fine first time but now 'Access denied'

    I am trying to backup my DB using this code First time I got it to run it worked well and My DB was backed up
    Since then I am getting 'Access Denied' errors at the executeNonQuery line . I deleted the old backup in case it just didn't like overwriting it but that didn't help
    The exact error is ---
    "Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS17\MSSQL\Backup'. Operating system error 5(Access is denied.).
    BACKUP DATABASE is terminating abnormally."

    any advice would be appreciated

    Code:
    Dim sqlConnectionString As String = $"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog={DBName};Data Source={Server}" 
                'Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=BankAccounts;Data Source=MAIN\SQLEXPRESS17
                Dim BackupLocation As String
                BackupLocation = GetAllTextBeforeLastOccuranceof(DataLocation, "\") & "Backup"
                Dim conn As New SqlConnection(sqlConnectionString)
                conn.Open()
    
                Dim cmd As New SqlCommand
                cmd.CommandType = CommandType.Text
                cmd.CommandText = $"BACKUP DATABASE BankAccounts TO DISK='{BackupLocation}'"
                cmd.Connection = conn
                cmd.ExecuteNonQuery()
                conn.Close()
    Last edited by mpooley; Sep 17th, 2020 at 06:47 PM. Reason: spelling

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    105,599

    Re: Running code against Sqlserver worked fine first time but now 'Access denied'

    Did you perhaps run your application as an administrator the first time?

  3. #3
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,269

    Re: Running code against Sqlserver worked fine first time but now 'Access denied'

    Backup (write-permissions) in "Program Files"?
    Good lord......
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Nov 2008
    Posts
    68

    Re: Running code against Sqlserver worked fine first time but now 'Access denied'

    @Zvoni
    Would be helpful if you could be a bit less facetious.

  5. #5
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,428

    Re: Running code against Sqlserver worked fine first time but now 'Access denied'

    You need to add a file name to the backup command.. It looks like you are sending a folder path without a file name
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  6. #6
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,021

    Re: Running code against Sqlserver worked fine first time but now 'Access denied'

    Quote Originally Posted by mpooley View Post
    @Zvoni
    Would be helpful if you could be a bit less facetious.
    OK, writing files to anywhere in the Program Files is a bad idea... MS has increasingly locked down that folder over the years to prevent users & apps from shooting themselves in the foot. Ideally, your backups should be more accessable anyways and be relocated to a clean directory all its own, not one part of the applicaiton's install path.

    When ever I set up a SQL Server, I create a D: (or what ever drive is NOT C \SQLBackups\{DB Name} and store the backups for databsses in individual folders. SO the Northwind database gets backed up to D:\SQLBackups\Northwind\*.bak

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Nov 2008
    Posts
    68

    Re: Running code against Sqlserver worked fine first time but now 'Access denied'

    Thankyou!
    I was only doing this as a test as it's going to be deployed elsewhere, I'd assumed I would have Access but I'll change it to one of the user folders

  8. #8

    Thread Starter
    Lively Member
    Join Date
    Nov 2008
    Posts
    68

    Re: Running code against Sqlserver worked fine first time but now 'Access denied'

    I changed to my user folder to Test it but I got the same error?

  9. #9
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,021

    Re: Running code against Sqlserver worked fine first time but now 'Access denied'

    Couple things:
    1) Why would you have a user folder on the server?
    2) Even if you did, you're not the "user" on the server, the service is... it doesn't have the same context, so your user folder wouldn't be visible to the service. That's why I use the path I use... it's not tied to any user.
    3) Just to be sure, you are saving it to a path that EXISTS and is LEGAL on the SERVER, and NOT on your machine, right? Because that's a common mistake I see people make with running the backup process... thinking that it will be on their local system somehow and will give it a folder on their machine, but doesn't exist on the server.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10

    Thread Starter
    Lively Member
    Join Date
    Nov 2008
    Posts
    68

    Re: Running code against Sqlserver worked fine first time but now 'Access denied'

    Sorry I didn't explain properly the application will use a local copy of sqlserverExpress and I am testing on Pc with sqlserverExpress installed
    The path I'm testing does exist

    thanks

    Mike

  11. #11

    Thread Starter
    Lively Member
    Join Date
    Nov 2008
    Posts
    68

    Re: Running code against Sqlserver worked fine first time but now 'Access denied'

    Quote Originally Posted by GaryMazzone View Post
    You need to add a file name to the backup command.. It looks like you are sending a folder path without a file name
    You were right about that! thanks I hadn't noticed.
    still had access problems but sorted as far as testing is concerned

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width