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!

Connections

Hi Folks,
I have a question. I've developed a desktop application. But i can't decide
sql server connection strategy. The application uses by 15-20 Windows client
and it connects to same sql server.
What am i do ?
1- I can open connection start of the program and close when program is shut
down.
2- I can open connection when i need a connection and i can close
connection when i executed sql statements.
Do you have any idea?
Thanks.Hi
In general two-tier applications tend to use a single connection for the
application runtime, web applications tend to use connection pooling and
close the connection. Your application seems to be best suited to the single
connection strategy. See my other reply for some links about these.
John
"Erencan SADIRODLU" wrote:

> Hi Folks,
> I have a question. I've developed a desktop application. But i can't decid
e
> sql server connection strategy. The application uses by 15-20 Windows clie
nt
> and it connects to same sql server.
> What am i do ?
> 1- I can open connection start of the program and close when program is sh
ut
> down.
> 2- I can open connection when i need a connection and i can close
> connection when i executed sql statements.
> Do you have any idea?
> Thanks.
>
>|||John Bell wrote:[vbcol=seagreen]
> Hi
> In general two-tier applications tend to use a single connection for the
> application runtime, web applications tend to use connection pooling and
> close the connection. Your application seems to be best suited to the sing
le
> connection strategy. See my other reply for some links about these.
> John
> "Erencan SADIRODLU" wrote:
>
Thanks John for all answers.

Connections

Hi Folks,
I have a question. I've developed a desktop application. But i can't decide
sql server connection strategy. The application uses by 15-20 Windows client
and it connects to same sql server.
What am i do ?
1- I can open connection start of the program and close when program is shut
down.
2- I can open connection when i need a connection and i can close
connection when i executed sql statements.
Do you have any idea?
Thanks.Hi
In general two-tier applications tend to use a single connection for the
application runtime, web applications tend to use connection pooling and
close the connection. Your application seems to be best suited to the single
connection strategy. See my other reply for some links about these.
John
"Erencan SAÃ?IROÃ?LU" wrote:
> Hi Folks,
> I have a question. I've developed a desktop application. But i can't decide
> sql server connection strategy. The application uses by 15-20 Windows client
> and it connects to same sql server.
> What am i do ?
> 1- I can open connection start of the program and close when program is shut
> down.
> 2- I can open connection when i need a connection and i can close
> connection when i executed sql statements.
> Do you have any idea?
> Thanks.
>
>|||John Bell wrote:
> Hi
> In general two-tier applications tend to use a single connection for the
> application runtime, web applications tend to use connection pooling and
> close the connection. Your application seems to be best suited to the single
> connection strategy. See my other reply for some links about these.
> John
> "Erencan SAÃ?IROÃ?LU" wrote:
>> Hi Folks,
>> I have a question. I've developed a desktop application. But i can't decide
>> sql server connection strategy. The application uses by 15-20 Windows client
>> and it connects to same sql server.
>> What am i do ?
>> 1- I can open connection start of the program and close when program is shut
>> down.
>> 2- I can open connection when i need a connection and i can close
>> connection when i executed sql statements.
>> Do you have any idea?
>> Thanks.
>>
Thanks John for all answers. :)