Solved

SQL Backup to network share with different credentials

Posted on 2013-05-29
11
668 Views
Last Modified: 2013-05-31
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
Comment
Question by:Gary Harper
  • 6
  • 5
11 Comments
 
LVL 23

Expert Comment

by:nemws1
ID: 39206065
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
 

Author Comment

by:Gary Harper
ID: 39206770
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
 
LVL 23

Expert Comment

by:nemws1
ID: 39208491
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
 

Author Comment

by:Gary Harper
ID: 39208729
My issue is the account used for the SQL server service is different then the account that has permissions on the network share.
0
 
LVL 23

Expert Comment

by:nemws1
ID: 39208766
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:Gary Harper
ID: 39208773
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
 
LVL 23

Assisted Solution

by:nemws1
nemws1 earned 400 total points
ID: 39208799
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
 

Author Comment

by:Gary Harper
ID: 39211516
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
 

Author Comment

by:Gary Harper
ID: 39211542
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
 
LVL 23

Expert Comment

by:nemws1
ID: 39211522
Well, if that works, great.  I usually consider having xp_cmdshell enabled to be a security risk.
0
 
LVL 23

Accepted Solution

by:
nemws1 earned 400 total points
ID: 39211543
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now