• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1457
  • Last Modified:

SQL Backup to network share with different credentials

I am trying to set up my database backups on a network share with different credentials.  The credentials needed to connect to the network share are in a different domain.  I am able to connect to it by mapping a network drive but I would like to use the UNC path.  It is my understanding that the account used to run SQL server needs to have access to this share.  This is not the case.  Is there anyway for me to do a successful backup in this scenario?  What needs to be changed for it to be successful?  Please advise.
0
Gary Harper
Asked:
Gary Harper
  • 6
  • 5
2 Solutions
 
nemws1Database AdministratorCommented:
All backups performed by the SQL server are done as the user (typically a system account) who is running SQL server.  Within SQL server you can grant additional users the ability to run backups, but all backups are done as the SQL server user.

In your scenario, you'll have to do the backups normally (to local disk) and then run a process as a user that has BOTH *read* access to the backup files and *write* access to your UNC path.
0
 
Gary HarperAuthor Commented:
Thanks for the info.  So there is no way to connect to a network share directly without using the sql server credentials with the appropriate permissions on that share.  Is that correct?
0
 
nemws1Database AdministratorCommented:
Either the server has permissions to write to the share or it does not.  I'm not sure what kind of end-around you're looking for here.

You could set the share up with world-write access.  That would solve your problem, but give you a bunch of other headaches.

What is the issue with granting your SQL Server process write access to your share?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Gary HarperAuthor Commented:
My issue is the account used for the SQL server service is different then the account that has permissions on the network share.
0
 
nemws1Database AdministratorCommented:
Sure - it *should* be set up that way (good security practices!)  My question is, why don't you *add* the SQL Server service account to the share and grant it write privileges?  Do you not have permissions to do so?
0
 
Gary HarperAuthor Commented:
No.  I don't have the permissions to do so.  But if that is the only way it will work then I will have to work that angle on my end.
0
 
nemws1Database AdministratorCommented:
Well, I think you still have 3 options.

1) Get the SQL Server creds added to the share

2) Let SQL run its backup normally, grant *local* access to the user that does have access to the remote share, and do a file copy/move after the backups are run

3) Change the user the SQL Server is running as (bad suggestion - you'll have to make sure to change ownership of any/all files the SQL Server process owns and it opens up a security hole on your SQL Server, but it *is* an option)
0
 
Gary HarperAuthor Commented:
I ended up having to use the following logic to write a network share that had different credentials in another domain.

EXEC xp_cmdshell 'net use H: \\computername\sharename password/user:domain\username /persistent:yes'

EXEC sp_configure'xp_cmdshell', 1
reconfigure  
GO

EXEC sp_configure 'show advanced options', 1
reconfigure
GO

--Step 1. Create a Procedure

CREATE PROCEDURE map_drive_startup
AS
EXEC xp_cmdshell 'net use H: \\computername\sharename password/user:domain\username /persistent:yes'

--Step 2.  Set Procedure Options

sp_procoption  @ProcName = 'map_drive_startup'
, @OptionName = 'startup'
, @OptionValue = 'on'
0
 
Gary HarperAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for GHarper143's comment #a39211516

for the following reason:

This process will allow you to backup to a network share with different credentials.
0
 
nemws1Database AdministratorCommented:
Well, if that works, great.  I usually consider having xp_cmdshell enabled to be a security risk.
0
 
nemws1Database AdministratorCommented:
Actually, your solution doesn't make much sense.  You should be issuing your 'net use' command in Windows, not from within SQL Server.  You've done what I suggested since the beginning - created a new share that SQL Server can write to.  All you are doing is mounting that new share from within SQL Server (which is bad practice).
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now