Issue connecting ODBC to SQL Server 2005

Posted on 2009-04-16
Last Modified: 2012-05-06

I'm running SQL Server 2005 Standard Edition on a Windows Server 2003 SP2 Standard Edition.
I have a database in SQL Server used for a program installed on each client of my network.
To access the database, each client has an ODBC connection setup on their machine to connect to the database.
I want the ODBC to use Windows Authentification.

For the Admins on the domain I have no issue to create an ODBC and connect to the database.
But for the "regular" domain users (without Admin rights),
the ODBC won't work (Error: 18456, Severity: 14, State: 11) unless I add each individual username as a user in SQL Server.

I have over 40 clients and I don't want to add the users one at a time in SQL Server.
I was wondering if there is a way to allow all my domain users to connect to SQL Server without adding them one by one?

I hope I was clear enough.

Thank you,
    1 Comment
    LVL 26

    Accepted Solution

    One thought would be to add a login to SQL Server for "NT AUTHORITY\Authenticated Users" and grant access to your database objects to that login.  This will lump all users together.  If that is not ok and/or a more secure solution would be to create one or more groups on the machine or domain, grant those groups appropriate db access and put the users in there.  It would still mean you have to add each new user somewhere, but it would be outside of SQL.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now