Solved

SQL Backup to network share with different credentials

Posted on 2013-05-29
11
675 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

911 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

18 Experts available now in Live!

Get 1:1 Help Now