In a new data warehousing project that I'm involved with, I ran into some challenges connecting to and pulling data from NetSuite ODBC server using SuiteAnalytics Connect. It was quite a journey to finally find the right tool, as many SQL Clients - including the one from Oracle and Microsoft - were not as robust and flexible to connect to NetSuite's ODBC.
After digging around, I found an "Universal Database Tool" called "DBeaver" that is free for developers, SQL programmers, database administrators and analysts. It was very flexible that I could connect to any database if I had the JDBC available.
And I wanted to share these steps (as detailed as possible), hoping that no one else runs into the issues I had and waste so much time.
These steps are based on MacOS, but it's not very different on Windows either. If you need the guide to connect to NetSuite using DBeaver on Windows, please let me know.
1) Download and install DBeaver Community from https://dbeaver.io/
2) Download and install SuiteAnalytics Connect - refer to SuiteAnswers article 38965 and related topics. I downloaded and extracted JDBC for MacOS since ODBC isn't available.
3) Launch DBeaver Community. We need to add NetSuite driver. Go to Database > Driver Manager, and click New.
4) Give it a proper name and description. Set Class Name to com.netsuite.jdbc.openaccess.OpenAccessDriver and leave the URL Template and Default Port blank. Click "Add File" and choose the NQjc.jar file that was extracted from step 2). Click OK.
5) Once the driver is added, you can hit OK to exit from the driver manager window. Click on New Connection or go to Database > New Connection. Select the driver you created in step 4), and hit Next.
6) Enter below URL to JDBC URL field, and add your user name and password. Account Id and Role Id can be found on "Set Up SuiteAnalytics Connect" page.
jdbc:ns://odbcserver.na1.netsuite.com:1708;ServerDataSource=NetSuite.com;encrypted=1;Ciphersuites=TLS_RSA_WITH_AES_128_CBC_SHA;CustomProperties=(AccountID={accountId};RoleID={roleId})
7) When you click on Test Connection... you will see a Success box if things went well!
12 comments:
THANK YOU - This was VERY helpful, but needs a few updates, since NetSuite appears to have changed its connection requirements.
I found this article extremely helpful:
https://5291968.app.netsuite.com/app/help/helpcenter.nl?fid=section_4425626714.html
Mainly, the string requirements have changed. You can no longer include the Cipersuites parameter (as of Nov'19) and the Service name (right after JDBC:NS: needs to match whatever is called out on YOUR NETSUITE CONNECT SERVICE, not as it is in this blog.
Thank you for pointing it out. I've been lacking on updates apparently. I will make the correction soon, but you will have to use your domain-specific address for your JDBC connection now.
Thank you for posting this.
Unfortunately i get error 'Login has timed out'.
Also am using an Oracle connection setting dialog box. As, I could not find how to use generic JDBC connection in Dbeaver 6.0.1.
Thank you for reading my post! I think the time out is related to the server URL. I am not covering NetSuite related posts here on my personal blog anymore, and some information are outdated. Please refer to the updated post below:
https://blog.rowa.tech/2018/10/connecting-and-navigating-netsuite-with.html
Also, I think some NetSuite datacenters had service incidents recently. I wonder if your time was just within that outage?
THANK YOU
Just wanted to say thanks! :)
I love you random person on the internet!
Please follow anonymous comment January 19, 2020 at 10:27 PM for updated requirements... Worked on date stamp
Thanks a lot for this!!! :)
Thanks for this post---as of today I can use this, if I include the updates around (a) losing the cipher string; (b) ensuring I'm calling my actual NS URL. Super helpful in the clutch!!
Thank you! I finally setup using JDBC driver in mac with updated Netsuite2.com and things work fine. I'm able to view the tables and execute queries within DBeaver
Helllo! Than you very much for your post, helped me greatly
One thing though, the curly braces are not needed on AccountID and RoleID anymore.
With the new JDBC driver, connection string looks something like this:
jdbc:ns://serverName:1708;ServerDataSource=NetSuite2.com;encrypted=1;CustomProperties=(AccountID=accountID;RoleID=roleID)
Thank you
Post a Comment