Connection inconsistency - SQL 2005 1

[Glasgow]

I am having problems connecting to a SQL 2005 Express instance using Windows authentication.

I can consistently connect using SQL Server Management Studio Express and run queries etc without problems. However attempts to connect with OSQL using:

osql -E -Smypc\sqlexpress

are inconsistent - sometimes they work, sometimes I get a "Cannot generate SSPI context" error message.

I also get similar inconsistency when trying to connect with Visual Basic (VB6). In all cases I am attempting to use Windows Authentication - I supply no username or password.

I cannot tie this down to any sequence of events. Everything tends to work fine for a while then it fails and I either have to reboot or wait a while until the problem 'disappears'.

I have SQL 2000 Development edition installed on the same machine which may or may not be relevant.

Any ideas?

 

[mrdenny]

Cannot generate SSPI context error messages can usally be traced back to a domain account event. Either your account or the SQL Servers account gets locked out, one of the passwords has been changed recently, etc.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Glasgow]

Thanks.

But it's so intermittent and I'm the only one working on the machine. So why would the circumstances change in the course of 20 minutes or so?

What I have found is that I can re-establish the connection by toggling (i.e. either stopping or starting) the MSSQLSERVER SQL 2000 service which at least gets around the need for a reboot.

One point which may be relevant is the fact that at one stage I was inadvertently running SQL scripts under both SQL 2000 and SQL 2005 which were restoring 'independent' databases to the same physical disk file. I have since dropped the database on both servers but this could have confused matters. Not sure how I would go about resetting things.

 

[mrdenny]

The database restore issue shouldn't matter.

If the domain account which runs the SQL Server gets locked out, then unlocks quickly I could see the SSPI error comming up. Check the security logs on the domain controllers and see if there are any failures in there.

You can also try setting up another domain account, and having try running the SQL Server under that account.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Glasgow]

Thanks for the reply.

Well I've taken a look at the logs on my PC (which hosts both SQL2000 & SQLEXPRESS). The security logs are empty but there are a few recurring issues in the application logs:

SQLEXPRESS (failure audit): Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection. [CLIENT: 10.0.0.13]

While I have personally made a few attempts to login as 'sa', these messages are cropping up at times when I am fairly sure I'm not responsible but I can't explain this.

SQL2000 (warning): SuperSocket info: (SpnRegister) : Error 1355.

This is explained at

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

though I'm not sure I'm much the wiser!

SQLBROWSER(warning): The configuration of the AdminConnection\TCP protocol in the SQL instance SQLEXPRESS is not valid.

There is a long debate on this at

http://forums.microsoft.com/msdn/sh...36&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=0

but I'm not sure this is the problem.

Similar messages appear on a similarly configured PC on our network which does not appear to be experiencing the same symptoms.

The SQLEXPRESS service starts under the NT AUTHORITY\NetworkService account - which I assume is a default. The SQL2000 (MSSQLSERVER) service starts under my own domain account (administrator).

Any further thoughts? How would such a 'lock-out' occur?

 

[Glasgow]

A possible bombshell....

I get the problem when the SQL 2000 (MSSQLSERVER) service has not even been started. I can then (temporarily) resolve the problem by starting the MSSQLSERVER service.

 

[mrdenny]

It appears that the SQLEXPRESS instance is setup for Windows Auth only.

Try setting it to run under a domain account.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Glasgow]

Thanks - tried changing account as you suggest but no joy I'm afraid. Sob.

 

[mrdenny]

Can you post the most recent ERRORLOG file.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Glasgow]

I really appreciate your persistence with this, Denny, thanks.

What I did this morning was to follow my usual pattern and monitor the SQL 2005 ERRORLOG file while doing so. I did NOT start SQL 2000 so that I could eliminate it from the process.

I ran my VB application several times without incident and the ERRORLOG remained static. Eventually (around 9:55am), and true to form, the application failed. The error returned to the application was no -2147221504 (automation error - invalid OLEVERB structure). This did NOT affect ERRORLOG.

I realised that, when the app fails to connect at first attempt, it then tries to connect as "sa" which causes the last 2 entries in ERRORLOG.

The tail of ERRORLOG looks like this:

2007-06-01 07:35:08.35 Server      Dedicated administrator connection support was not started because it is not available on this edition of SQL Server. This is an informational message only. No user action is required.
2007-06-01 07:35:08.37 Server      The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
2007-06-01 07:35:08.39 Server      SQL Server is now ready for client connections. This is an informational message; no user action is required.
2007-06-01 07:35:09.01 spid8s      Clearing tempdb database.
2007-06-01 07:35:10.51 spid8s      Starting up database 'tempdb'.
2007-06-01 07:35:10.79 spid5s      Recovery is complete. This is an informational message only. No user action is required.
2007-06-01 07:35:10.87 spid11s     The Service Broker protocol transport is disabled or not configured.
2007-06-01 07:35:10.92 spid11s     The Database Mirroring protocol transport is disabled or not configured.
2007-06-01 07:35:11.23 spid11s     Service Broker manager has started.
2007-06-01 08:43:50.57 Server      Server resumed execution after being idle 4088 seconds: user activity awakened the server. This is an informational message only. No user action is required.
2007-06-01 08:44:04.76 spid51      Starting up database 'UpLine'.
2007-06-01 09:56:49.18 Logon       Error: 18452, Severity: 14, State: 1.
2007-06-01 09:56:49.18 Logon       Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection. [CLIENT: 10.0.0.13]

Repeated attempts at firing the app failed. I restarted the SQL 2005 service and that cleared it. Until now, I have been restarting SQL 2000 service but that would appear to be something of a red herring.

Any thoughts as to where this leaves me? I wonder if it could be a problem with some other SQL related component or service that might be used by VB and OSQL but not by Management Studio Express?

 

[Glasgow]

Any further thoughts Denny?

My only thought is to uninstall and reinstall both SQL 2000 & SQL 2005 Express which I don't relish.

Thanks again.

 

[mrdenny]

I wish that we could get more specific information from the application about what has failed.

It may be related to the SPN error further up in the error log.

What authentication policies are setup on the domain for authentication? In Kerebos required os is NTLM allowed?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Glasgow]

What authentication policies are setup on the domain for authentication? In Kerebos required os is NTLM allowed?

Sorry, but we're starting to move out of my comfort / confidence zone! How do I go about checking this?

What I have done is explore the error log message a bit further and

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

tells you how to go about implementing Kerberos authentication. I'm struggling with that a bit - only because I happen to be a bit short of time and it suggests I may need to get hold of additional utilities.

It seems to concentrate on 'remote' connections but I am connecting to an instance on my local PC. Though the SQLEXPRESS service has been starting under a domain administrator account.

 

[mrdenny]

How do I go about checking this?

You ask your sysadmin. S/he'll know.

Is any other software which connects to the SQL Server throwing errors? If so what errors?

What driver is being used to connect to the SQL Server?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Glasgow]

Thanks yet again. I'll get on the case and report back. I have been having problems with only VB6 and OSQL. I have no problem with sql server management studio express. If it helps, I am using the following connection string with VB6:

provider=sqloledb.1;Integrated Security=SSPI;persist security info=false;initial catalog=mydb;data source=mypc\sqlexpress

I have changed it today to one recommended for SQL2005 to see if that makes any difference:

Driver={SQL Native Client};Server=mypc\sqlexpress; Database=mydb;Trusted_Connection=yes;

I believe OSQL has been superseded by SQLCMD. Just maybe it's all going wrong because I'm using 'old stuff' - i.e. old connection string and OSQL.

 

[mrdenny]

Correct OSQL has been replaced by SQLCMD. OSQL uses the SQL 2000 drivers while SQLCMD uses the SQL 2005 drivers.

Let us know if the SQL 2005 drivers help any.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Glasgow]

I didn't get a chance to prove the SQL 2005 drivers yesterday. I connected with them fine but, when firing a report from the VB application that triggers a reasonably complex query, it returned an empty recordset. As it was immediately prior to a demo, I reverted back to the SQL 2000 connection string. Somebody's out to get me!

I will try to establish why the query is failing. I will also investigate SQLCMD and see if, when VB loses its ability to connect, whether SQLCMD behaves like OSQL (i.e. also fails to connect). If SQLCMD connects, we may at least be on the right track.

 

[andysk]

Perhaps you could try Profiler to see detail of command to sql server.

 

[Glasgow]

OK, 2 weeks on and I have made some progress (I've been forced to treat this as a low priority). There are 3 areas that can fail and I can get around 2 of them:

1. OSQL will fail but SQLCMD does not, so not a problem, I can use SQLCMD.

2. ADO fails with SQL 2000 connection string but it works with SQL 2005 string so not a problem, I can use SQL2005 string.

3. SQLDMO fails within VB6 application on connect using LoginSecure = True. The error is -2147221504 (cannot generate SSPI context). I have yet to find the solution to this but perhaps there's a new version of SQLDMO object library for SQL 2005. I will investigate.

 

[Glasgow]

Hmmm. Apparently SMO (SQL Management Objects) is probably where I should be heading. Although

http://www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx

suggests:

You can still use SQL Server 2005 with SQL-DMO, but SQL-DMO will not be updated to manage features that are specific to SQL Server 2005.