如何避免在MSAccess 2003中提示输入用户ID和密码 [英] How to avoid prompting for user id and password in MSAccess 2003

问题描述

我是.Net/SQL Server程序员.我正在尝试使其他人创建的Access数据库起作用.似乎正在使用类似于

I am a .Net / SQL Server programmer. I am trying to make an Access database created by someone else to work. It looks like is uses a procedure similar to Save password for ODBC connection to SQL Server from MS Access 2007 but I am using Access 2003 so I suspect something else needs to be done. I don't really know what I am doing with Access but I am stuck with it.

我确实有ODBC连接，并且看起来链接表正在使用它.该数据库具有一个AutoExec，可从报表使用的链接表中填充一些本地表.它还像链接的文章一样调用具有无dsn的连接的qryConnect，这似乎可行.我已停止在启动时要求用户名和密码.我也可以打开大多数报告而没有问题，2不起作用.表单在启动时会加载带有基本上显示报告的按钮的表单.这就是奇怪的地方.

I do have an ODBC connection and it looks like the linked tables use it. The database has an AutoExec which populates some local tables from the linked tables that the reports use. It also calls a qryConnect with a dsn-less connection like the linked article and this seems to work. I have got it to stop asking for a user and password on start up. I can also open most reports without problems, 2 do not work. A form loads on Startup with buttons that basically show reports. This is where it gets odd.

使用Windows凭据的ODBC驱动程序报告失败.然后，将显示一个具有正确用户(具有只读权限的用户)的登录框，但选中了受信任的连接"框.我没有在DSN中选中信任"框.在qryConnect的无DSN的连接中没有Trusted_Connection=Yes.

The reports fail from the ODBC driver with the windows credentials. Then a logon box shows up with the correct user (a user with read only privileges) but the trusted connection box is checked. I do not have the trusted box checked in the DSN. Not do I have Trusted_Connection=Yes in the DSN-less connection in qryConnect.

这是形式的代码

stDocName = "rptNegativeLotQtys"
DoCmd.OpenReport stDocName, acPreview

我不知道如何进行大量修改，在再次尝试将所有内容复制到新的mdb并再次尝试之前，我希望得到一些指导.

I don't know how I buggered it up so much and before I try again copying everything to a new mdb and trying again I am hoping to get some guidance.

在旁注中，我可以打开机器上的所有功能.就Windows而言，我是管理员，但不是SQL Server.

On a side note I can open everything fine on my machine. I am an administrator as far as Windows is concerned but not SQL Server.

我仅为这些报告创建了一个新的DSN.然后，我删除了所有链接表并重新链接，然后使用新的DSN.我查看了MsysObjects中的链接表，并在其中列出了新的DSN.但是，Access仍然会提示您输入密码.看来它没有缓存密码.

I created a new DSN just for these reports. Then I deleted all linked tables and re-linked then using the new DSN. I looked at the linked tables in MsysObjects and the new DSN is listed there. Yet Access still prompts for the password. It appears that it is not caching the password.

推荐答案

实际上，您不需要在所使用的连接字符串中添加或存储UID/Pass.但是，您必须确保所有连接字符串都相同.如果它们不同，则缓存的UID/密码将不起作用.

Actually, you do NOT need to add nor store the UID/Pass in the connection strings used. However, what you MUST ensure is that all connection strings are the SAME. If they are different, then the cached UID/password will not work.

此外，请确保在使用Windows Auth连接后不连接或打开表-原因是当您添加表链接(尤其是通过代码)时，将发生使用Windows auth的缓存设置，并且因此，当链接建议您执行其他操作时，将出现提示.换句话说，如果您已经通过Windows Auth打开了任何表链接，则链接并尝试使用SQL登录将不起作用(因此请退出数据库，不要打开任何可以/可以使用Windows Auth的表).

Also, make sure you do NOT connect nor open a table AFTER having connected using Windows Auth – the reason being then when you add the table links (especially via code), then the cached setup of using windows auth will occur, and thus prompts will occur when the links suggest to do otherwise. In other words linking and attempted to use SQL logons will NOT work if you already opened any table link via windows auth (so exit the database and do NOT open any table that could/can use windows auth).

因此，我将删除链接，然后重新创建它们-但再次确保您从未以Windows身份验证用户身份连接到SQL Server.

So I would delete the links, and re-create them – but again, ensure that you never connected as a windows auth user to SQL server.

因此，访问所具有的缓存"是一个很好的朋友/功能，它消除了在连接字符串中包含UID/pass的需要-但是，如果您随时连接到数据库中的SAME，那么SAME功能将给您带来极大的伤害.通过Windows身份验证提问.

So the "cache" that access has is a GREAT friend/feature to eliminate the need to include UID/pass in the connection string - but the SAME feature will bite you VERY bad if you at any time connecte to the database in question via windows auth.

此处介绍了如何缓存"用户登录名和ID:

How to "cache" the user logon and ID is explained here:

电源提示:提高数据库连接的安全性

Power Tip: Improve the security of database connections

http://blogs.office.com/b/microsoft-access/archive/2011/04/08/power-tip-improve-the-security-of-database-connections.aspx

因此，您可以将" UID/LOGON包含在链接表中，但是上面显示了也可以使用一次登录.因此，在设置表链接时要非常小心-由于用户的上述缓存"以及他们如何登录到数据库-如果您已经以Windows身份验证连接，则ATTEMPTS创建基于SQL链接的表链接的代码实际上将使用Windows Auth结束这样(如果要使用SQL登录重新链接，请退出数据库).

So you "can" include the UID/LOGON in the linked tables, but above shows that a ONE time logon can also be used. So be VERY careful when setting up table links – due to the above "cache" of the user and HOW they logged into the database – code that ATTEMPTS to create table links based SQL logons will actually wind up using windows auth if you already connected as such (so exit the database if you going to re-link using SQL logons).

请记住，链接表后，Access不会使用DSN.来自DSN的信息是一次复制到连接字符串的信息.您可以通过进入我的文档并删除您使用的DSN来进行验证.假定链接表正常工作，那么即使删除DSN，您也会发现它们甚至继续工作.实际上，这意味着链接表的DSN较少，但创建链接表时除外.这样一来，您就可以轻松地将应用程序复制到其他计算机上，而不必复制DSN.

Keep in mind, Access does NOT use the DSN AFTER you linked the table. The information from the DSN is a ONE TIME copy to the connection string. You can verify this by going into my documents and deleting the DSN you used. Assuming the linked table was working correctly, you find that they CONTINUE to work EVEN if you delete the DSN. In effect this means that linked tables are DSN less except for when you create the linked table. This allows you to easy copy the application to different computers without having to copy the DSN.

要确保所有链接表都使用相同的连接字符串，只需删除所有表，然后重新链接即可.

As to ensure that all the linked tables use the same connection string, it is a simple matter to delete them all, and re-link.

您还可以按ctrl-g来打开调试窗口，并以这种方式查看连接字符串:

You can also hit ctrl-g to bring up the debug window, and look at the connection string this way:

? currentdb.TableDefs("linked table name").Connect

Windows身份验证连接字符串的调试窗口中的结果将如下所示:

the result in the debug window for a windows auth connection string will look like this:

ODBC;Description=test DSN;DRIVER=SQL Server;
SERVER=albertkallal-pc\SQLEXPRESS;
Trusted_Connection=Yes;
APP=Microsoft Office 2010;DATABASE=AxisMIS

请注意，在上面如何看到受信任的连接"(即Windows身份验证).

Note how in above we see "trusted connection" (that means windows auth).

如果我使用SQL登录链接表(并使用REMEMBER来检查保存密码)，那么您会看到以下信息:

If I linked the table using SQL logon (and REMEMBER to check save password), then you see this:

ODBC;Description=TEST3;DRIVER=SQL Server;
SERVER=ALBERTKALLAL-PC\SQLEXPRESS;
UID=MySQLogon;PWD=MyPassword;
APP=Microsoft Office 2010;DATABASE=AxisMIS

请记住在表链接过程中检查"保存密码.

Just remember during the table link process to "check" the save password.

例如:

这篇关于如何避免在MSAccess 2003中提示输入用户ID和密码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！