I have created several Sql 2000 Db's over the last year accessed through an Access Front End. In the past I have authenticated Users with SQL Login Accounts.

However as with happens when more people use the systems, I am getting a large number of calls requesting support.

The biggest is resetting User's Passwords. I have hit a brick wall when trying to reset a Users Password through an ADO Connection using the SP_Password Stored Procedure.

I am now wondering whether I should really have a seperate SQL Table containing UserNames / Encrypted Passwords, and move away from SQL Server Authentication.

Added to this is the fact that Users at the moment are members of SQL Groups defining what they can and can't do.

I'd just be interested in other peoples take....


Cheers

Hi,

The best practice for User Authentication when working with SQL Server is Windows Authentication.

This will reduce your Administration overhead. So the best thing would be add these sql authenticated users to a Windows Group, say your AppUsersGroup that is currently accessing SQL Server.

1) Now open up Enterprise Manager, navigate to the Security Folder and under Logins choose new Login.

2) Now under the login name, choose Search, you can add members from the Active Directory

3) Now your users will be authenticated to SQL Server via Windows Authentication.

Hope this helps

Cheers!

Anil Mahadev
Senior SQL Server DBA
MISPL

http://anilm001.myfreewebs.net
http://www.sqlserverclub.com
http://www.sqlpass.ch
http://www.sqlpass.org

Thanks for getting back to me.

I appreciate your answer but feel you are removing a layer of security that I need.

I want the User to logon through the Front End with a seperate account.