CODEWITHSUNDEEP
sql-serverms-accessauthenticationvbaldap
jasongetsdown
jasongetsdown

Reputation: 1305

LDAP Authentication using VBA in MS Access

I am creating an Access project linked to a SQL Server. Instead of using Windows authentication for the SQL Server I want to use an application specific username/password and authenticate users myself using a login form. The reason is that other applications may connect to this database from a context where Windows authentication is not an option (FileMaker Server for instance).

I have found myriad discussions that cover this issue from one angle or another, but none that have actually enabled me to implement a solution. I do not have a lot of VBA or LDAP experience but I know this must be possible.

So, what I want to do is:

  1. Ask the user for a username and password when they open the database.
  2. Use that username and password to authenticate them against our LDAP server.
  3. Proceed if it passes, fail if it does not.

Can someone sketch out how this works or point me to a resource that describes this?

Upvotes: 0

Views: 3070

Answers (1)

Jim Parker
Jim Parker

Reputation: 365

One way to accomplish this is to create a VBA procedure which loops through all of your linked tables and alters the connection string to embed the username and password from a login form.

You can get the syntax for the existing ODBC linked tables by going to the debug window and typing this:

? CurrentDb().TableDefs("My Table Name").Connect

Which should give you something similar to:

ODBC;DRIVER=SQL Server;SERVER=MYSQLSERVER001;UID=JPARKER;PWD=Pizza123;APP=2007 Microsoft Office system;DATABASE=MyDatabaseName

So your login form would capture the user name and password from the user, then store those as variables and use to build a new connection string. Then iterate through the tabledefs collection updating the connect property. Happens pretty quickly.

Upvotes: 1

Related Questions