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!
