E McNeill (6/27/2016)
My company has a Quality control program that utilizes MS Access to store quality control data. The computer that runs the quality control is a component computer of a much larger system and has no user interface per say. When the whole system is started the computer with the quality program is also started. The database resides one of our company’s other servers. The data is strictly used to analyze the QC results of the current batch for and we have no interest in saving any data once we have verified the results and run any necessary reports. In fact when the system is shut down, part of the shutdown process is to delete all of the data, compact and repair the database and get ready for the next batch . There is absolutely no need for any security of the data because the data would mean absolutely nothing to anyone else.As everyone here knows, MS Access is definetly not the best database for this process! I’ve convinced my boss of this and he gave me the go ahead to reprogram using Sql Server. I have created all of the stored procedures, views and table to accomplish my mission and have everything working with a brand new application – All I have to do is to install it.
Now my question is security. When I created all my sql Server objects I also created an SQL Logon and gave that logon permission to execute all procs and whatever other permissions needed to access the database but I’m wondering if that was the best option.
After testing my program we thought this might be something we could sell to other companies. If so, what is the best security scheme to use especially if we were to try to come up with some we could create an installation program.
We have absolutely no need to secure any data. From a security perspective MS Access was great with a single simple connection string. No user names or passwords that could change, no permissions to server objects to worry about – just connect and go. Ideally it would be great if current windows user could automatically have all the permissions it needed but I know this is not possible with SQL Server.
I would just like some professional input on how to best handle security. Application Role, Assign all permissions to Public and/or Guest… questions like that……
I’m using SQL Server 2008 R2
Since most connection string information is NOT protected/encrypted, the option you chose isn't the best in a security sense, for protecting access to this data.
As you said this data is meaningless to anyone outside the company and only at the time of data retrieval. So this may not be a factor but it is out there for all the world to see and so for malicious intent I would change your security paradigm as follows:
1. Create an Active Directory group named something like "QC_Report_Group" (without quotes)
2. Place all user accounts who would run said reports into this group.
3. Create a login for Active Directory group on SQL Server.
4. Give proper permissions to this group in SQL Server.
5. Change all connection strings used to generate these reports to utilize a trusted connection.
6. Remove or deactivate the SQL Server user account you were using.