保存从 MS Access 2007 到 MS SQL 服务器的 ODBC 连接密码 [英] Save password for ODBC connection to MS SQL server from MS Access 2007

问题描述

我负责将旧的 Access 2007 项目迁移到 MS SQL Server 2008 Express.第一阶段是将所有数据从 MS Access 数据库移动到 SQL 服务器，同时将 Access 表单和报表保留在客户端.

I am in charge for migrating old Access 2007 project to MS SQL server 2008 Express. The first stage is to move all the data from MS Access database to SQL server while keeping the Access forms and reports at the client.

因此，现在移动了数据，创建了一个 SQL 服务器用户(仅用于访问该特定数据库)，并且表通过 ODBC 连接链接到 Access 数据库.但是，有一个问题应该以某种方式解决:当打开 Access 数据库时，Access 会定期询问用户密码.

So, the data are now moved, an SQL server user (for accessing only that particular database) created, and the tables are linked the Access database via ODBC connection. However, there's one nuisance that should be somehow solved: the Access regularly asks for the user password, when opening the Access database.

服务器PC和客户端PC上的用户都登录到他们的本地机器上，即他们的用户没有在独立的域服务器上进行验证.

The users on server PC and client PC both log onto their local machines, ie their users are not verified on independent domain server.

我看到有几种方法可以解决这个问题:

I see there are several ways how to solve this:

• 1) 配置集成安全模型，以便用户可以登录，并通过其 Windows 登录自动授权(即，使用可信连接").鉴于服务器 PC 无法识别来自客户端 PC 的用户，我不确定如何做到这一点.如果我现在尝试这样做，我会收到错误消息，指出用户正在从不受信任的域进行连接.
• 2) 在客户端存储 SQL 服务器用户密码.不过，我不确定这是可能的.我知道将密码保存在某些配置文件中，或在应用程序配置中混淆存储应该被视为降低安全性，但这对于给定的设置是可以接受的.
• 3) 或许可以通过其他方式将 SQL 服务器表链接到 Access 中?

推荐答案

显然最好的解决方案是使用 Windows 安全性.

The best solution is obviously to use Windows security.

如果这不合适，这里有一个可能的替代技巧，利用 Access 记住所有打开的连接直到程序关闭的事实:

If that is not suitable, here is a possible alternative trick, exploiting the fact that Access remembers all opened connections until the program is closed:

1. 复制其中一张表的连接字符串
2. 创建一个passthru查询ptqConnect"并在其中输入任何快速SQL语句，例如SELECT 1
3. 将 的连接字符串粘贴到 PTQ Connect 属性中，并确保在其中添加 PWD=something;.
4. 在您的应用程序的启动过程中，请确保您调用了该 PTQ.像 DCount("*", "ptqConnect") 这样的东西就可以了.

1. copy the connect string of one of your tables
2. create a passthru queries "ptqConnect" and enter any fast SQL statement in it, like SELECT 1
3. paste the connect string of into the PTQ Connect property, and make sure you add the PWD=something; in it.
4. in the startup procedure of your app make sure you call that PTQ. Something like DCount("*", "ptqConnect") will do.

就是这样.由于 Access 会记住打开的连接，直到您关闭它，即使您关闭数据库，您的其他表现在也将毫不费力地打开，即使链接表中没有存储密码连接字符串.
如果您不想公开包含 PWD 的连接字符串，您也可以从 VBA 启动连接并通过提供 MDE 或仅密码保护代码来隐藏代码.

That's it. Since Access remembers opened connections until you close it, even if you close the db, your other tables will now open without any fuss, even if no password is stored in the linked tables Connect string.
If you don't want to expose the connection string that includes the PWD, you could as well initiate a connection from VBA and hide the code by delivering a MDE or just password protecting the code.

您可以找到对此行为的解释 此处.

You can find an explanation of this behaviour here.

这篇关于保存从 MS Access 2007 到 MS SQL 服务器的 ODBC 连接密码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！