1

I am using Python3.6 and pyodbc to try and connect to Teradata. I need this for a script I want to hand off to an end user to update from data. I would prefer that each user not have to be instructed to setup their own DSN connection with a specific name I hard encode into the script.

I think I have the driver correct now as well as the server and DB name. What I can't figure out is how to get the connection string to not require a username and password. Helpful info: This is for windows OS. When I go to ODBC connections in windows, connection mechanism is listed as LDAP and i can connect through the Teradata program as well.

What does work: Connection directly through the Teradata program (showing Teradata itself knows who I am) Using DSN="DSN_name" where DSN_Name is the name under ODBC connections found in windows. Showing I can connect via DSN.

-Looking at ODBC Data Source Admin shows Driver matches what I have. "Server Name or ID" matches "servername" below.

Tried:

connect_string = 'Driver={Teradata Database ODBC Driver 
16.20};DBCName=servername;Database=db_name;MechanismName=LDAP;UseIntegratedSecurity=1;'
con = pyodbc.connect(connect_string)

which gives:

pyodbc.Error: ('HY000', '[HY000] [Teradata][ODBC] (11210) Operation not allowed during the transaction state. (11210) (SQLExecDirectW)')

connect_string = 'Driver={Teradata Database ODBC Driver 
16.20};DBCName=servername;Database=db_name;MechanismName=LDAP;'
con = pyodbc.connect(connect_string)

or

connect_string = 'Driver={Teradata Database ODBC Driver 
16.20};DBCName=servername;Database=db_name;Authentication=LDAP;'
con = pyodbc.connect(connect_string)

which gives: pyodbc.InterfaceError: ('28000', '[28000] [Teradata][ODBC Teradata Driver] (2) Unable to logon with Authentication Mechanism selected. (2) (SQLDriverConnect)')

I have also tried a few other suggested authentication mechanisms in addition to LDAP, but this is the one listed in Teradata and ODBC connections.

In SQL_Server I use something like trusted_connection=yes to achieve the same effect I desire which doesn't seem to work in the above examples.

error:
pyodbc.OperationalError: ('08001', '[08001] [Teradata][ODBC] (10380) Unable to establish connection 
with data source. Missing settings: {[Password] [Username]} (10380) (SQLDriverConnect)')
brian_ds
  • 317
  • 4
  • 12
  • When you connect through ODBC, are you being challenged for user id and password? – Andrew Oct 24 '19 at 19:14
  • What do you mean through ODBC? When I connect using DSN=Name, I don't get challenged for a uid and pword. My DSN Connection has a saved version of both. Can you give an example if that isn't what you mean? – brian_ds Oct 24 '19 at 19:35
  • I can only assume that pyodbc can't get the id/password from your registry. Which is probably a good thing. – Andrew Oct 24 '19 at 20:15
  • If you have a DSN that works then try using the `to_text` method of [dump_dsn](https://github.com/gordthompson/dump_dsn) to spit out the values and use them to create a DSN-less connection string. – Gord Thompson Oct 24 '19 at 20:17
  • Single sign-on requires configuring Kerberos authentication between the database instance and Windows AD. Are you able to connect using your DSN with the checkbox selected and Username/Password blank? The MechanismName / Authentication keyword should not be specified with UseIntegratedSecurity=Y. Username and Password must also be omitted from the connection string, since including them would imply a third-party sign-on, which is not supported. – Fred Oct 25 '19 at 17:20
  • @Fred, sorry I don't know much about that side of things. I am a data guy working on a project that happens to need a little ODBC stuff. What check mark? I found one under ODBC connections labeled integrated security. If I click that, it fails. I am thinking the best I may be able to do is store the script in a folder that has read access only to a select number of individuals - Then hard code the username and password for our anonymous user. Not perfect, but for the project that level of security may be enough. – brian_ds Oct 28 '19 at 14:52
  • Integrated Security / Single Sign-on requires additional configuration steps on both the Teradata database nodes and Active Directory (or other Kerberos KDC). If your site has not done that, then SSO is not an option. You might also look at Teradata Wallet (tdwallet) as a way to obscure the password with ODBC. Or if you use the teradatasql package to make the connection instead of pyodbc + Teradata ODBC Driver, there is a different form of Stored Password Protection (TJEncryptPassword) available. – Fred Oct 28 '19 at 15:02

0 Answers0