Best way to do Access/SQL Server Security
I have several Access front ends that work with SQL Server back ends. The front ends are used only by internal company people on our intranet or connected to it via VPN.
What is the best method of setting up security?
Currently, I set up user accounts in Access that go into System.mdw. I then rename that to something else and distribute it to the various users along with the .mde files and set up the .mde packages to point to the correct .mdw file. This way, I do not inturrupt their ability to use other Access files without having to enter a password.
Inside the .mde files is code to decrypt am embedded, encrypted password, and build a connection string with a common SQL Server UserID.
This has been fine for our small group of users, but now the community is growing and I need more control over the ability to restrict access.
I am in process of converting to individual UserIDs in SQL Server, restricting the users to only the databasases they can connect to. I suppose I could set each user up in Access and give them a password, and use that same UserID and password in SQL Server.
But, before I get too far along, I would like some input on the best way of doing this.
What is the best method of setting up security?
Currently, I set up user accounts in Access that go into System.mdw. I then rename that to something else and distribute it to the various users along with the .mde files and set up the .mde packages to point to the correct .mdw file. This way, I do not inturrupt their ability to use other Access files without having to enter a password.
Inside the .mde files is code to decrypt am embedded, encrypted password, and build a connection string with a common SQL Server UserID.
This has been fine for our small group of users, but now the community is growing and I need more control over the ability to restrict access.
I am in process of converting to individual UserIDs in SQL Server, restricting the users to only the databasases they can connect to. I suppose I could set each user up in Access and give them a password, and use that same UserID and password in SQL Server.
But, before I get too far along, I would like some input on the best way of doing this.
ASKER
So, Let me parrot this back...
Create Windows Groups on the server.
Set up Windows Authentication for each Windows Group in SQL Server.
Have Access use Windows Authentication and essentially have a single userID and password for each Group.
I would still have to have individual UserIDs and passwords to allow people to open the Front End tools in Access.
Is this right?
Create Windows Groups on the server.
Set up Windows Authentication for each Windows Group in SQL Server.
Have Access use Windows Authentication and essentially have a single userID and password for each Group.
I would still have to have individual UserIDs and passwords to allow people to open the Front End tools in Access.
Is this right?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
1. Use Windows authentication between the access front end and SQL backend.
2. Create Windows groups for each level of access that you desire. For example, a simple scheme might be 3 levels: Readers, Writers, and Admins. Of course, you can get more granular if you like and/or have discreet groups by database or application. My suggestion is to keep it simple at first and add sophistication later.
3. Create SQL Server logins for each group created in 2. Grant permissions to these groups as desired. A SQL Server login will get the accumulated permissions of all groups that the windows account belongs to. Don't forget, you can also take advantage of the built-in windows groups. For example, you could give SELECT permission to "Authenticated Users" and be done with it. My suggestion is to grant permissions at the highest level possible. By that I mean, there is less administration to grant permissions at the database level than at the schema level, less at the schema level than at the table level, less at the table level than at the column level.
4. In addition to above, you can grant individual permissions for special exceptions. However, then you need to create an individual logins, which gets labor intensive. Unless the required access is temporary in nature, I think it's still better to use the group approach, even if the group only has one member.
5. To reiterate:
Use windows authentication
Use windows groups
Assign windows accounts to windows groups
Use group logins on SQL Server that correspond to the windows groups
Grant permissions to group logins