Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to copy .bak files on an external drive which is not part of domain but requires authentication? Expand / Collapse
Author
Message
Posted Tuesday, May 14, 2013 12:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 19, 2014 7:48 AM
Points: 39, Visits: 113
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.
Post #1452404
Posted Tuesday, May 14, 2013 12:33 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:32 AM
Points: 7,135, Visits: 12,745
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
Post #1452767
Posted Tuesday, May 14, 2013 1:03 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 19, 2014 7:48 AM
Points: 39, Visits: 113
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..
Post #1452789
Posted Tuesday, May 14, 2013 1:18 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:35 AM
Points: 12,921, Visits: 32,289
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1452795
Posted Tuesday, May 14, 2013 2:04 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:32 AM
Points: 7,135, Visits: 12,745
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
Post #1452823
Posted Tuesday, May 14, 2013 2:19 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:32 AM
Points: 7,135, Visits: 12,745
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
Post #1452827
Posted Tuesday, May 14, 2013 2:31 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:35 AM
Points: 12,921, Visits: 32,289


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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1452835
Posted Tuesday, May 14, 2013 3:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:32 AM
Points: 7,135, Visits: 12,745
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
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
Post #1452858
Posted Wednesday, May 15, 2013 8:41 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:25 PM
Points: 31,279, Visits: 15,740
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
Post #1453138
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse