Solved

SQL 2000 Cluster Help Urgent

Posted on 2007-03-26
20
585 Views
Last Modified: 2008-06-01
unable to move any instances over to the second node
was working fine for over a year  windows2003 sp1  sql 2000

17052 sqsrvres  odbc sql driverconnect failed
sqsrves check odbc  not asociated


if i try to move an instance the Ip and disk move fine then waits for a while on the sql bits then flips it back to original node.
0
Comment
Question by:mhamer
  • 12
  • 8
20 Comments
 
LVL 16

Expert Comment

by:rboyd56
ID: 18792516
Can you post the exact error messages from the application log?
0
 
LVL 16

Expert Comment

by:rboyd56
ID: 18792598
Sounds like the cluster service cannot connect ot SQL Server to verify that it is running.

Verify that the cluster service service account on the node that SQL Server will not start on has a login with SQL Server and that it is a member of the sysadmin role.

With the full messages from the application log, we an get a better idea of what is happening.
0
 

Author Comment

by:mhamer
ID: 18792621
Source  (mssql$TRN  which is the instance im trying to move to other node)

event id 17052

computer affcetd node

[sqsrvres] ODBC sqldriverconnect failed





Source  (mssql$TRN  which is the instance im trying to move to other node)

event id 17052

computer affcetd node

[sqsrvres] checkODBCConnectError: sqlstate = 28000; native error = 4814; message = [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
0
 

Author Comment

by:mhamer
ID: 18792642
we have 2 nodes,  both run 2 instances of SQL each, at present only node a will run these instances.
0
 
LVL 16

Expert Comment

by:rboyd56
ID: 18792692
This is an authentication error. For some reason the cluster server service account cannot be validated.
If the cluster service account is domain account then this coupd be because the cluster service is attempting to make a Kerberos connction and is not falling back to NTLM. Verify that the cluster service account on this node is a domain account on the same domain as the SQL Server.

If it is a domain account there are a couple of things you can do to resolve this. The easiest is to change the default client library on this node to be named pipes. You can do this with the SQL Server client network utility. This will force the cluster service to use named pipes. If the problem is related to a Kerberos connection then this will reoslve that. Kerberos does not use named pipes, only tcp/ip.

Or you can use the steps in this URL to create the necesssary SPNs for SQL Server to use Kerberos:

http://msdn2.microsoft.com/en-us/library/ms189585.aspx
0
 

Author Comment

by:mhamer
ID: 18792814
hii cheers it is the same account that is in use on node a

i have made the change to use Namedpipes  but same error ( irebooted too)

i di dnotice a big patch of "information mesages in event viewer" that seemd to indicate it was talking ok, but then back to the RED  it fillthe whole event viewer in a few seconds.
0
 
LVL 16

Expert Comment

by:rboyd56
ID: 18792849
Are there aliases established on this node that is used to connect to these instances of SQL Server?
Check the SQL Server client Network Utility on this node for aliases. If thera are any aliases, rename them and see if it will come online.

SQL Server is probably starting ok but the cluster service cannot connect. If there are aliases they could be the reason.
0
 

Author Comment

by:mhamer
ID: 18792853
Cluster resource 'SQL Server (TRN)' in Resource Group 'Training Sql' failed.
ClusSvc  
failover Mgr
1069
0
 

Author Comment

by:mhamer
ID: 18793018
not sure what you mean about rename alias's

where?
0
 
LVL 16

Expert Comment

by:rboyd56
ID: 18793069
In the client network utility
Start - Run - cliconfg
Check the alias tab for aliases
Also make sure named pipes is at the top of the enabled protocols list.
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:mhamer
ID: 18793270
hi thanks

same error though

named pipes was top and no aliases. listed

any more ideas?

thanks for yyour help by the way
0
 
LVL 16

Expert Comment

by:rboyd56
ID: 18793471
Then it could be a true authentication error.

Look in the nodes Security log for logon failures.
0
 

Author Comment

by:mhamer
ID: 18793597
not a single security error  :-(
0
 

Author Comment

by:mhamer
ID: 18793617
in fact   both the cluster service and the sql server account are both in there under sucsessful..
0
 

Author Comment

by:mhamer
ID: 18795957
ok out of hours took whole cluster down then just brought up the array and node be   same stuff  just wont start the sql resources (disk and ip etc fine)

could it be Sql rather than the node?

im a bit loath to rebuild node till i know what cause is.
0
 
LVL 16

Expert Comment

by:rboyd56
ID: 18796051
Fail SQL over to that node and let it fail on that node. You may have to set the resource to not restart.

Go to the Services applet on that node and try to start the SQL Server service. If it does start then it is not SQL Server.  Now see if you can start SQL Server Agent. If it starts then connectivity is OK

 If you are still getting login failed or use 'Null'. Then it is something to do with authentication of the either the cluster service account or the sql server agent account (provided of courxe you get the same error starting SQL Server Agent)

When a user attempts to connect the account is passed to the DC to be authenticated. IF the account cannot be authenticated then the NULL user is passed back to SQL Server. That is why the error is being generated..

0
 

Author Comment

by:mhamer
ID: 18796780
did as you suggested, the instance i failed over did start using services  as did the matching sql agent.
but would not come live in cluster mgr,

tried to remake instance (old db not needed)  and i cant make a new instance, similar error not being able to connect to sql  or it diesnt exist..
0
 

Author Comment

by:mhamer
ID: 18818436
Sorted, any one know what might cause the issue though.

started to go through each and every error in event log to the point it no longer occured.

the fix was to  in adsi edit  alter the rights  of the sql server account,
the setserverprinciplename was read only, changed it to write  and it all started to work.

so what would cause the rights to change?  no one who works there even knows what adsiedit is.
0
 
LVL 16

Accepted Solution

by:
rboyd56 earned 500 total points
ID: 18818464
That is strange. The only thing that I can think of it is that someone changed it.
0
 

Author Comment

by:mhamer
ID: 18980195
Thnak you for your time and effort
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Introduction You may have a need to setup a group of users to allow local administrative access on workstations.  In a domain environment this can easily be achieved with Restricted Groups and Group Policies. This article will demonstrate how to…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now