1

I maintain a SQL Server database that's used as a workflow tool for a team of a few dozen users. Those users interact with the database via an MS Access front-end and Windows Authentication. Part of the process requires that anyone in the firm (>10,000 people) needs to be able to submit jobs and feedback via Excel forms that are available to all. To accommodate this, we use a SQL Authenticated service account that's permissioned to write to the required target tables only. It uses a connection script that looks something like this:

conn.Open "Provider=SQLOLEDB; SERVER=[ServerAddress]; DATABASE=[DatbaseName]", [ServiceAccountName], [ServiceAccountPassword]

This has been working fine for many years. Now however, IT wants to prohibit the use of service accounts, and rely on Windows authentication exclusively. They have asked me to decommission the service account, and I'm trying to figure out how I can accomplish this.

My first thought was that I should be able to use the guest user account in lieu of a service account. I made sure the guest account was active in the database, gave it Insert permissions to the target tables, and changed the connection script to this:

conn.Open "Provider=SQLOLEDB; SERVER=[ServerAddress]; DATABASE=[DatbaseName]; Trusted_Connection=yes"

This works when I use it, but fails for any users who don't have Windows Authenticated logins in the database. The error they receive reads "Login failed for [user]".

So, my basic question would be, is what I'm trying to accomplish feasible? Can the guest account be used in lieu of a SQL Authenticated service account? If so, am I missing something obvious here? If not, is there a preferable method for allowing submissions by users who don't have login privileges to the database?

Thanks for reading. Any suggestions would be greatly appreciated.

EDIT: To clarify the goal here, I need to be able to allow any member of the firm to submit a job record. I cannot assume any knowledge of these users, including what Active Directories they may belong to. Basically, I need an "other" category for any users who are not recognized by the database. I was hoping the guest account might be a means of accomplishing this.

Improve this question
4
  • 2
    This works when I use it, but fails for any users who don't have Windows Authenticated logins in the database. The error they receive reads "Login failed for [user]" .. that's by design, WinAuth is working exactly as it should! Now admins can maintain user rights trough AD security group memberships - users without access becomes theirs (admins') to deal with now.
    – Mathieu Guindon
    Dec 22, 2021 at 2:29
  • 2
    This really belongs on Database Administrators, but relevant: How to add Active Directory user group as login in SQL Server
    – Charlieface
    Dec 22, 2021 at 2:47
  • 3
    You can have a Domain Group/User Group as a "login" in SQL Server. In other words create a Windows domain group, assign all users to it. Grant SQL Server login for this group and grant permissions. All users will then be able to login to SQL Server.
    – Alex
    Dec 22, 2021 at 3:11
  • 1
    Re "Basically, I need an "other" category for any users who are not recognized by the database.": this is exactly what IT is trying to prevent by prohibiting "service account". You should not have unapproved users accessing you DB and you should not have multiple users sharing the same credential. In any case, this looks like what you want: sites.google.com/site/jayantdass/…
    – Alex
    Dec 22, 2021 at 7:21

1 Answer 1

Reset to default
2

To clarify the goal here, I need to be able to allow any member of the firm to submit a job record. I cannot assume any knowledge of these users, including what Active Directories they may belong to.

You'll need to solve this with by adding logins for AD Security Groups. If you have users in multiple AD domains, you may need one group per domain. But you should find appropriate "global" groups in your AD, perhaps the built-in Domain Users security group.

Improve this answer
3
  • Thanks for the suggestion. I'm definitely going to need to consult with IT to get an overview of what our AD structure looks like. We've got staff spread across multiple divisions in dozens of countries, so I'm hesitant to make assumptions here.
    – Allen R. Brady
    Dec 23, 2021 at 3:06
  • 1
    Do that. Even Microsoft has only a handful of domains. So it’s probably not too bad.
    – David Browne - Microsoft
    Dec 23, 2021 at 3:13
  • @AllenR.Brady In the worst case, as David recommended, you could even use the Domain Users Active Directory group, which is a default all inclusive group of all users in the domain.
    – J.D.
    Dec 23, 2021 at 4:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.