Can I use NT Authentication with server running as "System Account"

[jadn]

Hi,
I'd like to use NT Authentication to allow Domain Users to connect (via ODBC connections) to an SQL Server (2000). The server is running under "System account". When adding myself as a Login on the server, I can connect from a client. I've also added a "MYDOMAIN\Domain User" group-Login, but domain users (besides me) get the "[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user" message. I'm using the "ODBC Data Source Administrator" to do the connection-testing on client machines.

Right now, my applications want to connect to the Server using an ODBC connection string like "ODBC;DSN=MyServer".

In searching for an answer, I've seen some references relating OLE DB with ODBC, but don't understand if/how it might apply to my situation. Have also seen solutions which require running server as a specific domain-user (instead of System account) - but this is not an option.

Any help/insight would be appreciated,
Cheers!

 

[maswien]

It wouldn't matter the service is run under 'system' accunt or your domain account. To use the windows athentication, the NT account need to be add to the logins of SQL Server. The easy way is create NT groups on the server then add domain users to their group. Add these groups to SQL Server logins.

 

[jadn]

Hi maswien,
Thanks for the reply. When you say "add domain users to their group", do you mean to add users individually? Is it possible to grant permission to all "MyDomain" users without having to specify each specific user?

Thanks!

 

[maswien]

yes, you can do that. You create separate groups only for granting different permissions to the users.

 

[jadn]

Hi maswien,
I added a [Local]"MyReadGroup" to the server, then added "MyReadGroup" as a Login in SQL Server.

After adding a specific user, "myUser", to MyReadGroup, then myUser can connect. If I remove myUser from MyReadGroup, and add "MyDomain\Domain Users", then myUser can no-longer connect.
The error message is:
"[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'MyDomain\myUser'

... which implies that the server knows myUser is a member of MyDomain, but myUser isn't getting authenticated as a member of "MyDomain\Domain Users" - is it reasonable to expect this?