locked
ODBC only working for domain admins RRS feed

  • Question

  • I'm very much a Microsoft SQL Server newbie, so please be patient with me, folks.

    Environment - I've got the Express edition of SQL Server 2008 R2 running on a Windows Server 2008 machine. This server hosts a single database for an Accounting & PM software called 'Ajera'. Most of our staff are running 64-bit Windows 7 machines and are all set up as LOCAL administrators for their own machines.

    I've recently created an ODBC driver to connect to Ajera's database so that I can pull information into Excel & build some custom reports, giving me more flexibility than the reporting options in Ajera itself. This has worked like a charm for me (a domain admin), but does not work for any of our regular domain users unless I add them to the Domain Admins group.

    The first error message is what I receive when I attempt to create an ODBC data source for a regular user using Windows authentication. This message pops up when I test the connection, at the end of the ODBC setup process.

    If I create the ODBC data source using the 'sa' account, the test works successfully. However, when I open up an Excel file that uses the ODBC and attempt to refresh the data, the second error message below is what pops up.

    So, authenticating the ODBC driver both by using Windows authentication, and by using the 'sa' account credentials will work fine when the user is a member of the 'Domain Admins' group, but fail for everyone else.

    I've looked around in Active Directory, but have been unable to find something like a 'Database Administrators' group or anything like that which would give my domain users access to querying the database without having to be domain admins.

    Can anyone help me figure this out? Thanks!

    Monday, August 20, 2012 6:03 PM

Answers

  • Hello,

    You have to add the Windows login in SQL Server itself to grant access to the database, of course.

    Start SSMS (SQL Server Management), logon to SQL Server, add the logins to "Security", create database users and may add them to database role "db_datareader" to grant select permissions to all tables/views.


    Olaf Helper
    Blog Xing

    • Marked as answer by Iric Wen Tuesday, August 28, 2012 8:38 AM
    Tuesday, August 21, 2012 6:42 AM