Solved

SQL Backup to network share with different credentials

Posted on 2013-05-29
11
806 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: 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!

 

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
 

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

728 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