March 7, 2011 at 2:25 pm
Hope someone can guide me to a solution.. Been running the same setup for 18 months. Last week SQL Server 2005 started disconnecting ODBC and web clients at the same time. ODBC are internal users interfaced with Access dbs... message is SQLState 28000 SQL Error 18456 etc... Anyone outside the firewall gets "OLE Db Provider for SQL Server... Cannot generate sspi context.
I don't use a DC as the server is on a WorkGroup.
If I restart the server all is well... the disconnects are random... sometimes going 24 or more hours without a disconnect... other times 30 minutes.
I have checked Surface Area Configuration Manager to verify that TCP connections are allowed.
March 7, 2011 at 4:01 pm
Did you upgrade the server recently, for example by applying a Service Pack or Cumulative Update and not update the SQL Server Native Client on all the clients running Access?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 7, 2011 at 4:27 pm
No service paks or manual updates, but I do have auto-updates active on the server. Another note... I am not using the SQL Client... I am using the OBDC System DSN to connect... up to now, this has not been an issue. Also I can see how that might hose up the internal ODBC clients but why would the webclients be effected at the same time? I'm fairly new to SQL Server so please forgive my ignorance if my questions are silly... I'm a fast learner 😛
March 7, 2011 at 4:47 pm
Just so we're on the same page, and in case it changes your response...the "Native Client" driver, ODBC driver and OLE DB driver are all part of the "SQL Native Client Library". All access methods leverage the same core "Native Client" functionality. If you were to upgrade "the native client library" it would upgrade all three pieces.
Re: your System DSN, how is the authentication setup, using a SQL Server login or using Windows Authentication?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 8, 2011 at 4:38 am
thanks for Native Client info... still learning the lingo... I am using Window Authentication and TCP instead of Named Pipes.
March 8, 2011 at 6:48 am
No worries, we're all learning 🙂
You said you don't have a DC, that your server is just in a Workgroup, but your DSN is setup to use Windows Authentication. How are you achieving that...pass-through Authentication? Are the Access clients going through the same firewall as the web clients? You mentioned your web clients are going through a firewall and are receiving an sspi context error as well...are saying they are using pass-through Windows Authentication too?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 8, 2011 at 7:23 am
Yes.. Using pass-through authentication (I have the inside Access users all set up on that Server as "Users"). The Access users are inside of the firewall.... The web users are coming in through the firewall/routers and routed to this server where IIS authenticates them via usernames/passwords. Does that make sense?
March 8, 2011 at 11:05 am
tcloud (3/8/2011)
Yes.. Using pass-through authentication (I have the inside Access users all set up on that Server as "Users"). The Access users are inside of the firewall.... The web users are coming in through the firewall/routers and routed to this server where IIS authenticates them via usernames/passwords. Does that make sense?
The Access clients make sense. On the web sites, it sounds like you may have IIS Application Pools setup that log into the OS locally, and those same login names were used to setup local accounts on the SQL Server machine with matching passwords.
Do all web servers and all Access clients lose their connections simultaneously? Or is it selective?
Assuming you have some logging in place what do you see in the way of error messages?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 8, 2011 at 11:42 am
They all lose connectivity at the same time... in and outside the firewall... Error messages in the event logs are as follows (not sure if they are related...)
* QoS RSVP has failed to find any interfaces with traffic control enabled
* Unable to open ATK Device for R Access. Returning IO Status, Blocked Data
* DCOM got error system cannot find specified ". Attempting to start service My AgtSvc with Argument -com service started
March 8, 2011 at 12:09 pm
I don't know if any of those messages are related to your issue.
I am kind of shooting in the dark here as this could be any number of things and I don't have a full set of info to work from, however I am going to go out on a limb and say that this is probably not a SQL Server issue.
- Have you checked your Network Card drivers lately?
- Are there any other services on the machine hosting SQL Server that also have issues around the same time when SQL Server drops all the connections? This could tell us it's a generic OS issue, or it could point to a culprit application that is resetting network connections for some other purpose (like a software firewall, spyware prevention or virus protection app).
- Have you looked into the SQL Server Error Logs to see what they say leading up to and immediately after one of these episodes?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 8, 2011 at 12:30 pm
I will take a look at the NIC and see if the drivers need to be updated...
As for other services, This server only runs the SQLServer (not to say the Windows loaded a bunch of services that I'm not using..) No software firewall installed, and I removed McAfee services
Where do I get access to the SQLServer Logs? is this through the SQLServer Studio?
I agree with you... I'm thinking that SQLServer is not the culprit at this point. Thanks again for your help! I'll let you know what the final solution was (once I find it...)
March 8, 2011 at 12:36 pm
tcloud (3/8/2011)
Where do I get access to the SQLServer Logs? is this through the SQLServer Studio?
In SSMS Object Explorer under Management.
Let us know if you get to the bottom of it. Good luck 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply