Issues Programmatically Adding ODBC DSN to ODBC Administrator on Windows

Question

I am working on trying to automate some configuration, and as part of that we need to add an ODBC DSN through a script. The driver I'm trying to use is the Cloudera Impala ODBC Connector, downloaded from here. All of the machines this will run on will run Windows, most of them Windows 8.1 or 10. I've tested everything below on Windows 7 (where possible) and Windows 10, with the same results. I can create a DSN on this machine manually, so the driver seems to be installed correctly. I have also tried all of these with both user DSNs and system DSNs with no luck.

According to here, there are three ways to do this. The first is messing with the registry directly, which I'd like to avoid if possible.

The second is using odbcconf. The issue is, when I run the command below on both Windows 7 and Windows 10, it comes up with a configuration window for ODBC Administrator, but none of the values I specified are filled in. If I create a DSN for SQL Server, this does work, however, so it seems to be driver specific. I've double checked that I'm setting things right, but not even the name is getting set. See screenshot below for what it looks like.

odbcconf CONFIGSYSDSN "Cloudera ODBC Driver for Impala" "DSN=Testing|Server=server"

Screenshot of Window Which Opens

The third is to use a Powershell CmdLet, Add-OdbcDsn. When I run the following command for either SQL Server or Cloudera Impala, it just hangs, not returning even after 10 minutes. Powershell's resource usage is essentially 0, and I'm not seeing any activity on the system. No DSN shows up in ODBC Administrator.

Add-OdbcDsn -Name "testing" -Driver "SQL Server" -DsnType "System"

Any ideas on how either of these can be resolved so I can move forward?

Answer 1

So since no one answered this question, I'm going to go ahead and fill in what I ended up doing below, for people such as @gimpy who may be looking for a similar answer.

I ended up just going the route of registry keys, with a .reg file that is run to add the correct ODBC information. See below for a sample of what I put in the .reg file to make this work.

I'm going to mark this answer as the solution until/if someone is able to give me an answer to one of the other two approaches above, which I would find preferrable.

Windows Registry Editor Version 5.00 

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources] 

"Non-Prod Impala Connection"="Cloudera ODBC Driver for Impala" 



[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\Non-Prod Impala Connection] 

"AllowHostNameCNMismatch"="1" 

"AllowSelfSignedServerCert"="1" 

"AsyncExecPollInterval"="10" 

"AuthMech"="1" 

"AutoReconnect"="1" 

"CheckCertRevocation"="1" 

"DelegateKrbCreds"="1" 

"DelegationUID"="" 

"DESCRIPTION"="" 

"Driver"="Cloudera ODBC Driver for Impala" 

"EnableSimulatedTransactions"=0 

"Host"="<ImpalaHost>" 

"KrbFQDN"="_HOST" 

"KrbRealm"="<Domain>" 

"KrbServiceName"="impala" 

"LCaseSspKeyName"="" 

"Port"="21050" 

"RowsFetchedPerBlock"="10000" 

"Schema"="<Database>" 

"ServicePrincipalCanonicalization"="1" 

"SocketTimeout"="30" 

"SSL"="1" 

"StringColumnLength"="32767" 

"TrustedCerts"="<CertificatePath>\rootca.cert.pem" 

"TSaslTransportBufSize"="1000" 

"UID"="" 

"UseKeytab"="0" 

"UseNativeQuery"="0" 

"UseOnlySSPI"="0" 

"UseSASL"="1" 

"UseSQLUnicodeTypes"="0" 

"UseSystemTrustStore"="1"