Mystery - SQL Suddenly Failing to Connect

Question

Mystery - SQL Suddenly Failing to Connect

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

homebrew01 (12/1/2009)
Lynn Pettis (12/1/2009)
In the SQL Server Configuration Manager, what protocols are enabled for the SQL Server instance?
"TCP\IP only" ... our usual setting

Okay, but looking at the error message it looks like it is trying to use shared memory not TCP/IP.

login failed : HResult 0xE9. Level 16 State 1 shared memory providor no process is on the other end of the pipe. Sqlcmd: Error: MS Native Client: Client unable to establish connection.

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

Lynn Pettis (12/1/2009)
homebrew01 (12/1/2009)
Lynn Pettis (12/1/2009)
In the SQL Server Configuration Manager, what protocols are enabled for the SQL Server instance?
"TCP\IP only" ... our usual setting
Okay, but looking at the error message it looks like it is trying to use shared memory not TCP/IP.
login failed : HResult 0xE9. Level 16 State 1 shared memory providor no process is on the other end of the pipe. Sqlcmd: Error: MS Native Client: Client unable to establish connection.

I answered incorrectly. I should have said:

Surface Config: TCP/IP Only

SQL Config Manager Protocols for MSSQLSERVER:

Shared Memory - Enabled

TCP\IP - Enabled

SQL Native Client Config:

Shared Memory 1 Enabled

TCP/IP 2 Enabled

Named Pipes 3 Enabled

These settings match my other servers that have no issues.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Might be worth calling Microsoft's customer support on this

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eoin The Forge SSCrazy Points: 2932 More actions · Answer 4

homebrew01 (12/1/2009)
I've rebooted several times, and the drives have been available. Not sure about drive availability when the problem first appeared.
The errors in post #4 came from the event log.
If SQL can't find a particular file or drive for a non-system DB, wouldn't SQL still start ok, but just give an error on that database, showing as "suspect" or something ?

I see a SPN error message in the posted sql logs, have you recently changed the permissions of the service accounts (or changed the accounts).

Also is the E drive connected to the SAN using the same hardware and drivers as the F:??

homebrew01 SSC Guru Points: 55548 More actions · Answer 5

Eoin (12/2/2009)

I see a SPN error message in the posted sql logs, have you recently changed the permissions of the service accounts (or changed the accounts).
Also is the E drive connected to the SAN using the same hardware and drivers as the F:??

We did change the Service Account being used from a domain admin to local admin. The services do start under that account. The E drive is on the SAN, the F drive is local.

homebrew01 SSC Guru Points: 55548 More actions · Answer 6

GilaMonster (12/2/2009)
Might be worth calling Microsoft's customer support on this

I suggested that to the Network Admin, but he doesn't want to pay. :angry:

But we might have to anyway if there's no other solution.

Eoin The Forge SSCrazy Points: 2932 More actions · Answer 7

homebrew01 (12/2/2009)

Eoin (12/2/2009)
I see a SPN error message in the posted sql logs, have you recently changed the permissions of the service accounts (or changed the accounts).
Also is the E drive connected to the SAN using the same hardware and drivers as the F:??
We did change the Service Account being used from a domain admin to local admin. The services do start under that account. The E drive is on the SAN, the F drive is local.

Can you switch back the account temporarily - Bounce it and see does that have an impact...

homebrew01 SSC Guru Points: 55548 More actions · Answer 8

I tried to install a separate instance of SQL on the server "Instance2". I got past the authentication screen, but partway through the "Setup Process", on the "SQL Server Database Services - Configuring Components" step, I got a similar error:

SQL Server Setup could not connect to the database service for server configuration. The error was: SQL Native Client Shared memory provider: No process is on the other end of the pipe. Refer to server error logs and setup logs for more information. ......

From the event log:

The configuration of the AdminConnection\TCP protocol in the SQL instance INSTANCE2 is not valid.

and

A provider, SQLServerEventProvider, has been registered in the WMI namespace, root\Microsoft\SqlServer\ServerEvents\INSTANCE2, but did not specify the HostingModel property. This provider will be run using the LocalSystem account. This account is privileged and the provider may cause a security violation if it does not correctly impersonate user requests. Ensure that provider has been reviewed for security behavior and update the HostingModel property of the provider registration to an account with the least privileges possible for the required functionality.

homebrew01 SSC Guru Points: 55548 More actions · Answer 9

Resolved. See Updated in Post #1

Thanks for all the help.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Glad to see that you were able to correct the problem.