ODBC connection not work without adding users to administrator group 1

[cooperchien]

I have a SQL server that accepts both SQL and NT authentication. This server belongs to an Active Directory Domain but is not the domain server itself. A user who belongs to a workgroup logs on to a workstation and want to create an ODBC connection to this SQL server. However, he keeps getting a login failure error message. (Login failed for user 'SQLserver\username')
I found that if I add that user to the SQL server's local administrator group, he then can establish ODBC connection.
Does that mean I have to add all the users who wants to have ODBC connection to the SQL server to the lcoal admin group? Is there another way to enable the users to have ODBC connection to the SQL server without adding them to the local admin group???
I cannot add the user to my SQL server's domain because he needs to belong to the other domain.
Please help. Thank you very much.

 

[mutley1]

It's probably 1 of 2 things.

1. Does the users domain account has access to the SQL server (check in Enterprise manager - Server / Security / Logins)
2. What version of SQL are you running? If it's 7 with SP4, you might need to add a regisrty key.

http://support.microsoft.com/?kbid=823492

It's probably the first one. The reason (I'm guessing) it works when you add them to the adminstrators group is because their individual account has not been granted access to SQL Serve, but you still have the builtin\administrators role there.

Cheers,

M.

 

[mikealco]

I have a similar problem, the BUILTIN\Administrators group works correctly. I have created another group of which there are two members (in AD), in the sql security the group exists, but yet I cannot get ODBC access to sql server still......any further ideas would be gratefully accepted

 

[mikealco]

I should clarify, I am using SQL 2K on server 2003....

I have just cured the problem by deleting the User out of SQL2000 and then re-adding it

All appears OK now