Connectivity Issue

Hi Folks,

Im a bit stuck with this one.

Have a SQL 2000 Instance that we require a load of clients to connect to.

It seems that as soon as we get the MSSQL$Service to run under a domain account we can only authenticate to SQL Server if we set up a client alias.

Under a local system account it seems to authenticate without any issues.

This is a real pain as I don't want to have to set client ailas up on every one of my clients.

Anybody seen this sort of issue before.

Thanks in advanceAddtional Info :

The domain account that is running the mssql$service is in a differnet domain from the users that are connecting to SQL Server. There is a trust in place between the two domains.

The connection error is your usual null user not associated with a trusted connection.

The error log says SQL is listening on TCP, Shared Mem and Named Pipes|||Sorted

Bloody Group Policy on the service account.

Service Account had to get additional permissions.

Thanks|||This sounds cool .. any idea what permissions were required for the service accounts ?

Thanks,
-Ranjit

----------------------
Its OK to be a fool for 5 minutes than for the rest of your life ( Old Japanese Proverb )|||To summarise the problem for future reference:

Situation
Windows Server 2003 in RBSRES01.Net domain
SQL Server 2000 + SP4 (and any other appropriate patches)
SQL Service running under domain account (either EUROPA or RBSRES01)

Connection From
Enterprise Manager or ODBC (DSN or Ms Access)
Any client machine (workstation or server) without a named pipe client alias setup

Error
Server: Msg 18452, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

Root Cause
As per the below Microsoft KB article the account running SQL server must have certain windows rights. To grant these rights under the RBSRES01 group policy setup, the account needs to be a member of the following domain local security groups:
sSRV-AssignPrimaryToken-Privilege
sSRV-BatchLogon-Right
sSRV-ChangeNotify-Privilege
sSRV-EnableDelegation-Privilege
sSRV-Impersonate-Privilege
sSRV-LockMemory-Privilege
sSRV-ServiceLogon-Right

http://support.microsoft.com/kb/840219

Simply adding the account to the local Admins group will not suffice!