SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to copy .bak files on an external drive which is not part of domain but requires authentication?...


How to copy .bak files on an external drive which is not part of domain but requires authentication?

Author
Message
shamshad.ali
shamshad.ali
Say Hey Kid
Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)

Group: General Forum Members
Points: 692 Visits: 419
We are looking for a solution to copy .bak files on external network drive which does not allow access without loginId and password, we are seeing some intermittent hardware issue with database server, right now the backup is on local db server, which would be useless incase of hardware disaster. so to be on safe side, we arranged a portable device attached as network drive which requires authentication. We want to simply copy that backup file on network drive. How do we do this. the network drive is not part of domain etc. It is independent drive.

Please help and provide if there is any solution (Script/ .bat files etc)


Shamshad Ali.
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39256 Visits: 14411
Ideally the network device would be setup to refer to Active Directory for Authentication, but if that is not possible and the device does it's own Authentication then you can try mapping a drive to it using NET USE and supplying a username and password, then writing to the mapped drive letter, then removing the mapped drive also using NET USE.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
shamshad.ali
shamshad.ali
Say Hey Kid
Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)

Group: General Forum Members
Points: 692 Visits: 419
Thanks for help, could you please write some more in details, how can we supply login I'd and password to network drive, do you mean while mapping it? Please provide steps..
Lowell
Lowell
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71086 Visits: 40924
an example via xp_cmdshell from an old post;
the obvious problem is the script contains a cleartext password.

if the shared drive was already set up at the operating system level, the mapped drive would be available there, which would better and more secure.


NET USE Drive: path /USER:domainname\username password
--working example
exec master.dbo.xp_cmdshell 'NET USE J: \\DEV223\c$\DataFiles /user:disney\lowell SeriouslyNotMyRealPassword /persistent:yes'
GO
RESTORE Database DBName FROM Disk = 'J:\Path to bak'
GO
--cleanup after ourselves and remove the mapping
EXEC master.dbo.xp_cmdshell 'NET USE J: /DELETE'




Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39256 Visits: 14411
shamshad.ali (5/14/2013)
Thanks for help, could you please write some more in details, how can we supply login I'd and password to network drive, do you mean while mapping it? Please provide steps..

In short, yes. Lookup the help for the NET USE DOS command. Lowell has posted an example for you including the syntax for NET USE but I say do not use xp_cmdshell unless you absolutely have to. You should be able to do this by calling a .bat file from a SQL Agent Job Step.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39256 Visits: 14411
Lowell (5/14/2013)
if the shared drive was already set up at the operating system level, the mapped drive would be available there, which would better and more secure.

It's been a while, but I remember there being a hitch with this approach. When xp_cmdshell is run by a member of the sysadmin Role does the SQL Server service account User have to be logged into the operating system console for the mapped drives to remain accessible to these unattended processes? Or was there a way to get the mapped drive to be available when the User is actually not logged in?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Lowell
Lowell
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71086 Visits: 40924
yeah this was from my SQL 2000 notes, so we are talking really old school code;

It Used to be possible to map a drive by throwing a key in the registry @ HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Run, and having a key there to call a bat file or other executable, which ran the NET USE command. an executable woudl at least mask the password.

I think that registry key is mostly disabled on most machines due to it's abuse; i stopped using it long ago.

it has been a zillion days since i ever had to look at this issue, since it's a lot easier to fix in other ways, like impersonation in a powershell or scheduled task or something similar, right?

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39256 Visits: 14411
I remember one server I had to work on being hacked to us autologon so the machine logged itself in as the SQL Server service account upon reboot so the drive mapping could happen, then it would immediately lock the desktop after logging itself in. Also a registry hack. Sort of secure in a way since the desktop was immediately locked but the password appeared in plain-text in the registry so if you could get on the machine and get in the registry you could get the password from HKLM and do all kind of other stuff as the service acount. If you were someone else and did not know the password for the logged in user you would have to boot the user to log into the console which would break the apps that needed the mapped drive. That only happened once a week in this particular shop :-D
Or you would have to RDP into one of the two admin Term Serv ports. This was Server 2003 days.

Personally I would try using New-PSDrive in PowerShell for this and call it from an SQL Agent Job Step or Windows Scheduled Task, but the OP mentioned .bat so I went with it.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Steve Jones
Steve Jones
SSC Guru
SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)

Group: Administrators
Points: 145417 Visits: 19425
AFAIK, the service account can log into Windows with a console, if you allow it, and map permanent drives that are retained.

This doesn't require the user connected to the instance to be logged in.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search