1

I'm using following code to back up a SQL Server database using VB.Net to some other physical location. But I get the following error when trying to save it inside clients PCs. In some clients, it works on external hard drives but not on all clients.

Cannot open backup device 'D:\11-16-2020_POS_DB_MM.bak'. Operating system error 5(Access is denied.)
BACKUP DATABASE is terminating abnormally.

I understand it's an error happening due to user permissions. Is it possible to rectify this in my code or do I always have to set user permissions in every client (database is in a server and there are multiple clients. I use a setup package to deploy on clients)

Public Class frmBackup
    Private connString As String = "Data Source=SQLSERVER\SLMA;Database=master;User Id=sa;Password =" & dbPwd & ";"
    Private con As SqlConnection = New SqlConnection(connString)

Private Sub cmbbackup_Click(sender As Object, e As EventArgs) Handles cmbbackup.Click
        Try
            SaveFileDialog1.FileName = DateAndTime.DateString + "_" + dbName
            SaveFileDialog1.Filter = "SQL Server database backup files|*.bak"
            SaveFileDialog1.ShowDialog()
            Dim cmd As New SqlCommand("BACKUP DATABASE " & dbName & " To disk='" & SaveFileDialog1.FileName & "'", conn)
            If Not conn.State = ConnectionState.Open Then
                conn.Open()
            End If
            cmd.ExecuteNonQuery()
            conn.Close()
            MsgBox("The database was successfully backup to - " & System.IO.Path.GetFullPath(OpenFileDialog1.FileName))
        Catch ex As Exception
            MsgBox(Err.Description)
            Me.Close()
        End Try
    End Sub
    
End Class
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
lakshitha dilhan
  • 83
  • 9
  • 2
    The error seems to be telling you the problem; the service account that SQL Server is running under does not have access to the destination you want to back up to. That isn't something you can fix in your VB code, its something that needs to be done from your Network Administrators side. Don't forget, as well, that any paths are from the **instances** perspective, not the users. `D:\ ` would relate to the device mounted on `D` for the user that SQL Server is running as on the instance it's running on; which is likely a 2nd local storage device. If it's network, you should be using a UNC path. – Thom A Nov 16 '20 at 11:05
  • I'm also managing the database server. How can i correct it from database server side or network? – lakshitha dilhan Nov 16 '20 at 11:13
  • 1
    Ask your network administrator to ensure the service account that the SQL Instance is running under to be granted access to the local storage device `D` on the host the instance is running on. – Thom A Nov 16 '20 at 11:16
  • I'm the only IT guy here and has to manage the network even though I'm not too good at it. I want to choose the destination to backup using a file dialog box. But as per your instruction its not possible. Isn't it? – lakshitha dilhan Nov 16 '20 at 11:21
  • 1
    Depends on your goal. I still don't know, for example, if `D:` is local to the instance, or to the user. If the user, certainly not as the SQL instance's Service Account should not have have that much access on the domain. You would normally back up and then copy the backup to a local location if you need it locally; or put it on a network location and `RESTORE` locally from the network path. – Thom A Nov 16 '20 at 11:26
  • D is in user and it will change according to the location selected by the user through file dialog box – lakshitha dilhan Nov 16 '20 at 11:30
  • 1
    Then see my [prior comment](https://stackoverflow.com/questions/64856769/vb-net-code-to-backup-and-save-mssql-database-wont-work?noredirect=1#comment114667801_64856769). – Thom A Nov 16 '20 at 11:31
  • What I'm trying to achieve here is to allow users at clients to take backups on their own computer. Server is inaccessible to them physically – lakshitha dilhan Nov 16 '20 at 11:32
  • 1
    This has a strong smell of an [XY Problem](http://xyproblem.info) if I am honest. Users who don't have access to the server should not be creating backups of it. The application, by the way, is dangerously open to injection; especially when you're using the `sa` `LOGIN` for the application. – Thom A Nov 16 '20 at 11:35
  • The problem is I'm not present at the clients location and their IT knowledge is very low. Server is physically protected there due to being handling a sensitive financial application. Clients do not have the option to restore the database or they do not know the server path since its embedder in the application. The application has a form with backup command button which they use to back to external drive. This is very useful in my case since it ensures more backup options. Anyway thank you very much for the advice. I'm only going to the site to restore backups during a fail. – lakshitha dilhan Nov 16 '20 at 11:42

2 Answers2

1

Rather than sacrificing security by changing directory/file permissions, you can make your own subdirectory in the directory returned by Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData), set the ACL for that subdirectory so that all users have read and write permission, create the backup in that subdirectory, then move the resulting file to the user's selected location. The file move will be done in the context of the user using your program.

You could also take the opportunity to make the backup into a password-protected zip archive.

Andrew Morton
  • 24,203
  • 9
  • 60
  • 84
1

Database is in a server and there are multiple clients. I use a setup package to deploy on clients

BACKUP always runs on the server, and any drives would have to be mouted on the server, not the client. You can't just plug in a thumbdrive on a client and backup a database on the server to it.

Also note that in Windows only administrators can write files to the root of a drive. If you do want to put the backup on the D: drive you should create a folder for it and grant permissions on that.

A database on a server should have an automated backup configured, but if you want to initiate an ad-hoc backup and you have no access to the server, you can backup the database to an Azure Blob Storage Account using Backup to URL.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67