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:
- Module Add-on Package purchased from NetSuite.
- 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.
- 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.
- Configure the Windows ODBC Driver.
- Provide your NetSuite user name and password.
- 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.
- Optionally, setup relationships to let the tools help you during the development of your queries.
- Retrieve data into your application.
Related Articles
- SQL Reporting on NetSuite Data Step-By-Step
- Connecting ODBC to Multiple NetSuite Accounts
- NetSuite ODBC: “You Don’t Have Permission to Use ODBC.”
- NetSuite 64-bit ODBC Setup Instructions
- Extract NetSuite Data for Backup and Reporting
- Warning: SQL Server Linked Server to NetSuite Issues
- Considering Producing NetSuite Financial Statements using Third Party Tools?
Screen Shots
Below are screen shots of the configuration basic steps:
-
- Download NetSuite ODBC Driver
-
- Windows ODBC Administrator Utility
-
- Advanced ODBC Administration
-
- Access: New Database
-
- Access: Link Data
-
- Access: Select NetSuite ODBC
-
- Access: Select your NetSuite ODBC Data Source
-
- Login with NetSuite Credentials
-
- Access: Available Link Tables
-
- Access: Assign Primary Key
-
- Access: Relationship Manager
-
- Access: Get data from query
See Related Articles
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
Where can this driver be downloaded? I see it mentioned in the docs. Yet I cannot find it.
Found it: Once ODBC feature is enabled, go to the Home page, and in the Settings portlet, click the Download ODBC Driver link.
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?
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
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
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.
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
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
Thanks Jerome. Appreciate the follow up for the community.
Marty
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
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
Thanks John. That should help a number of people. I found studying the DataDirect driver helps to diagnose situations.
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
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.
Here are the instructions to setup the 64-bit ODBC drivers. https://blog.prolecto.com/2012/08/28/netsuite-64-bit-odbc-setup-instructions/
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..
Hello Yukesh,
Here is a video on the basics of SSIS. https://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
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
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
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.
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
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 ??
Hi Manjesh,
This is a good NetSuite Support question. I suspect it has something to with your environment and possibly security context.
Marty
Hi I want to know the what database are used for store the Netsuite data. Is it possible to access the data.
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
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.
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
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.
Does the Sesame Software bring all NetSuite data include supporting file attachments?
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
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].
Hi Ryan,
Take a look at the Connect Schema. Doesn’t the “Notes_system” object give what you need?
Marty
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.
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
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: https://www.sesamesoftware.com/relational-junction/netsuite-backup/
Marty
Thanks Marty,
I got lots of help from your blogs and able to complete most of work.
Thanks,
Hitesh Kumar
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
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
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.