Solved

SQL Backup to network share with different credentials

Posted on 2013-05-29
11
740 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
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!

 

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

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!

Question has a verified solution.

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

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…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

726 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