Connecting SQL Server Management Studio to QuickBooks Desktop via ODBC Driver

Why use a linked server?

You can use Microsoft SQL Server Management Studio (SSMS) to connect your QuickBooks Desktop data to a SQL Server instance. A linked server is a feature in SQL Server that enables execution of distributed queries across different data sources, including non-SQL Server databases. It allows you to reference external tables—such as those from QuickBooks Desktop—within a single SQL query. With linked servers, you can run commands across different data platforms and integrate them seamlessly with your SQL Server environment. A linked server can be created either through the Object Explorer interface or by executing T-SQL stored procedures.

Key advantages of using linked servers with QuickBooks Desktop:

• Ability to connect to other database instances, whether on the same computer or remote servers.
• Support for running distributed queries across heterogeneous data sources.
• Unified experience when working with diverse data sources.

Requirements

To ensure proper integration with Microsoft SQL Server Management Studio (SSMS), the working environment must meet the following conditions:

• ODBC Driver for QuickBooks Desktop, SQL Server Management Studio, QuickBooks Desktop, and SQL Server must all be of the same bitness (either 64-bit or 32-bit).
• ODBC Driver for QuickBooks Desktop, QuickBooks Desktop, and SQL Server must be installed on the same computer.

Set up a SQL Server user account to connect to QuickBooks Desktop

You can configure a user to run under a specific account in two different ways:

• Use an existing local user account.



• Add the user of a running service to the Administrators group.

After configuring a user account, click OK to confirm your changes. Then restart the SQL Server service.

Retrieve data from QuickBooks Desktop

1. Open QuickBooks files.
• If you have a company file:

Launch QuickBooks Desktop with administrator privileges and click I want to open an existing Company File.

• If you don't have a company file:

In QuickBooks Desktop, click I want to see a sample Company File to download a company file. After the download, navigate to the folder where the file was saved, open the sample database, and then close the application. Now, launch QuickBooks Desktop with administrator privileges and click I want to open an existing Company File to access the previously saved file.


2. Configure a Data Source Name (DSN) for the QuickBooks file.

Open ODBC Data Sources. In the ODBC Data Source Administrator dialog, navigate to the System DSN tab and click Add. In the Create New Data Source dialog, select Devart ODBC Driver for QuickBooks Desktop as the driver for which you want to set up a data source and click Finish.



Next, specify a Data Source Name and set a path to the QuickBooks file you want to use. Click OK to continue.




Don't click Test Connection at this stage.

As a result, the data source is added. Click OK to save the configuration and close the dialog.



3. Define MSDASQL properties.

Run SSMS as an administrator and connect to the server using your local user account. In the Object Explorer, navigate to Server Objects > Linked Servers > Providers > MSDASQL. Then, right-click the MSDASQL provider and select Properties from the menu. In the Provider Options dialog, clear the Allow inprocess checkbox. Click OK to apply the changes.



4. Create a linked server with the preconfigured DSN.

In the Object Explorer, navigate to Server Objects. Right-click Linked Servers and select New Linked Server from the menu. In the New Linked Server dialog, fill in the fields:

• Linked server: Enter the linked server name.
• Provider: Select Microsoft OLE DB Provider for ODBC Drivers from the dropdown list.
• Data source: Specify the Data Source Name (DSN) defined earlier in the Configure a Data Source Name (DSN) for the QuickBooks file step.



Click OK. In the Application permission dialog that appears in QuickBooks Desktop, define the permission levels for ODBC Driver for QuickBooks Desktop to access the QuickBooks company file. Then, click Continue.




In the Object Explorer, navigate to Server Objects > Linked Servers and select the newly created linked server. Expand the node, then navigate to Catalogs > default > Tables, and execute the following query:

SELECT * FROM [linked_server_name].[default]..[Account] 
GO 




Successful execution confirms that the linked server has been added and is functioning correctly.