NetSuite Delivers on ODBC

NetSuite provides a read-only ODBC driver (common way to access a databases) to connect to its platform.  We are interested in this because we offer Secure Remote SQL Reporting Services which closes the gap for businesses that wish to fully leverage Cloud Computing but have discovered that NetSuite’s built-in report tools are not quite good enough.

Setup of the NetSuite ODBC Driver requires the following:

  1. Module Add-on Package purchased from NetSuite.
  2. Get the ODBC driver installed on client machines.  The ODBC Driver is available for download on the NetSuite Settings Dashboard. In our case, we configured this with our Remote SQL Reporting services integrated with Windows SharePoint Services.  For the purposes of this discussion, we will use Microsoft Access to illustrate connectivity.  The driver is available for download from the NetSuite dashboard Setting Portlet.
  3. Download and Install OpenSSL offered from Shining Light Productions.  Additional C++ distributables may be required.    These are clearly referenced on the site.  Be sure to donate to help further the cause to keep software costs low.
  4. Configure the Windows ODBC Driver.
  5. Provide your NetSuite user name and password.
  6. Link to interested tables and provide reference to the respective primary key(s).  Use the NetSuite “Table of ODBC Views” guide for reference within the Help system.
  7. Optionally, setup relationships to let the tools help you during the development of your queries.
  8. Retrieve data into your application.

Related Articles

  1. SQL Reporting on NetSuite Data Step-By-Step
  2. Connecting ODBC to Multiple NetSuite Accounts
  3. NetSuite ODBC: “You Don’t Have Permission to Use ODBC.â€
  4. NetSuite 64-bit ODBC Setup Instructions
  5. Extract NetSuite Data for Backup and Reporting
  6. Warning: SQL Server Linked Server to NetSuite Issues
  7. Considering Producing NetSuite Financial Statements using Third Party Tools?
If you need help working with ODBC on NetSuite to drive enhanced reporting, contact us.

Screen Shots

Below are screen shots of the configuration basic steps:

Be Sociable, Share!

Marty Zigman

Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - YouTube

| Tags: , | Category: NetSuite | 40 Comments

40 thoughts on “NetSuite Delivers on ODBC

  1. Alex Rykov says:

    Where can this driver be downloaded? I see it mentioned in the docs. Yet I cannot find it.

  2. Alex Rykov says:

    Found it: Once ODBC feature is enabled, go to the Home page, and in the Settings portlet, click the Download ODBC Driver link.

  3. marunach says:

    Hi Mr Marty,

    I have a sql server 2008 dabaase containing a Datewarehouse. the user would liket to directly conenct to netsuite CRM and join the DW tables with CRM tables.

    1. how to go about it?
    2. should the ODBC driver be installed on client machine or inthe SQL server 2008 server?
    3. can we access the nwetsduite DB from sql management studio?
    4. do we really rewuire the wind32open ssl as in your posting?
    5. any other info i need to take care of?

  4. Marty Zigman says:

    Hello. Let me answer those questions:

    1. You need to get that add-on directly from NetSuite. I believe if you have a development account, they may be able to turn that on. But you are required to pay if it is on a production account.
    2. Where you install the driver depends on your integration approach. Assuming that you will be using SSIS, yes, you will want to install it on the Database Server.
    3. Yes, you will be able to connect to NetSuite and it will look like a regular database.
    4. Yes, as part of the installation, you may need to install some other “connection plumbing”. I believe the latest release, since this first post, has optimized this element.
    5. If you are nervous about the driver, I recommend installing it on a workstation and then connecting with a simple tool like MS Access. This will help you get comfortable with the installation process and the way it works before you touch a Production database.

    Let me know if this makes sense.

    Marty

  5. marunach says:

    Hi Marty, Thanks for the quick response . Much appreciated.

    1. what is the addon you r talking about ?
    i thought just installing the driver would be sufficient enough.

    BTW, Great job your posts.just to compliment on your posts. they were really clear, concise ,simple and fast to understand

  6. Marty Zigman says:

    Yes, there are a number of add-ons that require NetSuite to throw switches. To determine what you have provisioned, go to: Setup, Company, View Billing Information, Add-On Modules. This will help clarify what capacities you have active and what you need to turn on.

  7. Jerome says:

    Marty,
    Thanks for the insight. Do you know where I can find more information on building a Connection String to use inside SSIS ODBC data source instead of using DSN’s? I have looked through the NetSuite help, finding nothing. I have done several web searches, and that is how I found your blog. But not really anything else on this topic. I have NS ODBC Drivers 6.0 (DataDirect) drivers installed and working using the DSN. But I would rather use a dynamic connection string in my SSIS packages.

    Thanks
    Jerome

  8. Jerome says:

    Marty,
    I did a little more digging, and found this gem:
    DataDirect – Specifying ODBC Connection Attributes

    The missing puzzle piece for me was to include the ENC=1 attribute.

    Jerome

  9. Marty Zigman says:

    Thanks Jerome. Appreciate the follow up for the community.

    Marty

  10. ganesh says:

    Hi Marty,

    i got the ODBC driver from “Get the ODBC driver” when i installed it, its not showing up in the ODBC sources. I am using windows 7, 64 Bit

  11. John says:

    Just an FYI, I just spent close to 2 weeks to try to connect to Netsuite from MSSQL 2012 express using the Netsuite ODBC driver 6.0 on my Windows 7 PC…

    The Solution? Call netsuite support they have the 64 bit version of the ODBC driver… It’s Netsuite ODBC 7 created by DataDirect.

    I’ve installed it, set it up and it now works wonders. although I had some complications, I got it to work fairly quickly!

    Thanks
    JG

  12. Marty Zigman says:

    Thanks John. That should help a number of people. I found studying the DataDirect driver helps to diagnose situations.

  13. Marty Zigman says:

    Ganesh,

    Have a look at John’s solution. It appears that you need to get the updated 64 bit driver. If you get a public link, post it here.

    Marty

  14. John says:

    I’ve searched for a public link for over 2 weeks… as soon as I called Netsuite support they knew exactly what I needed and connected to my PC to send me the file.

    When we asked why it wasn’t public they said because DataDirect did it for them and they had some papers to finalize (or something of that matter) … I really didn’t care for the why I just wanted to get it to work after 2 weeks of banging my head on my desk!!!

    Ganesh, That’s exactly the behavior I was getting… If you run c:\windows\sysWOW64\odbcad32 you will be able to see it and edit it… not matter what you do it won’t work until you call netsuite support and get that netsuite odbc 7.00.

  15. Yukesh says:

    Hello Marty,

    Am new to Netsuite.. Trying to extract data from netsuite using SSIS… I tried using Cozy roc s/w but facing issues in it. please suggest me a way ti extract data from Netsuite using SSIS..

  16. Marty Zigman says:

    Hello Yukesh,

    Here is a video on the basics of SSIS. http://www.youtube.com/watch?v=Jl0Vze2_6_o Instead of using Microsoft Access, as suggested in the video, use your NetSuite connection which was setup via your ODBC connection. Remember, you are now in the world of SSIS and there are MANY tools available to get data out of NetSuite. I recommend learning SSIS essentials.

    Marty

  17. Pravar Aseem says:

    Hi im using MS SQL Server 2008 to get data from NetSuite and the test connection is successful. However, the SCHEMA Tables are blank. Can you help?

    Thanks,
    Pravar

  18. Marty Zigman says:

    Hi Pravar,

    Try clicking the options to show system tables on and off. I noticed this problem a couple of times and, by my memory only, I was able to play with the switches to eventually see everything. If you figure it out, let us know.

    Marty

  19. Manjesh says:

    Hi Marty,

    I am trying to download ODBC connection but I am not able to see “Download ODBC Driver” option in my setting. We have already paid for ODBC connection. Do I need to be Admin to download ODBC Driver?
    Any Ideas?
    Thanks in advance.

  20. Marty Zigman says:

    Hello Manjesh,

    Yes, I suspect you need to be an Administrator to get to the area you can download the ODBC driver. These are good questions for NetSuite Support if you can’t find it.

    Let us know how you resolve it.

    Marty

  21. Manjesh says:

    Thanks for your quick response.
    Once my role was changed to administrator I am able to download ODBC connection. But I am not able to install. Once I double click install file I get error message ” Netsuit ODBC Driver 32 bit installer has stopped working”
    Any idea ??

  22. Marty Zigman says:

    Hi Manjesh,

    This is a good NetSuite Support question. I suspect it has something to with your environment and possibly security context.

    Marty

  23. Ramarao says:

    Hi I want to know the what database are used for store the Netsuite data. Is it possible to access the data.

  24. Marty Zigman says:

    NetSuite’s database is Oracle. However, you don’t interact with Oracle directly. Instead, you interact with the data with different APIs. What are you trying to achieve?

    Marty

  25. Ramarao says:

    Hi Marty Zigman,

    I want to integrate data into Netsuite from my legacy system,because I want to backup all the Netsuite data.

    Thank you.

  26. Marty Zigman says:

    Hello Ramarao,

    There are many ways to get data out of NetSuite. The team at Sesame Software has done a very good job and bringing down all NetSuite data into an upadatable SQL Server database. We work with their product team to retrieve “difficult to obtain” data segments.

    Marty

  27. sowjanya says:

    I am trying to read data from Netsuite. I installed “NetSuite Drivers 32bit” ODBC driver. I am building the connection string to connect to the host. can someone suggest how i can specify the Account Id, Role ID ? Pls note that i am not doing it from SSIS, i am a test trying to read data from Netsuite for the purpose of ETL testing.

  28. john lee says:

    Does the Sesame Software bring all NetSuite data include supporting file attachments?

  29. Marty Zigman says:

    Hi John,

    Yes, my understanding is that Sesame system does bring all the data down (we have helped them get to some hard to reach data). File attachments are part of the application.

    Marty

  30. Ryan says:

    I’m curious if the NetSuite ODBC drivers provide access to the System Notes (audit log table). We’re currently using CData but it doesn’t seem to pull down that table and I don’t have the ODBC drivers [yet].

  31. Marty Zigman says:

    Hi Ryan,

    Take a look at the Connect Schema. Doesn’t the “Notes_system” object give what you need?

    Marty

  32. Ryan says:

    Thanks for the reply Marty! Notes_system would give us what we need, however we are currently using CData’s ODBC driver and it doesn’t seem to have access to that table. Based on your reply I’m assuming the NetSuite ODBC driver does in fact include this table.

  33. Hitesh Kumar says:

    Hi Marty Zigman,

    Thanks for great support. i have 10 million records in my SQL database i want to move these to net suite and want two way synchronization as records can be modified by net suite or by application.
    Does SSIS is good option do i need any third party tool rather than ODBC driver

  34. Marty Zigman says:

    Hello Hitesh,

    Good luck on your efforts. Remember, NetSuite’s ODBC offering (now called Suite Analytics) can only allow you to read, not write to the database. We have built a number of tools to do two way sync. But the team that really has this down is Sesame Software as they focus on these types of challenges: http://www.sesamesoftware.com/relational-junction/netsuite-backup/

    Marty

  35. Hitesh says:

    Thanks Marty,
    I got lots of help from your blogs and able to complete most of work.

    Thanks,
    Hitesh Kumar

  36. Nishanth says:

    I see that there are some questions around DataDirect. DataDirect has been acquired by progress. You may want to take a look at the following page: DataDirect ODBC Driver for SQL Server

  37. Denis says:

    Hi guys, my company Devart released a new NetSuite ODBC Driver

    You can download it now. If you have any technical questions please send them here support@devart.com

  38. Ron says:

    Hi All,
    I’m currently evaluating few tools which allow me to extract data out of Netsuite to our local database so that we can report on it.
    Based on the price and feature which one is better between Netsuite SuiteAnalytics and CData Sofware Data Sync. I also see few people recommend Progress Data Direct.

    Sesame software does most of it but currently we only need to extract the data so their price is pretty expensive for us.

    Thanks in advance.

Leave a Reply

Your email address will not be published. Required fields are marked *