Determining Current Windows User, OpsNav Prompting

Hey, Howard:

I am using your DSNLess connection technique to connect users to my iSeries in an Excel macro. I don't want to make the user fill in a dialog box to connect to the iSeries. Is there any way to get the current user ID from Windows, so I can pass this to the iSeries and avoid a login dialog box?

--Rick

Yes, there is a way to retrieve user information in a macro, by using the Windows Scripting Host Network object. Also, the behavior of those dialog boxes that Client Access uses are influenced by several factors, so you need to examine some Client Access settings to accomplish everything you want to do. But, first, let's look at how to get the current user.

WSH Primer

Windows Script Host (WSH) is a technology that has been available from Microsoft for quite some time. It is extremely useful, because it allows you to write scripts in VBScript or JavaScript languages that interact with Windows objects, like the file system, the network system, or ActiveX Data Objects. If you are not running Windows 2000 or Windows XP, you might need to download the latest version of Windows Script Host from Microsoft.

The neat thing about WSH is that it's available not only in the operating system but also in the scripting engine, in Excel, Word, PowerPoint, and a host of other products. So you can write scripts and run them from Windows Explorer, or place them in Access and run them as a macro. The languages currently supported in WSH are VBScript and JavaScript. Since I do a lot of development work with Visual Basic, I prefer to write my scripts in VBScript. Those of you who do a lot of work in JavaScript may prefer to use that language.

To create a script, simply open Notepad, type in the following code, then save the file as MyUser.VBS. The VBS extension identifies the file as a VBScript file, and when double-clicked in Windows Explorer, Windows will run the VBS file through the WSH interpreter and perform the actions specified.

Dim X
set X = createobject("WSCRIPT.Network")
dim U
U=x.UserName
MsgBox "Logged In User is: " & U

The second line sets X to an instance of the WSCRIPT network object. This object allows access to shared network resources, like printers, folders and files. It also contains a property called UserName, which identifies the current user.

If you wanted to access the network object in a macro or from a Visual Basic program, your code would look slightly different. First, you would have to reference the Windows Script Host Object Model to your programming environment. In Visual Basic, you would choose the Project menu and then the References menu, and then place a checkmark next to Windows Script Host Object Model. In the embedded Visual Basic editor in Word, Excel, Access, etc., you would choose the Tools menu, then the References selection, and then place a checkmark next to Windows Script Host Object Model. Here is a sample of how the same code would look in VB:

Sub Main()
       Dim X As New WshNetwork
       Dim U As String
       U = X.UserName
       MsgBox "Current User is: " & U
End Sub

Note that in the VB environment, I do not have to use a call to CreateObject, as I have referenced the object in the development environment. I could use CreateObject in the VB environment, but by referencing the object I get the nice tool tips and function arguments that make programming easier.

Configuration of Client Access for Prompting

Now you know how to get the current user name, but how do you control Client Access prompting behavior? This can be done on several levels: at the connection string, at the DSN, or within Operations Navigator. Let's look at Operations Navigator first.

If you start Operations Navigator, then right-click on an iSeries, and select properties from the popup menu, you will see a dialog that controls the properties of that iSeries connection. Select the connection tab and, depending on your version of OS/400 and Operations Navigator, you will see a combination of the following sign-on capabilities:

• Use Windows user name and password; no prompting.
  
  
• Use default user ID; prompt as needed.
  
  
• Prompt every time.
  
  
• Use Kerberos principal name; no prompting.

The first setting causes any connection that you attempt to make to use the current Windows User ID and password. If you synchronize your Windows user ID and password with the ones stored on your iSeries, this might be the option for you.

The second setting allows you to place a default user ID, and Client Access will only prompt for the password if necessary. Let me explain how this one works. Say that you open Operations Navigator and click an iSeries that you are not logged on to. Operations Navigator will display a prompt dialog and allow you to log in. Next, you start Excel and run a macro that connects to the iSeries, using an ODBC connection that is set to Use Operations Navigator Default as its security setting. This macro can open the connection without supplying a user ID or a password, because the user is already connected to the iSeries, via Client Access, and the password is cached; no dialog will be presented asking for login information. If the user was not previously connected to the iSeries, a dialog box would have been generated asking for an iSeries user ID and password. The password will remain cached until you reboot the machine, so you will not have to supply user ID and password for subsequent connections to the iSeries.

"Prompt every time" is exactly what you expect it to be. It causes any connection attempt to generate a Client Access prompt dialog. This is bad news for connections being made from Active Server Pages, as there is no way for the system to reply to the prompt screen and it hangs the ASP process.

The use Kerberos setting causes the program to use the Kerberos principal name when logging in. Kerberos is a third-party, centralized security authentication method. If you select this option, you will never be prompted.

ODBC DSN Settings That Affect Prompting

When setting up an ODBC DSN, there are several ways to affect prompting. Open a Client Access ODBC Data Source, using the Data Source Administrator in Control Panel, and take a look at the connection options on the first panel. You have the following options available:

• Use Windows User Name
  
  
• Use the User ID specified below
  
  
• None
  
  
• Use iSeries Navigator Default
  
  
• Use Kerberos principal

The "Windows user name," "user ID specified below," and "Kerberos principal" options work exactly as described in the previous section of this article. The "none" option ensures that the cached password and user ID will never be automatically passed to an ODBC connection. The "use iSeries Navigator (or Operations Navigator) default" option causes the ODBC connection to look at the configuration information for the target iSeries to determine how the connection should be implemented.

Using a DSNLess Connection String to Specify Prompting Behavior

When using a DSNLess connection to the iSeries, you can control the prompting behavior with the SIGNON keyword. The SIGNON keyword has the following options:

	0 = Windows User Name
	1 = Default User ID
	2 = None
	3 = User Operations Navigator Default
	4 = Kerberos Principal

If you do not specify a value for this option, the connection will default to use the Operations Navigator default, value 3. Setting the value to 3 will cause the connection to look in the registry for the default user ID for this iSeries and to look in the cache for the password (if you have connected to the iSeries and have not rebooted). If you have connected previously, the program will not prompt. Here is an example VBScript program that connects to an iSeries and uses the default user ID and cached password:

Dim ConStr
set Con1=createobject("ADODB.Connection")
Constr = ""
ConStr = ConStr & "Driver=Client Access ODBC Driver (32-bit);"
ConStr = ConStr & "System=192.168.1.100;"
ConStr = ConStr & "SIGNON=1;"
con1.open constr
msgbox "Connection Opened!"

Note that the above script attempts to pass only the System keyword (not a user ID or password) to specify which iSeries to connect to, and it attempts to pass the SIGNON keyword to specify that the connection should use the iSeries default user ID and cached password.

Now, if you want to pass the currently logged in user, this script would do it:

Dim X
set X = createobject("WSCRIPT.Network")
dim U
U=x.UserName
Dim ConStr
set Con1=createobject("ADODB.Connection")
Constr = ""
ConStr = ConStr & "Driver=Client Access ODBC Driver (32-bit);"
ConStr = ConStr & "System=192.168.1.100;"
ConStr = ConStr & "UID=" & U & ";"
ConStr = ConStr & "SIGNON=1;"
con1.open constr
msgbox "Connection Opened!"

Note that the script above uses the network object to retrieve the current user and then passes the current user using the UID keyword in the connection string.

Where to Go from Here

So those are your options for controlling prompting. The best bet is to use the same account/password on both Windows and the iSeries, so that your security is integrated and you are not exposed to cached passwords. The caching of passwords is a nice option, but if you allow this, any user can come up to a machine and run a program that connects via ODBC without having to use a user ID and password.

Howard F. Arner, Jr., is a writer and consultant with Client Server Development, and author of iSeries and AS/400 SQL at Work. Howard also designed SQLThing Enterprise Edition, a query program and stored procedure editor specifically for the AS/400's unique capabilities. You can purchase a copy of Howard's book or learn more about SQLThing at www.sqlthing.com. Send your SQL and scripting questions to harner@sqlthing.com or go to www.sqlthing.com to find out more about SQL on the iSeries.

Sponsored By ITERA

Echo2 High Availability - Affordable, Reliable, Easy-to-Use Reliable, state-of-the-art high availability isn't as expensive and complex as you might think. Not only does Echo2 cost about half of other solutions, it so automated and self-managing that operation of the system can be done in less than an hour a day and production-to-backup switchovers can be accomplished in minutes. For your Free whitepaper, "An Introduction to iSeries High Availability," call 1-800-957-4511, email us at info@iterainc.com, or visit www.iterainc.com.