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.