We have an EXE and a DLL that reads/writes to a SQL Server database. The EXE and DLL runs on the user's computer.
We would like to connect to SQL Server database using Integrated Security, but not with the user's windows id, instead with a windows service account. This way, we don't need to give the users write access to the database.
Since the user runs the EXE and DLL on the user's computer, is it possible to set SQL Server connection string to integrated security, but uses a windows service account (not the user's account) ?
Thank you.
Technically you could, though it is not a good idea. What you have not described is your reasoning for this requirement.
This feels like an XY issue, by running a user application in the context of another security account, you are creating a security window that other processes might be able to hijack. You lose the ability to audit and control per-user access to the resource that you want to secure, instead of making things more secure, you have in most cases actually achieved the reverse.
Bold claim right... If the user is running the application, then they must have a saved or stored credential for the elevated security account, or that user must have knowledge of the elevated credential.
Running background services in the context of a different account is perfectly acceptable, but when you script impersonation into how your user application executes this creates vulnerabilities, either in the way you script it and how the credential is stored, or once the user is in your application, any file access dialog, like an export, file open, file save dialog gives your user access to the filesystem and the network in the context of the account you have impersonated. Depending on your application this could be a good thing, or a bad thing, but it is often overlooked in UX.
What is further overlooked is that everything that your application does, every audit trace, every log, everything will show up as the service account, not the user account. When there is a security breach and you need to trace it, you cannot separate the individual user accounts, everyone will look like this one service account, in this case, it extends to SQL Server audits and security profiling, the whole environment will look like one user using the database and that one user will probably have the highest privileges inside SQL Server, meaning it only takes one console to be compromised and that one console probably has rights to run DDL statements and could seriously cause some trouble.
I can't over-state this, everyone accessing the database over a single credential, is a very bad security practice and is laughing in the face of the very robust security mechanisms built into SQL Server.
Instead, use the domain security to your advantage. Do not be concerned about having write access to the database files, if you want your user to insert a record, it makes sense that they need windows security rights to modify the file. I would be more concerned that I knew who was accessing my files and when, that can only happen if they have individual accounts.
If it is the management overhead that you are concerned about, then create a security group and put the necessary users into that security group. Then you manage the file-level access be assigning the group jut to the files that are needed. Inside SQL server you can further restrict the access level to SQL operations for that user
Remember that the alternative is probably to make the service account a DB Admin role or owner for the database, so any user of your app could drop the database.
If you want to use Integrated Security, then the users must already be trusted to access domain resources, the database is just another domain resource, so the security management should be treated the same. The biggest reason that we want unique accounts for users is so that we can easily restrict or revoke access to individuals without having to affect ALL users on the domain.
The best way to disassociate your users from direct access to the database is to setup a tiered DAL layer where the DAL is deployed as a standalone service that the apps communicate with, this service can run in the context of a specific user account for database operations. This usually involves substantial changes to your application design but it still introduces all the issues listed above with SQL Security implementation, yes it might solve a network security issue, but there is no easy way to apply individual user or role based security at the database level if everyone is using the same account, meaning you have the responsibility of managing this at the application level.