Search code examples
c#sql-serverdatabase-backupsfileshare

Get backup from a remote SQL Server

I'm writing a program to create a backup of a database and I need to be able to access the backup file in my program afterwards. The user should only provide connection information to the database. The server and the client are on the same network but not necessarily in the same domain. The program runs as admin.

My original plan was to create a share on the client using WMI and then execute the backup with that share as target. But I'm having authentication problems. I think I need a way to create a share everyone can connect to without being prompted for a login.

This has to work for Windows XP and 7.

Is this possible? Is there another solution?

Solution

  • With Windows 7 client not in a domain it is possible to connect without authentication by turning off Password Protected Sharing under Advanced Sharing Settings.

    With Windows 7 client in a domain this setting doesn't exist. It should be possible by setting the security policy 'Network access: Restrict anonymous access to Named Pipes and Shares' to disabled. But that didn't work for me. Specifying the share in 'Network Access: Shares that can be accessed anonymously' gave me read access but no write. Note that you have to give the local 'ANONYMOUS LOGON' user share and NTFS permissions (still write wouldn't work here). Also note that I have no clue about domains or AD so I don't know whether this can be blocked by domain policies or whatever.

    I found another solution which will have to do though I'm not happy with it. The big disadvantage is that you need to be sysadmin on the SQL Server.

    • Create a local temporary user on the the client computer. Max. user name length is 20, you can use a GUID as the password, this should satisfy the various password policies.
    • Create a share and give the temporary user read and write access to the share and the shared directory itself.
    • Enable xp_cmdshell.
    • Use xp_cmdshell and 'net use' to create a deviceless share using the temporary user account. E.g.: xp_cmdshell 'net use \\hostname\tmp /user:hostname\TmpUser E2C3E1B0-AFE6-49D6-96BD-DA5957EB319B'
    • Backup to the share like you would normally.
    • Use xp_cmdshell and 'net use' again to delete the share. E.g.: xp_cmdshell 'net use \\hostname\tmp /delete /yes'
    • If needed disable xp_cmdshell again.
    • Delete share, remove directory permissions and delete temporary user account.

    You need sysadmin on SQL Server because Microsoft says the following about xp_cmdshell:

    When it is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail.