How to set up an ODBC Data Source on SQL Server for Microsoft Dynamics GP

INTRODUCTION
This article discusses how to set up the ODBC Data Source for Microsoft Dynamics GP or for Microsoft Business Solutions - Great Plains on Microsoft SQL Server 2008, on Microsoft SQL Server 2005, and on Microsoft SQL Server 2000.

MORE INFORMATION
To set up an ODBC data source connection for SQL Server by using the ODBC data sources, follow these steps:

  1. If the operating system is 32-bit follow these steps:
  2. Click Start, and then click Control Panel.
  3. Double-click Administrative Tools, and then double-click Data Sources (ODBC).
  4. If the operating system is x64, follow these steps:
  5. Right-click Start and then click Explore.
  6. Locate the following folder: C:\Windows\SysWOW64
  7. Right-click the Odbcad32.exe file and then click Create Shortcut.
  8. Right-click the shortcut file, and then click Rename.
  9. To rename the shortcut file, type Data Sources (ODBC) 32-bit.
  10. Right-click the shortcut file, and then click Copy.
  11. Expand My Computer, expand Control Panel, and then click Administrative Tools.
  12. Paste the shortcut file in the Administrative Tools folder.
  13. Close Windows Explorer.
  14. Click Start, click Control Panel, double-click Administrative Tools, and then double-click Data Sources (ODBC) 32-bit.
    Note: To clearly identify the names of the shortcut files of the ODBC data sources, rename the shortcut file of the x64 version Data Sources (ODBC) x64.
  15. Click the System DSN tab, and then click Add to create a new ODBC data source.
  16. Follow the appropriate step:
  17. If the computer is running SQL Server 2000, select SQL Server, and then click Finish.
  18. If the computer is running SQL Server 2008 or SQL Server 2005, select SQL Native Client or SQL Native Client 10.0, and then click Finish.

Note: The ODBC DSN must be setup using the SQL Native Client or SQL Native Client 10.0 for Microsoft Dynamics GP 2010.

Note: For information about how to install the SQL Native Client on a client workstation, see the "To install the SQL Native Client" section in this article. The SQL Native Client is required if you will use the Advanced SQL Server Options for users in Microsoft Dynamics GP 9.0 or in a later version. For more information about the Advanced SQL Server Options for users, click the following article number to view the article in the Microsoft Knowledge Base:

922456 Frequently asked questions about the advanced SQL Server options in the User Setup window in Microsoft Dynamics GP

  1. Provide an ODBC name and description. For "Server," enter the instance of SQL Server to which Microsoft Dynamics GP or Microsoft Business Solutions - Great Plains will connect. Then click Next.

Note: For the information that is required in this step, specify the following parameters:

Name: Type the name that you want to use for the data source. This name is stored in the ODBC.ini file. If you use process servers, use the same name for the 32-bit ODBC Data Source for all client workstations.

Description: Type a description of the data source. This field is optional.

Server: Type the name that you assigned to the instance of SQL Server when you installed Microsoft SQL Server to run together with Microsoft Dynamics GP or together with Microsoft Business Solutions - Great Plains. Use either the server name or the IP address for all ODBC setup on each workstation. Do not use the IP address on some workstations and the server name on others.

  1. Click the With SQL Server Authentication using a login ID and password entered by the user option.
  2. In the Connect to SQL Server to obtain default settings for the additional configuration options area, type the user name and password for the sa user.

Note: The sa user name and password are not required. However, we recommend that you use them. If the sa user name and password are entered, the credentials are not stored in the ODBC setup.

  1. Click Client Configuration.
    Note: The Client Configuration option is not available if you create an ODBC connection by using the SQL Native Client. If you are using the SQL Native Client or the SQL Native Client 10.0, proceed to Step 10.
  2. In the Network libraries section, click TCP/IP. Verify that the instance of SQL Server that you are using for or Microsoft Dynamics GP or for Microsoft Business Solutions - Great Plains appears in the Server alias section and in the Server name section. Verify that the Dynamically Determine Port box is cleared, and then enter the appropriate port number. Click OK, and then click Next.
  3. Clear all the options, and then click Next.

Note: Do not change the default database even if you will use this ODBC for only one company.

  1. Clear all the options, and then click Finish.
  2. Verify all the information, and then click Test Data Source. If the connection tests successfully, click OK.
  3. Click OK two times to exit the ODBC Data Source Administrator.

Note: If the SQL Server Browser service is not running, you will receive the following error message when you try to test the ODBC connection:

Connection failed:
SQLState: '08001'
SQL Server Error: -1
[Microsoft][SQL Native Client]SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
Connection failed:
SQLState: 'HYT00'
SQL Server Error: 0
[Microsoft][SQL Native Client]Login timeout expired
Connection failed: SQLState: '08001'
SQL Server Error: -1
[Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

To resolve this issue, follow these steps to start the SQL Browser Service:

  1. Click Start, click Administrative Tools, and then click Services.
  2. Right-click the SQL Server Browser service, and then click Start.

SQL Native Client Installations

To install the SQL Native Client on the client workstation, use one of the following methods:

  1. Follow the instructions in the following Microsoft Knowledge Base article:

910016 Release manifest for Microsoft SQL Server Native Client (9.00.1399.06)

  1. For SQL Server Native Client 10.0, go to the link below, find the Microsoft SQL Server 2008 Native Client section and client the package version to download.
  1. On the SQL Server 2005 CD, open the Servers\Setup folder, and then double-click the Sqlncli.msi file.
  2. On the SQL Server 2008 CD, open the x86\Setup\x86 folder and then double-click the Sqlncli.msi file

APPLIES TO

Microsoft Dynamics GP 2010
Microsoft Dynamics GP 10.0
Microsoft Dynamics GP 9.0