300字范文 > odbc驱动程序配置失败_如何使用ODBC驱动程序配置链接服务器

odbc驱动程序配置失败_如何使用ODBC驱动程序配置链接服务器

时间：2023-01-24 23:38:08

odbc驱动程序配置失败

Microsoft Open Database Connectivity (ODBC) is an application programming interface (API) designed to access data from a different of database management systems (DBMS). ODBC is designed for relational data stores.

Microsoft开放数据库连接（ ODBC ）是一种应用程序编程接口（ API ），旨在访问来自不同数据库管理系统（ DBMS ）的数据。 ODBC专为关系数据存储而设计。

In this article, we will explain how to install the appropriate ODBC drivers for SQL Server, how to configure ODBC to connect to a SQL Server instance and how to create and configure a Linked Server using the ODBC driver and the MSDASQL provider to query tables on a SQL Server instance.

在本文中，我们将解释如何为SQL Server安装适当的ODBC驱动程序，如何配置ODBC以连接到SQL Server实例，以及如何使用ODBC驱动程序和MSDASQL提供程序来创建和配置链接服务器来查询表。 SQL Server实例。

The diagram below explains the flow of connecting to a SQL Server database using the MSDASQL provider and a SQL Server ODBC driver.

下图说明了使用MSDASQL提供程序和SQL Server ODBC驱动程序连接到SQL Server数据库的流程。

MSDASQL allows OLE DB consumer applications to use ODBC drivers to connect to a data source. The provider transforms incoming OLE DB calls into ODBC calls, and transfers them on to the specified ODBC driver. After that, it retrieves results from the ODBC driver and formats those into OLE DB specific structures which the consumer has access to them.

MSDASQL允许OLE DB使用者应用程序使用ODBC驱动程序来连接到数据源。提供程序将传入的OLE DB调用转换为ODBC调用，并将其转移到指定的ODBC驱动程序上。此后，它将从ODBC驱动程序中检索结果，并将结果格式化为用户可以访问的OLE DB特定结构。

为SQL Server安装和配置Microsoft ODBC驱动程序 (Installing and configuring the Microsoft ODBC drivers for SQL Server)

From this link, the Microsoft ODBC drivers for SQL Server can be downloaded and installed. Depending on which Windows operating system version (32-bit or 64-bit) is used, there are two versions of the Microsoft ODBC drivers for SQL Server that can be installed:

通过此链接，可以下载和安装SQL Server的Microsoft ODBC驱动程序。根据所使用的Windows操作系统版本（32位或64位），可以为SQL Server安装两个版本的Microsoft ODBC驱动程序：

x64\msodbcsql.msi is for the Windows 64-bit version x64 \ msodbcsql.msi适用于Windows 64位版本 x86\msodbcsql.msi is for the Windows 32-bit version x86 \ msodbcsql.msi适用于Windows 32位版本

In case that the wrong version of the Microsoft ODBC drivers for SQL Server is trying to be installed (e.g. x86\msodbcsql.msi on the Windows 64-bit version), the following warning message will appear:

如果尝试安装错误版本SQL Server的Microsoft ODBC驱动程序（例如Windows 64位版本上的x86 \ msodbcsql.msi），则会出现以下警告消息：

Let’s install the appropriate Microsoft ODBC drivers for SQL Server. The process of installation of the Microsoft ODBC drivers for SQL Server is very simple and straightforward. Double click on the (e.g. msodbcsql.msi ) file and the welcome screen will appear:

让我们为SQL Server安装适当的Microsoft ODBC驱动程序。用于SQL Server的Microsoft ODBC驱动程序的安装过程非常简单明了。双击（例如msodbcsql.msi）文件，将出现欢迎屏幕：

Click theNextbutton to proceed with installation. Under theLicense Agreementwindow read the license terms and if you agree, check the “I accept the terms in the license agreement” radio button and click theNextbutton:

单击下一步按钮继续安装。在“许可协议”窗口下，阅读许可条款，如果同意，请选中“我接受许可协议中的条款”单选按钮，然后单击“下一步”按钮：

On theFeature Selectionwindow, select theClient Componentsitem and click theNextbutton:

在“功能选择”窗口上，选择“客户端组件”项，然后单击“下一步”按钮：

Click theInstallbutton on theReady to Install the Programwindow to begin the installation of the Microsoft ODBC drivers for SQL Server:

单击“准备安装程序”窗口上的“安装”按钮，开始为SQL Server安装Microsoft ODBC驱动程序：

In a few seconds, the installation of the Microsoft ODBC drivers for SQL Server finishes. Click theFinishbutton to close the Microsoft ODBC drivers for SQL Server Setup window:

几秒钟后，用于SQL Server的Microsoft ODBC驱动程序的安装完成。单击完成按钮以关闭“ SQL Server安装程序”的Microsoft ODBC驱动程序窗口：

To confirm that the Microsoft ODBC drivers for SQL Server is installed, go to Control Panel and under the Program and Features find the Microsoft ODBC drivers for SQL Server:

若要确认已安装用于SQL Server的Microsoft ODBC驱动程序，请转到“控制面板”，然后在“程序和功能”下找到用于SQL Server的Microsoft ODBC驱动程序：

Or open the ODBC Data Source Administrator dialog box:

或打开“ ODBC数据源管理器”对话框：

Under theDriverstab, check iif theODBC Driver for SQL Serverdriver exists:

在“驱动程序”选项卡下，检查是否存在用于SQL Server的ODBC驱动程序驱动程序：

The ODBC Data Source Administrator dialog box is used to create and manage ODBC data sources. The ODBC Data Source Administrator dialog box is a Windows component.

“ ODBC数据源管理器”对话框用于创建和管理ODBC数据源。 “ ODBC数据源管理器”对话框是Windows组件。

To open the ODBC Data Source Administrator dialog box, go to theStartmenu underWindows Administrative Tools, chooseODBC Data Sources:

要打开“ ODBC数据源管理器”对话框，请转到Windows管理工具下的“开始”菜单，选择“ODBC数据源”：

In theControl PanelunderAdministrative Tools, choose appropriateODBC Data Sources:

在“控制面板”的“管理工具”下，选择适当的ODBC数据源：

Or simpler, in the Windows search box, type theODBC Data Sourcesword:

或更简单的是，在Windows搜索框中，键入ODBC数据源一词：

And from the search list, choose appropriate:

然后从搜索列表中选择合适的：

As you may notice, there are two versions of theODBC Data Sources Administratordialogboxes:one is theODBC Data Sources Administrator (32-bit)and another is theODBC Data Sources Administrator (64-bit)version.Since Windows 8 is the operating system, there are two the ODBC Data Sources Administrator dialog boxes, one is for 32-bit ODBC Data Sources and the other is for 64-bit ODBC Data Sources.

您可能会注意到，有两个版本的“ODBC数据源管理器”对话框盒子：一个是ODBC数据源管理器（32位），另一个是ODBC数据源管理器（64位）版本。由于Windows 8是操作系统，因此有两个“ ODBC数据源管理器”对话框，一个用于32位ODBC数据源，另一个用于64位ODBC数据源。

Note: If theODBC Data Sources Administrator (64-bit)dialogfor creating connection with 32-bit data source (e.g.32-bit SQL Server) is used, the following error may appear when a linked server is created:

注意：如果“ODBC数据源管理器（64位）”对话框使用32位数据源（例如32位SQL Server）创建连接时，创建链接服务器时可能会出现以下错误：

The linked server has been created but failed a connection test. Do you want to keep the linked server?

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

链接服务器已创建，但连接测试失败。您要保留链接服务器吗？

附加信息：

执行Transact-SQL语句或批处理时发生异常。（Microsoft.SqlServer.ConnectionInfo）

Cannot initialize the data source object of OLE DB provider “MSDASQL” for linked server “SERVERX86”.

OLE DB provider “MSDASQL” for linked server “SERVERX86” returned message “[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application”. (Microsoft SQL Server, Error: 7303)

无法初始化链接服务器“ SERVERX86”的OLE DB提供程序“ MSDASQL”的数据源对象。

链接服务器“ SERVERX86”的OLE DB提供程序“ MSDASQL”返回消息“ [Microsoft] [ODBC Driver Manager]指定的DSN包含驱动程序和应用程序之间的体系结构不匹配”。（Microsoft SQL Server，错误：7303）

To create a new SQL Server ODBC data source, press theAddbutton under theUser DSNorSystem DSNtab. Both of these tabs are used to create a connection to ODBC data source, the difference is that theUser DSNtab will show data source only for a currently logged user on this computer. While creating data sources under theSystem DSN,it will be available to any user that is logged on to this computer.

若要创建新SQL Server ODBC数据源，请按“用户DSN”或“系统DSN”选项卡下的“添加”按钮。这两个选项卡均用于创建与ODBC数据源的连接，不同之处在于，“用户DSN”选项卡将仅针对此计算机上当前登录的用户显示数据源。在系统DSN下创建数据源时，所有登录此计算机的用户都可以使用它。

For the purpose of this article, the SQL Server ODBC data source will be created under theSystem DSNtab. To start creating connection, press theAddbutton:

出于本文的目的，将在“系统DSN”选项卡下创建SQL Server ODBC数据源。要开始创建连接，请按添加按钮：

This will open theCreate New Data Sourcedialog, from the list, choose a driver for which you want to set up a data source. In our case, that will beODBC Driver 13 for SQL Server:

这将打开“创建新数据源”对话框，从列表中选择要为其设置数据源的驱动程序。在我们的例子中，它将是用于SQL Server的ODBC驱动程序13：

After selecting a desired driver, click theFinishbutton, this will open theCreate aNew Data Source to SQL Serverdialog:

选择所需的驱动程序后，单击“完成”按钮，这将打开“向SQL Server创建新数据源”对话框：

Under theNamebox, enter the connection name, the name can be whatever you like (e.g. TestServer). TheDescriptionbox is optional and it’s for a description of the data source. In theServerfield, enter a name of a SQL Server for which you want to connect to (e.g. WIN10\SQLEXPRESS):

在“名称”框中，输入连接名称，该名称可以是您喜欢的任何名称（例如TestServer）。 “描述”框是可选的，用于描述数据源。在“服务器”字段中，输入要连接SQL Server的名称（例如WIN10 \ SQLEXPRESS）：

If a SQL Server instance is the default instance, type the name of the computer that hosts the instance of SQL Server(e.g. WIN10). If SQL Server is a named instance, type the name of the computer and the name of the instance separated by a slash (e.g. WIN10\SQLEXPRESS). Otherwise, the following message will appear when testing the SQL Server ODBC data source connection and the name of the SQL Server instance is wrong:

如果SQL Server实例是默认实例，请键入承载SQL Server实例的计算机的名称（例如WIN10）。如果SQL Server是命名实例，请键入计算机名称和实例名称（以斜杠分隔）（例如WIN10 \ SQLEXPRESS）。否则，在测试SQL Server ODBC数据源连接并且SQL Server实例的名称错误时，将出现以下消息：

After setting the appropriate Server and connection name, press theNextbutton. In this dialog, how to connect to SQL Server by using Windows authentication can be specified, including Active Directory authentication or using SQL Server authentication.

设置适当的服务器和连接名称后，按“下一步”按钮。在此对话框中，可以指定如何使用Windows身份验证连接到SQL Server，包括Active Directory身份验证或SQL Server身份验证。

For the purpose of this article, SQL Server authentication will be used. Check theWith SQL Server authentication using a login ID and password entered by the user radiobutton and, in theLogin IDandPasswordfield, enter appropriate credentials and press theNextbutton:

就本文而言，将使用SQL Server身份验证。选中使用用户单选按钮输入的登录ID和密码进行SQL Server身份验证，然后在“登录ID和密码”字段中输入适当的凭据，然后按“下一步”按钮：

On this dialog, set the database that you want to connect to, by checking theChange the default database tocheck the box and from the combo box, choose a deserted database.

在此对话框中，设置您要连接的数据库，方法是选中“将默认数据库更改为”复选框，然后从组合框中选择一个废弃的数据库。

During the change of the default database the following message may appear:

在更改默认数据库的过程中，可能会显示以下消息：

This means that communication link between the driver and the data source to which the driver was attempting to connect failed.

这意味着驱动程序和驱动程序试图连接到的数据源之间的通信链接失败。

To resolve this, close theODBC Data Sources Administratordialogboxand try again to create connection to ODBC data source.

若要解决此问题，请关闭“ODBC数据源管理器”对话框框并再次尝试创建与ODBC数据源的连接。

If that isn’t successful, check if Named Pipes under theProtocolsfor SQL Server for which you want to create a connection to (e. g. SQLEXPRESS) are enabled:

如果不成功，请检查是否启用了要为其创建连接SQL Server协议下的命名管道（例如SQLEXPRESS）：

Don’t forget to restart SQL Server under the SQL Server Services after enabling Named Pipes in order to changes have effect.

启用命名管道以使更改生效后，请不要忘记在SQL Server服务下重新启动SQL Server。

Make sure that theSQL Server and Windows Authentication moderadio button under theSecuritytab of theServer Propertiesdialog is checked:

确保选中“服务器属性”对话框“安全性”选项卡下的“SQL Server和Windows身份验证模式”单选按钮：

Also, check if theAllow remote connections to this servercheck box under theConnectionstab is checked:

另外，检查是否选中了“连接”选项卡下的“允许与此服务器进行远程连接”复选框：

After these settings are done, under theChange the default database tocombo box, available databases will appear, choose a database (e.g. AdventureWorks) and click theNextbutton:

完成这些设置后，在将默认数据库更改为组合框下，将出现可用的数据库，选择一个数据库（例如AdventureWorks），然后单击下一步按钮：

This will open one more dialog with more options to set. These options will be left as it is and click theFinishbutton:

这将打开一个对话框，其中包含更多设置选项。这些选项将保持不变，然后单击“完成”按钮：

After, clicking theFinishbutton, the ODBC Microsoft SQL Server Setup window will appear with configuration information that we set for the ODBC data source. At the bottom of the ODBC Microsoft SQL Server Setup window, there is the Test Data Source button, by clicking on this button, it can be checked if the connection with the data source is established successfully or not:

之后，单击“完成”按钮，将显示“ ODBC Microsoft SQL Server安装程序”窗口，其中包含我们为ODBC数据源设置的配置信息。在“ ODBC Microsoft SQL Server安装程序”窗口的底部，有“测试数据源”按钮，通过单击此按钮，可以检查与数据源的连接是否成功建立：

In case that connection with data source is established successfully, on theSQL Server ODBC Source Testwindow, the “TESTS COMPLETED SUCCESSFULLY” message will appear:

如果成功建立了与数据源的连接，则在“SQL Server ODBC源测试”窗口上，将显示“ TESTS COMPLETED SUCCESSFULLY”消息：

To confirm that the ODBC data source connection is created, in the ODBC Data Source Administrator dialog box under theSystem DSNtab, the name of the ODBC data source that was created will appear:

若要确认已创建ODBC数据源连接，在“系统DSN”选项卡下的“ ODBC数据源管理器”对话框中，将显示已创建的ODBC数据源的名称：

删除ODBC数据源 (Deleting an ODBC data source)

To delete ODBC data source in the ODBC Data Source Administrator dialog box, select the desired item from User DSN or System DSN tab and click theRemovebutton:

要在“ ODBC数据源管理器”对话框中删除ODBC数据源，请从“用户DSN”或“系统DSN”选项卡中选择所需的项，然后单击“删除”按钮：

As you may notice, under the System DSN tab, the LocalServer name is selected, but the Remove button is disabled. This happens because we are trying to delete a 32-bit System DSN from 64-bit ODBC Data Source Administrator dialog box. To delete 32-bit DSN, open the 32-bit ODBC Data Source Administrator dialog box and click the Remove button:

您可能会注意到，在“系统DSN”选项卡下，已选择LocalServer名称，但是“删除”按钮被禁用。发生这种情况是因为我们试图从64位ODBC数据源管理器对话框中删除32位系统DSN。要删除32位DSN，请打开“ 32位ODBC数据源管理器”对话框，然后单击“删除”按钮：

Now, when the ODBC data source is created, let’s create andconfigure a Linked Server using the ODBC.

现在，当创建ODBC数据源时，让我们使用ODBC创建和配置链接服务器。

Creating and configure a Linked Server using the ODBC driver via SQL Server Management Studio

通过SQL Server Management Studio使用ODBC驱动程序创建和配置链接服务器

In SQL Server Management Studio (SSMS) go to theObject Explorer, right click on theLinked Serversfolder and, from the context menu, choose theNew Linked Servercommand:

在SQL Server Management Studio（SSMS）中，转到“对象资源管理器”，右键单击“链接服务器”文件夹，然后从上下文菜单中选择“新建链接服务器”命令：

This will open theNew Linked Serverdialog:

这将打开“新建链接服务器”对话框：

Under theGeneraltab of theNew Linked Serverdialog in theLinked servertext box, enter a name of a new linked server (e.g. ODBC_SERVER). in theProvidercombo box, choose the Microsoft OLE DB Provider for ODBC Drivers item. TheProduct nameis an identifier and any appropriate value for this field (e.g. TestSarever) can be used. The data source must match a system data source defined in the ODBC Data Source Administrator dialog boxSystem DSNtab:

在“链接服务器”文本框中的“新建链接服务器”对话框的“常规”选项卡下，输入新链接服务器的名称（例如ODBC_SERVER）。在提供程序组合框中，选择“ Microsoft OLE DB提供程序用于ODBC驱动程序”项。产品名称是一个标识符，可以使用该字段的任何适当值（例如TestSarever）。数据源必须与在“ ODBC数据源管理器”对话框的“系统DSN”选项卡中定义的系统数据源匹配：

Under theSecuritytab, choose theBe made using this security contextradio button, then enter the username and password for a user account existing on the TestServer instance that was chosen as our data source:

在“安全性”选项卡下，选择“使用此安全性上下文进行访问”单选按钮，然后输入被选择作为我们的数据源的TestServer实例上存在的用户帐户的用户名和密码：

For more about theSecurityandOptionstab, see the How to create and configure a linked server in SQL Server Management Studio page.

有关“安全性和选项”选项卡的更多信息，请参见如何在SQL Server Management Studio中创建和配置链接服务器。

After, the security configuration is done, press theOKbutton on the New Linked Server dialog to create a new linked server. A newly created linked server will appear under the Linked Servers folder:

之后，完成安全性配置，然后在“新建链接服务器”对话框上按“确定”按钮以创建新的链接服务器。新创建的链接服务器将出现在“链接服务器”文件夹下：

To test if the connection with the ODBC data source is established successfully, right click on theODBC_SERVERlinked server and, from the context menu, choose theTest Connectioncommand:

要测试是否成功建立了与ODBC数据源的连接，请右键单击ODBC_SERVER链接的服务器，然后从上下文菜单中选择“测试连接”命令：

If a connection with the ODBC data source is established successfully, the following info message box will appear:

如果与ODBC数据源的连接成功建立，将出现以下信息消息框：

In case that the connection with a linked server is created successfully, but under theCatalogsfolder of the linked server, only thedefaultdatabase is shown:

如果成功创建了与链接服务器的连接，但是在链接服务器的Catalogs文件夹下，则仅显示默认数据库：

To resolve this, close SQL Server Management Studio and run it again, but this time as an administrator:

若要解决此问题，请关闭SQL Server Management Studio并再次运行它，但这一次是作为管理员：

Another way is to go to theSQL Server Configuration Manager:

另一种方法是转到SQL Server配置管理器：

From the SQL Server Configuration Manager dialog, select SQL Server for which a linked server has been created:

在“ SQL Server配置管理器”对话框中，选择已为其创建链接服务器SQL Server：

From the context menu, choose thePropertiesoption:

从上下文菜单中，选择“属性”选项：

Under theLog ontab of the SQL Server Properties dialog, choose theBuilt-in accountradio button and from the combo box, select theLocal Systemitem:

在“ SQL Server属性”对话框的“登录”选项卡下，选择“内置帐户”单选按钮，然后从组合框中选择“本地系统”项：

From the SQL Server Properties dialog, click theApplybutton and press theYesbutton on the Confirm Account Change warning message box:

在“ SQL Server属性”对话框中，单击“应用”按钮，然后在“确认帐户更改”警告消息框上按“是”按钮：

Now, open SQL Server Manage Studio asadministratorand under theCatalogsfolder, the databases should appear:

现在，以管理员身份打开SQL Server Manage Studio，并在Catalogs文件夹下显示数据库：

If the problem still persists, contact MSDN troubleshooting and support

如果问题仍然存在，请联系MSDN故障排除和支持。

使用T-SQL创建和配置ODBC链接服务器 (Creating and configuring a ODBC Linked Server using T-SQL)

To create a linked server using T-SQL, execute the following code:

要使用T-SQL创建链接服务器，请执行以下代码：

EXEC master.dbo.sp_addlinkedserver @server = N'ODBC_SERVER', @srvproduct=N'TestServer', @provider=N'MSDASQL', @datasrc=N'TestServer'GO/* For security reasons, the linked server remote logins password is changed with ######## */EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ODBC_SERVER',@useself=N'False',@locallogin=NULL,@rmtuser=N'Ben',@rmtpassword='########'GO

Note:

注意：

@rmtpasswordis the password of the remote user used to connect to a remote server, for security reasons password is changed with ‘########’.

@rmtpassword是用于连接到远程服务器的远程用户的密码，出于安全原因，密码已用'########'更改。

More about how to create and configure a SQL Server linked server using T-SQL can be found on the How to create, configure and drop a SQL Server linked server using Transact-SQL page.

有关如何使用T-SQL创建和配置SQL Server链接服务器的更多信息，请参见如何使用Transact-SQL创建，配置和删除SQL Server链接服务器页面。