How to LOGON with BTEQ if using "Integrated Security" in SQL Assistant

Connectivity
Enthusiast

How to LOGON with BTEQ if using "Integrated Security" in SQL Assistant

Newbie question, but my organization uses SQL Assistant in Windows with Integrated Security for logon to Teradata through either ODBC or .NET. I'd like to start scripting with BTEQ, but I can't successfully enter my logon credentials... How should I construct the string? (Or, how should I enter the logon details in interactive mode?)

 

Basic form of our Teradata server is:

tdinstance.internaldomain.org

 

We use basic NT logons for our organizational access, if that makes a difference.

 

Thanks!


Accepted Solutions
Teradata Employee

Re: How to LOGON with BTEQ if using "Integrated Security" in SQL Assistant

From that, I suspect you omitted the trailing / delimiter from the TDPID (hostname) in the .LOGON, in which case that lone value entered is assumed to be the username (and the TDPID is obtained from the clispb.dat file - defaults to dbc).

1 ACCEPTED SOLUTION
8 REPLIES
Teradata Employee

Re: How to LOGON with BTEQ if using "Integrated Security" in SQL Assistant

Hi AndSpenRob,

 

Maybe configure the LOGMECH before LOGON with BTEQ, something like :

.LOGMECH LDAP
.LOGON tdinstanct.internaldomain.org/user;

There is a lot of information here :

http://downloads.teradata.com/tools/articles/logging-on-teradata-database-sessions-via-bteq

 

Teradata Employee

Re: How to LOGON with BTEQ if using "Integrated Security" in SQL Assistant

Specify Kerberos (or TDNEGO) logon mechanism and omit username and password for single sign-on / integrated security:

.logmech KRB5
.logon tdinstance.internaldomain.org/

In interactive mode, you can simply press enter when prompted for the username and password (without typing anything), or ".set logonprompt off" before ".logon" and just click OK on the CLIv2 logon dialog. For batch mode on Windows, set the GUILOGON environment variable to NO before invoking BTEQ. (You can also set this variable in your user profile, which would also suppress the CLIv2 dialog when using BTEQwin.)

 

SET GUILOGON=NO
bteq <script.file >log.file 2>&1
Enthusiast

Re: How to LOGON with BTEQ if using "Integrated Security" in SQL Assistant

Thanks for the reply!

 

I have attempted both:

  • pressing ENTER to bypass password inquiry (I just get a newline and the prompt hangs), and
  • .SET LOGONPROMPT OFF (I still get the password inquiry with the above prompt hang when I ENTER past it)

 

Behavior seems identical for both KRB5 and TDNEGO. This is for Windows, mind you, so I don't know if that is a factor.

 

Any ideas?

Enthusiast

Re: How to LOGON with BTEQ if using "Integrated Security" in SQL Assistant

Hi Waldar!

 

LDAP works! I still have to key in my credentials, but this will let me test... Thanks!

 

Still looking for a way to use Integrated Security to bypass the need to store my password in the script, though, if you have any ideas. This will be for batch process work that runs overnight to update tables/views.

Teradata Employee

Re: How to LOGON with BTEQ if using "Integrated Security" in SQL Assistant

The "hang" is likely where CLIv2 pops up a GUI dialog on the console desktop to prompt (again) for the "missing" values. You probably need to set GUILOGON=NO in the user or global environment to avoid that.

 

But single sign-on only works while the user is logged in to the desktop. For scheduled batch work launched on an unattended server, consider Teradata Wallet (tdwallet) as a means to store the username & password rather than putting them into the script itself. (Of course, if you change the password for the user, you'll need to go update the wallet string also.)

Enthusiast

Re: How to LOGON with BTEQ if using "Integrated Security" in SQL Assistant

CommandLineIssue.png

Here is the CMD sequence I'm attempting to get this to work. It seems to try to ask for the password regardless, and still "hangs" on blank ENTER in the password field.

 

Does thisGUILOGON setting need to be in the same batch process as the other commands in order to work?

Highlighted
Enthusiast

Re: How to LOGON with BTEQ if using "Integrated Security" in SQL Assistant

CLI Error.PNG

 

Aha! Waiting long enough on the password "hang" produces this error, in case this helps...

Teradata Employee

Re: How to LOGON with BTEQ if using "Integrated Security" in SQL Assistant

From that, I suspect you omitted the trailing / delimiter from the TDPID (hostname) in the .LOGON, in which case that lone value entered is assumed to be the username (and the TDPID is obtained from the clispb.dat file - defaults to dbc).