May 14, 2013 at 12:44 am
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.
May 14, 2013 at 12:33 pm
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
May 14, 2013 at 1:03 pm
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..
May 14, 2013 at 1:18 pm
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
May 14, 2013 at 2:04 pm
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
May 14, 2013 at 2:19 pm
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
May 14, 2013 at 2:31 pm
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
May 14, 2013 at 3:14 pm
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
May 15, 2013 at 8:41 am
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply