1) Set up an AD group and assign to it the users who are to access this database. This enables the use of Windows Authentication as a security method rather than SQL Server;
2) Create an AD login for this group in SQL Server, mapping the login to your user database and maybe tempdb (for the benefit of your stored procedures).;
3) Grant appropriate permissions to the objects in the database eg tables will need select, insert, update and delete; stored procedures will require exec. You can do this manually if you only have a few objects, or write some SQl to do it for you - maybe keep it as a stored proc.
==> actually I've done all that (1,2 and 3)
You can either continue to use the existing ADO methods in Access or convert to using ODBC and linking the tables. If you use ODBC you will need to set up a connection (using the ODBC administrator) on all PCs designated for access to the app.
My serious (but may be naive) concern about linked tables: in a "simple" Access app form controls are linked to tables (or through queries) by DYNASET, that would change table field value every time you change it in the control. Our current design (SQL Server / ADO) allows to update the record ONLY by firing event on the Access front end form (e.g. clicking button "Save Changes"). Am I wrong?