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!

Connection string

hi. I have trying to load packages from SQL server using "load from SQL comman" and then programmatically changing the connection manager's certain properties(as this property has to be changed in run time.

It works fine when these packages are running from same database but when programmatically I am changing its initial catalogue to some other database, then this is not reflecting even after changing the initial catalogue. Its referring to database which these are created upon. If anyone has some idea please reply back

Is your catalog hardcoded somewhere in the package (like in a table name)?

I would suggest you open your package, try to change the connection string manually and then debug your package step by step.

HTH.

|||

thanks for your reply ,

Well yes the database name is getting added along with the table name.

is ther any way to place just table name or a way to remove the database name ?

Thanks,

|||

Could you give us more details? What component are you referring to? Is it OLE DB Source/Destination?

Most probably it can be fixed; just tell us what component has the catalog name hard-coded.

Thanks.

|||

Anshu nautiyal wrote:

thanks for your reply ,

Well yes the database name is getting added along with the table name.

is ther any way to place just table name or a way to remove the database name ?

Thanks,

That is your problem; make sure all sql statements in the package do not have the dabase name as they may be diffrent when you change the connection strings. Just edit the sql statements and remove the database part of the name.

|||

Anshu nautiyal wrote:

thanks for your reply ,

Well yes the database name is getting added along with the table name.

is ther any way to place just table name or a way to remove the database name ?

Thanks,

That is your problem; make sure all sql statements in the package do not have the dabase name as they may be diffrent when you change the connection strings. Just edit the sql statements and remove the database part of the name.