Using Cloudera Hive ODBC Driver with SSIS run as account

[#1]

Hello community!

I have a weird issue where we are on Windows 2016 server that also runs Microsoft SSIS services, and I have installed the Cloudera ODBC Driver for Apache Hive v2.6.4.1004.    When I install the ODBC with my personal account it will configure and test fine, but when we run a SSIS package using a different Run AS account that is not my personal AD account it fails with this error:

ERROR 1300 Connection::SQLDriverConnectW: [Cloudera][DriverSupport] (1030) Failed to decrypt data: Key not valid for use in specified state.

So my solution was to uninstall the ODBC driver on the windows server and re-install it using the Run As Service account we intend to use with SSIS.   

I would like to know if this is intentional or I have a desktop driver when i should be using a server driver?   Normally i do not like to log on to servers and install drivers with Service accounts.

Any information would be greatly appreciated!

Thanks,
Jesse

[#2 · Accepted solution]

Hi Jesse,

There seems to be some encryption enabled at your end, that does not allow the other user to access the ODBC driver connection.

This could be some Windows level configuration or some security parameter.

Found below article, relevant to the similar error:

https://stackoverflow.com/questions/30886839/key-not-valid-for-use-in-specified-state-how-to-load-pr...

https://bytes.com/topic/asp-net/answers/566477-dpapi-decrypt-error-decryption-failed-key-not-valid-u...

[#3 · Accepted solution]

Hey @Jesse_s,

You can check the below link for documentation:

https://www.cloudera.com/downloads.html

Also, please accept the answer, if it helped.

[#4 · Accepted solution]

Well my systems guys have not figure out what causes this with our ODBC but at least i know i have the correct drivers and that I just have to install the driver as the service account we intend to run SSIS packages with

[#5 · Accepted solution]

Hi Jesse,

There seems to be some encryption enabled at your end, that does not allow the other user to access the ODBC driver connection.

This could be some Windows level configuration or some security parameter.

Found below article, relevant to the similar error:

https://stackoverflow.com/questions/30886839/key-not-valid-for-use-in-specified-state-how-to-load-pr...

https://bytes.com/topic/asp-net/answers/566477-dpapi-decrypt-error-decryption-failed-key-not-valid-u...

[#6]

Ok thanks!  I'll reach out to our windows / security team and see if they have any ideas of what could cause it.   I just wanted to make sure I had the correct cloudera driver download for Servers,  but seems like there is only 1 odbc hive dl available.

On a side note, I also get a http 404 when trying to access the install guide on the site:

https://docs.cloudera.com/documentation/other/connectors/hive-odbc/2-6-1/Cloudera-ODBC-Driver-for-Ap...

[#7 · Accepted solution]

Hey @Jesse_s,

You can check the below link for documentation:

https://www.cloudera.com/downloads.html

Also, please accept the answer, if it helped.

[#8 · Accepted solution]

Well my systems guys have not figure out what causes this with our ODBC but at least i know i have the correct drivers and that I just have to install the driver as the service account we intend to run SSIS packages with

[#9 · Accepted solution]

Hello, community,

We received the same error when trying to run scheduled jobs with a service account, this was solved after we stumbled upon the "Password Options..." button in the Cloudera drivers. There you can select to encrypt the credentials for just the current user or for all users on the machine. Choosing the later solved the issue for us.

Hope this info helps others,

Bjorn de Jong

[#10]

Thank you Bjorn! saved me a headache in production when we go there.

This setting worked for our SSIS users and service accounts also.  I never even thought to tie that error message with that setting.