Use Windows Authentication for several Access FE's to connect to SQL Server backend

[BlueSpruce]

Is it possible for Access frontends to dynamcally connect to a SQL Server backend using Windows Authentication without users having to enter any login credentials?

e.g. A dozen Access application users, each with unique Windows login ID's, linking to the same SQL Server backend with a dsnless connection, and each users Windows ID is stored in records for auditing purposes. Can this be setup without having to create individual SQL Server accounts for each user?

 

[Isaac]

Is it possible for Access frontends to dynamcally connect to a SQL Server backend using Windows Authentication without users having to enter any login credentials?

e.g. A dozen Access application users, each with unique Windows login ID's, linking to the same SQL Server backend with a dsnless connection, and each users Windows ID is stored in records for auditing purposes. Can this be setup without having to create individual SQL Server accounts for each user?

What about an active directory group that they get placed in? I think the DBA's can handle that, and yes, then the connection would be seamless in Access. If they have windows auth access to sql server

 

[theDBguy]

Is the SQL Server set up as Hybrid or just SQL Account login?

 

[The_Doc_Man]

Both the Access FE and SQL Server have the ability to identify your domain login. However, I can only tell you about the FE side because I had a native Access BE file in the case relevant to the question.

In this particular case, I had an opening form defined that was my dispatcher/switchboard. In that form's OnOpen event, which DOES allow a CANCEL, I used an API call to determine the user's domain ID to determine whether that person would have access to the app. If not, the OnOpen can signal the CANCEL and then issue an Application.Quit to block further action. There is more to it than that, and you can look up this topic: "hardening a database" or "securing a database" to find out various approaches. This was a very popular topic a few years ago so you should be able to find several articles.

 

[Pat Hartman]

Can this be setup without having to create individual SQL Server accounts for each user?

I'm not sure you want to do that. It is far better for the DBA to know who is sending what to the server rather than having everyone use the same account.

If you have the FE set to have users log in, you can have the userid/password saved with the linked tables. That way no user needs specific credentials for the server. Everyone uses the saved credentials. This is generally poor practice and not at all secure and so is not recommended.

 

[BlueSpruce]

What about an active directory group that they get placed in? I think the DBA's can handle that, and yes, then the connection would be seamless in Access. If they have windows auth access to sql server

Yes, that's how I envisioned it working, but will SQL Server 2022 Enterprise Edition transparently know each different user that's connected to it via Windows Authentication, and can each user have different dba permissions, or is it one service account, or one role, for everyone in the same AD group?

Is the SQL Server set up as Hybrid or just SQL Account login?

No hybrid, no SQL login, just Windows Authentication to transparently connect with SQL Server. The data has PHI which is regulated by HIPAA, so there has to be auditing down to the user level. They just don't want to have to add and remove individual SQL Server login credentials for each user every time a new Access user is added or deleted, rather have SQL Server transparently get the Windows ID of each user that's connected to the db via a generic Access frontend that's copied from a master FE and launched when users click a desktop shortcut on each workstation.

 

[Isaac]

Yes, that's how I envisioned it working, but will SQL Server 2022 Enterprise Edition transparently know each different user that's connected to it via Windows Authentication, and can each user have different dba permissions, or is it one service account, or one role, for everyone in the same AD group?

No hybrid, no SQL login, just transparent Windows Authentication to connect with SQL Server. The data has PHI which is regulated by HIPAA, so there has to be auditing down to the user level. They just don't want to have to add and remove individual SQL Server login credentials for each user every time a new Access user is added or deleted, rather have SQL Server transparently get the Windows ID of each user that's connected to the db via a generic Access frontend that's copied from a master FE and launched when users click a desktop shortcut on each workstation.

The normal way to handle this is to have an active directory group and have people placed into the group. That's how you get lots of people having access in a simple way where everybody's accountable individually

I think it would be the permissions go with the group. But I'm not a DBA so definitely ask yours

 

[The_Doc_Man]

@Isaac, you are correct.

I think it would be the permissions go with the group.

The Navy's setup was that we read the domain ID for the user and granted them the GROUP ID for the SecActDBGroup. The permissions followed the group ID. But we added the domain ID as the PK of the user table and had the user's full name and preferred nickname and role. The domain ID was also used in a table of user privileges and in a junction table for projects to which the user was allowed to do things. Role in this case was NOT project-sensitive.

 

[sonic8]

will SQL Server 2022 Enterprise Edition transparently know each different user that's connected to it via Windows Authentication,

Yes.

and can each user have different dba permissions, or is it one service account, or one role, for everyone in the same AD group?

No, of course not. How should users have different permissions if their permissions are defined by a single AD Group.
You need to create one AD Group for every set of permissions.

 

[BlueSpruce]

Okay, so then I don't need to specify a user ID and password in my dsnless connection string code?

strConnectionString = "ODBC;DRIVER={sql server};" & _
        "DATABASE=" & DatabaseName & ";" & _
        "SERVER=" & ServerName & ";" & _
        "Trusted_Connection=YES;"
    End If
  End If

  intToChange = 0

  Set dbCurrent = DBEngine.Workspaces(0).Databases(0)

 

[AHeyne]

Correct. "Trusted_Connection=yes" is ok.