[AccessD] Connecting to SQL from Access w/a single login?

Hi Christopher:

The SQL server has it's own way of dealing with access rights. They are
through an internal 'sa' type account or it is through 'NT authentication'.

Traditionally, I only use the 'sa' accounts as a backdoor for an
administrator. Your best option would be to go with 'NT authentication'.

The setup is fairly easy, if network has a domain. Go to the main server,
login as an administrator, 'right-click' MyComputer (assuming a Windows2000
system), select the 'Manage' option, choose 'Local Users and Groups' then:
One; create a login group called something like 'sqlusers'.
Two; create or add individual login to the group.

Once all the appropriate users have been added to this group the access the
SQL Enterprise manager. 'EM'.
One; Expand the current SQL server, the 'Security' directory and the 'click'
on the logins icon.
Two; 'right mouse click', select 'New login' and, on the General tab, from
the popdown list select your 'sqlusers' group. Make sure that the
'Authentication' radio button is selected and the current domain/server is
visible.
Three; Go back to the 'Database' and 'MyDatabase' directory and subdirectory
and 'right mouse click' the users icon.
Four; Select 'New database user' and select the new group you have just
added from the popdown menu.

There is some clean up and defining to do but you have basically defined
access rights. Now all you do is attempt a connection at the point where the
user accesses the FE Access DB and if the connection fails, close and exit
the program.  (If you need a sample of code that will connect to your server
just request it.)

You will find this way very clean, secure and not intrusive to the users.
This way is the only way I now ever setup user accounts.

HTH
Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Christopher
Hawkins
Sent: Friday, September 17, 2004 11:06 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Connecting to SQL from Access w/a single login?


This is driving me nuts.  I know I did this a few years ago, but
right now I'm totally stumped.

A client of mine wrote himself an Access app for use around his
office.  Eventually the back-end swelled up to 500MB and problems
ensued, so they decided to go to SQL Server.  He upsized the database
himself and then called me in to help get the Access front-end
working against SQL Server.

Here's my current issue; I want all the Access clients to use the
same SQL Server login rather than having a unique SQL Server login
for each domain account.

I went ahead and created an AppUser login and gave it the appropriate
permissions to the application database.  I have the AppUser login
and password set in the ODBC DSN.  But whenever a user tries to
connect, an error message appears stating that his domain login was
not recognized by the SQL Server.  It's not even paying attention to
the AppUser info.

I'm surfing BOL right now, but haven't yet figured out what I missed.
And I know I'm missing something.

Any suggestions?
Respectfully,

Christopher Hawkins
Software Developer
(559) 687-7591
http://www.christopherhawkins.com


--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com