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

问题描述

我负责将旧的Access 2007项目迁移到MS SQL Server 2008 Express.第一步是将所有数据从MS Access数据库移至SQL Server，同时将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 Server用户(仅用于访问该特定数据库)，并且通过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 Server用户密码存储在客户端.不过，我不确定是否有可能.我知道将密码保存在某些配置文件中，或将应用程序配置中混淆的存储内容考虑为降低安全性，但这对于给定的设置是可以接受的.
• 3)也许可以通过其他方式将SQL Server表链接到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. 创建一个通过查询"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 Server的ODBC连接的密码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！