如何获取ms-access作为其他用户连接到ms-sql？

How do I get ms-access to connect (through ODBC) to an ms-sql database as a different user than their Active Directory ID?

我如何获得ms-access连接(通过ODBC)作为与其Active Directory ID不同的用户连接到ms-sql数据库?

I don't want to specify an account in the ODBC connection, I want to do it on the ms-access side to hide it from my users. Doing it in the ODBC connection would put me right back in to the original situation I'm trying to avoid.

我不想在ODBC连接中指定一个帐户,我想在ms-access端进行该操作以将其隐藏在我的用户之外。在ODBC连接中执行此操作会让我回到原来我想避免的情况。

Yes, this relates to a previous question: http://www.*.com/questions/50164/

是的,这与先前的问题有关:http://www.*.com/questions/50164/

6 个解决方案

#1

I think you can get this to work the way you want it to if you use an "ODBC DSN-LESS connection"

如果您使用“ODBC DSN-LESS连接”,我认为您可以按照您希望的方式工作

If you need to, keep your ODBC DSN's on your users' machines using windows authentication. Give your users read-only access to your database. (If they create a new mdb file and link the tables they'll only be able to read the data.)

如果需要,请使用Windows身份验证将ODBC DSN保留在用户的计算机上。为您的用户提供对数据库的只读访问权限。 (如果他们创建一个新的mdb文件并链接表,他们将只能读取数据。)

Create a SQL Login which has read/write permission to your database.

创建一个对数据库具有读/写权限的SQL登录。

Write a VBA routine which loops over your linked tables and resets the connection to use you SQL Login but be sure to use the "DSN-Less" syntax.

编写一个VBA例程,它循环遍历链接表并重置连接以使用SQL登录,但一定要使用“DSN-Less”语法。

"ODBC;Driver={SQL Native Client};" &
       "Server=MyServerName;" & _
       "Database=myDatabaseName;" & _
       "Uid=myUsername;" & _
       "Pwd=myPassword"

Call this routine as part of your startup code.

将此例程作为启动代码的一部分调用。

A couple of notes about this approach:

关于这种方法的几点注意事项:

Access seems to have an issue with the connection info once you change from Read/Write to Read Only and try going back to Read/Write without closing and re-opening the database (mde/mdb) file. If you can change this once at startup to Read/Write and not change it during the session this solution should work.

一旦从“读/写”更改为“只读”并尝试返回“读/写”而不关闭并重新打开数据库(mde / mdb)文件,Access似乎就会出现连接信息问题。如果您可以在启动时将其更改为读/写,并且在会话期间不更改此解决方案应该可以正常工作。
By using a DSN - Less connection you are able to hide the credentials from the user in code (assuming you're giving them an mde file you should be ok). Normally hard-coding connection strings isn't a good idea, but since you're dealing with an in-house app you should be ok with this approach.

通过使用DSN - Less连接,您可以在代码中隐藏用户的凭据(假设您给他们一个mde文件,您应该没问题)。通常硬编码连接字符串不是一个好主意,但由于你正在处理一个内部应用程序,你应该可以使用这种方法。

#2

I think you'd have to launch the MS Access process under the account you want to use to connect. There are various tools that let you do this, such as CPAU. This tool will let you encrypt the password as well.

我认为您必须在要用于连接的帐户下启动MS Access流程。有各种工具可以让你这样做,比如CPAU。此工具也允许您加密密码。

#3

We admit here that you are using an ODBC connexion to your database with Integrated Security on, so that you do not have/do not want to write a username/pasword value in the connexion string (which is according to me the right choice).

我们在此承认您正在使用ODBC连接到您的数据库并启用了集成安全性,因此您没有/不想在连接字符串中写入用户名/密码值(根据我的正确选择)。

In this case, there is fortunately no way to "simulate" another user when connecting to the data. Admit with me that being able to make such a thing would be a huge break in integrated security!

在这种情况下,幸运的是,在连接数据时无法“模拟”另一个用户。承认我能够做出这样的事情将是集成安全性的巨大突破!

I understood from your previous post that you wanted users to be able to update the data or not depending on the client interface they use. According to me, the idea would be to create for each table a linked 'not updatable' view. Let's say that for each table called Table_Blablabla you create a view (=query in Access) called View_Table_Blablabla ...).

我从您之前的帖子中了解到,您希望用户能够更新数据,具体取决于他们使用的客户端界面。根据我的想法,我们的想法是为每个表创建一个链接的“不可更新”视图。假设对于每个名为Table_Blablabla的表,您创建了一个名为View_Table_Blablabla的视图(= Access中的查询...)。

When using Access, you can then decide at runtime wether you want to open the updatable table or the read-only view. This can be done for example at runtime, in the form_Open event, by setting the form recordsource either to the table or the view.

使用Access时,您可以在运行时决定是否要打开可更新表或只读视图。这可以在运行时,在form_Open事件中,通过将表单recordsource设置为表或视图来完成。

#4

@Philippe
I assume that you are using the word admit as being roughly equivalent to understand or perhaps agree; as opposed to the opposite of deny.

@Philippe我假设你使用admit这个词大致等同于理解或者同意;与否认相反。

I understand the implications of having all the users login to the database using one ID and password (and having them stored in the application). That to me is a smaller risk than the problem I'm facing right now.
@off

我理解让所有用户使用一个ID和密码登录数据库(并将它们存储在应用程序中)的含义。对我而言,这比我现在遇到的问题风险小。 @off

Some more background to the problem: I have ODBC connections set up on each of the users workstations using Windwos NT authentication. Most of the time the users connect using an MDE setup to use that ODBC connection - in this case they ALWAYS have the ability to add/update/delete data.

问题的更多背景知识:我在使用Windwos NT身份验证的每个用户工作站上设置了ODBC连接。大多数情况下,用户使用MDE设置连接以使用该ODBC连接 - 在这种情况下,他们总是能够添加/更新/删除数据。

The problem comes that some of the users are educated enough about MS-Access to create a new mdb and link it to the MS-SQL server. They can then edit the data right within the tables rather than going through the application which does a certain amount of validation and hand holding. And they like doing this, but sometimes the mess it up and cause me problems.

问题在于,一些用户已经足够了解MS-Access以创建新的mdb并将其链接到MS-SQL服务器。然后,他们可以在表格中编辑数据,而不是通过执行一定量验证和手持的应用程序。而且他们喜欢这样做,但有时会把它弄得乱七八糟,给我带来麻烦。

#5

What I was hoping to do (which I just experimented with) was to refresh the links to the database something like this for each table (Note: I've switched the ODCB connection to SQL Server authentication for this experiment, and added the accounts to the SQL server as well: readonly - which can't to any updates, and readwrite - which has full privileges on the table).

我希望做的(我刚刚尝试过)是为每个表刷新数据库的链接(注意:我已将ODCB连接切换为此实验的SQL Server身份验证,并将帐户添加到SQL服务器:readonly - 不能进行任何更新,readwrite - 具有表的完全权限)。

myTable.Connect = _
                "ODBC;" & _
                "DATABASE=" & "MyTestDB" & ";" & _
                "UID=readonly;" & _
                "PWD=readonly_password;" & _
                "DSN=" & "MyTestDB" & ";"
myTable.RefreshLink

this stops them from editing, but I can't get a later readwrite to work

这会阻止他们编辑,但我无法让以后的readwrite工作

myTable.Connect = _
                "ODBC;" & _
                "DATABASE=" & "MyTestDB" & ";" & _
                "UID=readwrite;" & _
                "PWD=readwrite_password;" & _
                "DSN=" & "MyTestDB" & ";"
myTable.RefreshLink

It seems that whichever permission I connect with first, sticks permenantly. If I started readwrite and then go to readonly, the table remains with the readwrite privileges

看来,无论我先与谁联系,都要坚持不懈。如果我开始readwrite然后转到readonly,则表保留readwrite权限

#6

Why not use integrated/windows security. You can grant an active directory group the rights you want the users and then add the users accounts to that group. I believe you can also use sql server's roles feature in addition to this to limit functionality based on the client application being used.

为什么不使用集成/ Windows安全性。您可以向活动目录组授予您希望用户使用的权限,然后将用户帐户添加到该组。我相信你还可以使用sql server的角色功能来限制基于所使用的客户端应用程序的功能。

#1