How to create a custom ODBC data provider on your Datazen server

I was going to wait and wrap this into a blog post about setting up SAP HANA, but this is pretty important for folks wanting to use various ODBC data sources, so I wanted to get this out ASAP.

Out of the box, the Datazen server has the ability to connect via ODBC via some pre-configured options (Oracle, MySQL, etc.).  There is also a catch-all option named “Generic ODBC DSN” available. 

The issue with this is, you can’t pass-through a username and password using this option.  This becomes a big problem if you can’t save that information in your DSN information for your ODBC data source.  In fact, I’ve had that issue with two different times, once setting up a connection to SAP HANA, and the other connecting to Azure HDInsight via Hive ODBC.  I got an error message like the following –

There is an easy resolution for this, but it’s a little tricky if you haven’t done it before, which I am assuming a lot of folks have not.  Datazen provides the ability to extend the data providers you get out of the box by allowing you to create a custom data provider.  So, I needed to create a custom data provider file that allowed me to pass-through a username/password along with the DSN name to authenticate against the data source. 

I created an XML file that looked like the following so I could passthrough the Username/Password –

<dataproviderschema>

  <id>ODBC.DSNWITHUIDPWD</id>

  <enabled>true</enabled>

  <name>Generic ODBC DSN With Uid/Pwd</name>

  <type>odbc</type>

  <properties>

    <property>

      <name>DSN</name>

    </property>

    <property>

      <name>UID</name>

      <maskedForNonOwner>true</maskedForNonOwner>

    </property>

    <property>

      <name>PWD</name>

      <masked>true</masked>

    </property>

  </properties>

</dataproviderschema>

Then, I needed to create a new subfolder called dataproviderschemas on the Datazen Server at the following location –

C:\Program Files\Datazen Enterprise Server\service\

 

You can create a subfolder to house it in for better file mgmt of the providers you eventually create, so my final save location looked like the following –

 

C:\Program Files\Datazen Enterprise Server\service\dataproviderschemas\ODBC\dsnwithuidpwd.xml

 

Then I simply restarted the control panel instance and lo and behold, I had my new custom data provider all ready to accept username/password as part of the connection string –

 

That was all I needed to do – I was able to setup my ODBC connections to both SAP HANA and Azure HDInsight without issue, and I’ll walkthrough each of those in future posts.  One final note – you can only use 64-bit ODBC drivers with your Datazen Server, so keep that in mind when you are downloading and setting up drivers on the server.

 

If you want to download the XML file I used, here is a link to do so – http://1drv.ms/1Pg9gqu

 

Special thanks to Corey Cahill on the Datazen team for his assistance with this post. 

 

Have fun at Ignite if you’re going!