« Ouch - Cringely Offers Stinging Indictment of IT "Profession" | Main| Twitter Struggles to Stay Afloat »

Scheduled Agent Tricks: Log in to a restricted file share with alternate username/password

Category   


Domino-to-Windows.pngSo the SQL Server guy stops by and says, "Hey, we want to create some reports of Notes data and we only know how to use SQL Server to do it. Do you mind exporting it to a delimited text file and saving it to this restricted folder on the file server? Here's the username and password of a special Windows account with rights to the folder. Oh, and we need it to run on a scheduled basis so everything is automated. Thanks!"

So I think, "Gee, dumping Notes data to a text file...piece of cake. Saving that file somewhere...yeah whatever. Run the export on a schedule...no problem since Domino has scheduled agents. Save the file to a *restricted* folder on the file server...with a different set of credentials...uh, I don't think so."

At this point I figure we just need to give the Windows machine on which the Domino server resides access to said restricted directory, since after all it is the server doing the work. I've never considered the need to output a file somewhere that wasn't semi-open, so the notion of telling a Domino agent to "please use this username/password" when it runs didn't seem promising. But as with any question I cannot answer definitively, I try to find someone who can. And considering this is a question of Domino interacting with the Windows environment, who else to check with but our own Code Poet in Residency, Charles Robinson.

So I say, "Charles, here's what these guys want me to do. I think it might be a non-starter, but figured you'd know (way) better than I. Any thoughts?" And sure enough, Charles shoots back a couple of links to some sample code that puts me on what I hope is the right path.

Connecting to a remote share with different credentials really isn't that difficult:  http://vbnet.mvps.org/index.html?code/network/wnetaddconnection2.htm or http://www.dreamincode.net/forums/showtopic34399.htm.  The issue is what rights the account running Domino has to the local system.  You may end up having to create a new account for Domino to run under, but that would be the only configuration change in the worst case scenario.


The second link is what got me going, and after some fiddling to get the VB code ported over to work in LotusScript, presto!

So, without further ado, I present a little script library containing a couple of routines that will allow you to open and close a remote connection to a restricted file folder. You should be able to do whatever you want in terms of reading, updating, and saving files to that folder once you open the connection. I've included two sample agents to help get you started. One uses simple Print statements to write out a text file (as was my case), and the other detaches files from Notes documents to the folder.

Enjoy!

Download from OpenNTF here

Comments

1 - Curious, why not use LEI ?

2 - @Todd - why indeed. Different groups maintain different systems, and it might make Domino look too good if we had it handle everything in the most efficient way.

3 - Hi Kevin,

I'll take a look at this code. It could well come in handy at some point. Did you consider using the Lotus Notes ODBC client (on the SQL server) and pull the Domino data directly without going via a text file?

See you in Dublin! Emoticon

Rob

4 - @Rob, yes I did recommend NotesSQL at the outset as that would allow the SQL guys to run their own show without bothering the Notes guys. However, since that would also require (I believe, correct me if I'm wrong) installing Notes on the SQL server machine, that was deamed unacceptable.

5 - @Kevin,
This is very timely. I'm in the middle of the same thing. I've done this before by creating a .bat file on the server that does an xcopy with the id and password in the path. I run one LEI job to create the file to the Notes server and then run another shell agent to run the .bat. I really like your solution and will give it a try.
THANKS! Notes.net reading told me this could not be done because the server runs as a service and the service has no access to mapped drives on the server.

6 - @Kevin,
I was looking at your code and like I was saying, I was playing around with this recently too. Instead of hard coding the column titles, I created the view columns to match the target and did this to add to the first row of the exported text file.

Forall c In view.Columns
item = c.Title
szOut = item
Print #1, szOut & |;|;
End Forall

Print #1, "" ' End the line

szOut and item are strings. Took me a while to find the Title property. Emoticon

7 - @Kevin,
Played around some more. Instead of a search query, I'm looping through a view and using the view column values. This way, I was able to use column formulas in my views to do some of the string replacement for commas and @Newline. Cool. Very nice. Thank you. I just did it to my local. Will try on server too.

8 - @Curt, I'm glad you're finding this useful. Of course, the sample export and detach agents were just there to show a couple ways to use the truly interesting code that does the remote connection login. Using a view to generate the output is perfectly acceptable and "easier" in some ways than my example, but the example code is view independent, fwiw.

I would be curious to know if anyone encounters problems running on the server. There may yet prove to be some network configurations/partitioning where it won't work.

9 - @Kevin,
Understood.
I imagine since it's using Win32 api, it breaks when the os changes. But, doesn't everything involving MS break when the os changes? :)

10 - Yes, LEI is one way. We've had similar requests as well and have used LEI. In a couple of cases, they other team (requesting the data) wanted just a flat file and we used JAVA to FTP the flat files to them, on a scheduled basis.

11 - What do you do if the server you need to copy the file to is a 3rd party's (a Cisco Media Convergence Server - whatever that may be), and they are not willing to enable FTP on it, but you have access via an id etc. and the file transfer has to be scheduled? Emoticon

12 - @12 - This method has nothing to do with FTP so as long as you can address the server/directory (e.g. "\\servername1\Reports\Sales"), and you have a valid username/password, it should work. Scheduling the activity is as simple as putting the code into a scheduled agent.

13 - Hi Kevin,

It creates the text file on a remote server when I run the agent manually but it does not do anything when I try to schedule it. Am I supposed to do something on the server side?

Regards,

Waqqas

14 - @14 "Does not do anything" is pretty vague. Is the agent running at all? Is it generating an error (look in the server's log.nsf or put some proper error handling in the agent)? Are the agent security settings too restrictive? Is the Domino server unable to "see" the file server because of network/firewall settings (try changing the output directory to one on the Domino server itself and see if that runs properly)?

In all likelihood the problem has nothing to do with the *code* of the agent and everything to do with some other aspect of your environment setup.

Good luck Emoticon

15 - Sorry Kevin as I was not very clear. It does not throw any error. I checked the agent log and notes log and I did not find any error. I am trying to log the error in the agent log in case of any error and there is nothing. I also tried to create the file on the local machine itself and it did not create anything. It works fine when I run the agent manually.

Regards,

Waqqas

16 - @16 When you right click on the agent and "view log" does it indicate that it ran? Have you added Print statements to the code at several points to show in the server's log.nsf what is happening (if anything) as the agent runs? Did you check that the agent's security settings are set to "Allow Restricted Operations"?

Clearly it is an issue unrelated to the fact that the code was trying to put a file in a remote directory. You might check the Google for tips on general "Domino scheduled agent troubleshooting" or something similar if you are new to the process.


17 - Kevin, you are wonderful! I copied my code into your database agent and it started working. I had copied script library and agents in a new database and it was not working. May be I was missing something. Anyway, thanks a lot for your great help.


Regards,

Waqqas

18 - Hi ,
I tried WNetAddConnection2 in my lotus script agent , agent is working fine when ran manually by the admin. However its not working when run on schedule.
I have put the logs , it gives network error when WNetAddConnection2 method is called.
This is the error : Net Connection Failed! : ERROR: 1326

My question is why agent gives network error only when run on schedule , while it runs smoothly manually. Username and password is correct.
Thanks inadvance for the help.

Regards , Yogi

88 - hi, i modified this for my daily csv export agent. still works great. thank you :)

Post A Comment

:-D:-o:-p:-x:-(:-):-\:angry::cool::cry::emb::grin::huh::laugh::lips::rolleyes:;-)

Your Host

KevinPettitt.jpg
Kevin Pettitt View Kevin Pettitt's profile on LinkedIn

Tools I Use

Idea Jam

Subscribe to This Blog

 Full Posts  Comments

MyYahoo
netvibes Add to Netvibes

Contact

Hosted by

OpenNTF

Disclaimer

This site is in no way affiliated, endorsed, sanctioned, supported, nor blessed by Lotus Software nor IBM Corporation, nor any of my past or future clients (although they are welcome to do so). The opinions, theories, facts, etc. presented here are my own and in no way represent any official pronouncement by me on behalf of any other entity.

© 2005-2017 Kevin Pettitt - all rights reserved as listed below.

Creative Commons License
Unless otherwise labeled by its originating author, the content found on this site is made available under the terms of an Attribution / NonCommercial / ShareAlike