Problem Connecting to Sql Server 2005 locally by Name

Configuration:
Sql server running on a test machine with mirrored drive for OS and SAN for
data.
Problem:
A week ago a mirrored drive went down. We reconfigured the drive and
updated the drivers on the drive. About the same time the backups stopped
running. The sqlagent reported errors like this one:
2007-08-09 19:03:16 - ! [382] Logon to server 'DatabaseName' failed
(ConnAttemptCachableOp)
2007-08-09 19:03:46 - ! [298] SQLServer Error: 87, SQL Network Interface
s:
Connection string is not valid [87]. [SQLSTATE 08001]
Also note that when trying to connect to the database via the Sql Server
Management Studio using the name on the local machine an error is thrown
stating that a connection couldn't be made; however, when using the IP there
is no problem.
Note that there is no problem connecting to the machine from remote machines
and that the problem is only when trying to connect to the db using the name
from the machine. Reporting services can no longer connect nor the sql agen
t.Hmmm. When connecting locally the SQL Server Native Client will attempt to
connect using the Shared Memory protocol. If you connect locally using the
IP address, the SQL Server Native Client will attempt to connect using TCP.
So I'm guessing the problem is with shared memory. Is it still enabled on
both the server and for client connections on the server? You can use SQL
Server Configuration Manager to check both.
Did the server forget it's name? What is the result of SELECT name FROM
sys.servers ?
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Wes" <Wes@.discussions.microsoft.com> wrote in message
news:D0BA4742-F782-44AC-A7A8-86E4C8E7ACEA@.microsoft.com...
> Configuration:
> Sql server running on a test machine with mirrored drive for OS and SAN
> for
> data.
> Problem:
> A week ago a mirrored drive went down. We reconfigured the drive and
> updated the drivers on the drive. About the same time the backups stopped
> running. The sqlagent reported errors like this one:
> 2007-08-09 19:03:16 - ! [382] Logon to server 'DatabaseName' failed
> (ConnAttemptCachableOp)
> 2007-08-09 19:03:46 - ! [298] SQLServer Error: 87, SQL Network Interfa
ces:
> Connection string is not valid [87]. [SQLSTATE 08001]
> Also note that when trying to connect to the database via the Sql Server
> Management Studio using the name on the local machine an error is thrown
> stating that a connection couldn't be made; however, when using the IP
> there
> is no problem.
> Note that there is no problem connecting to the machine from remote
> machines
> and that the problem is only when trying to connect to the db using the
> name
> from the machine. Reporting services can no longer connect nor the sql
> agent.|||Rick,
Thx for the reply.
1. Q:Did the server forget it's name?
-- Running the supplied query returns the correct name of the server. So,
this is okay.
2. Q:Is it still enabled on both the server and for client connections on
the server?
-- Yes. I disabled Shared Memory for both the client & server and tried
again to connect and still recieve the message " Cannot connect to {da
tabase
name}. An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the fact
that under the default settings SQL Server does not allow remote connections
.
(provider: SQL Network Interfaces, error: 25 - Connection string is not
valid) (Microsoft SQL Server, Error: 87)"
*note: I have no problem connecting remotely. However - I do have a
problem connection locally and the services all throw similar errors when
trying to connect to the database. Also, I cannot apply any service packs
because I can not authenticate locally.
"Rick Byham, (MSFT)" wrote:

> Hmmm. When connecting locally the SQL Server Native Client will attempt to
> connect using the Shared Memory protocol. If you connect locally using the
> IP address, the SQL Server Native Client will attempt to connect using TCP
.
> So I'm guessing the problem is with shared memory. Is it still enabled on
> both the server and for client connections on the server? You can use SQL
> Server Configuration Manager to check both.
> Did the server forget it's name? What is the result of SELECT name FROM
> sys.servers ?
> --
> Rick Byham (MSFT)
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Wes" <Wes@.discussions.microsoft.com> wrote in message
> news:D0BA4742-F782-44AC-A7A8-86E4C8E7ACEA@.microsoft.com...
>|||I can’t explain it (yet) and it may just be coincidence but I was testing
connections to the database in the ODBC Data Source Administrator and create
d
a connection using the sqlsrv33 driver and then the sqlncli driver and now
all my local connections work and the processes run fine. One thing is the
default value for the pipe name came up incorrectly. I corrected the pipe
there and the test connection worked. It doesn’t make sense to me that th
is
value would be persisted anywhere and that this fixed the problem. Anyway i
t
is working now and we’ll see for how long. If anyone has a valid explanat
ion
on if this is just a coincidence or if it actually corrected the issue,
please share. Thx.
"Wes" wrote:
[vbcol=seagreen]
> Rick,
> Thx for the reply.
> 1. Q:Did the server forget it's name?
> -- Running the supplied query returns the correct name of the server. So
,
> this is okay.
> 2. Q:Is it still enabled on both the server and for client connections on
> the server?
> -- Yes. I disabled Shared Memory for both the client & server and tried
> again to connect and still recieve the message " Cannot connect to {
database
> name}. An error has occurred while establishing a connection to the serve
r.
> When connecting to SQL Server 2005, this failure may be caused by the fact
> that under the default settings SQL Server does not allow remote connectio
ns.
> (provider: SQL Network Interfaces, error: 25 - Connection string is not
> valid) (Microsoft SQL Server, Error: 87)"
> *note: I have no problem connecting remotely. However - I do have a
> problem connection locally and the services all throw similar errors when
> trying to connect to the database. Also, I cannot apply any service packs
> because I can not authenticate locally.
> "Rick Byham, (MSFT)" wrote:
>|||When you connect locally by name, SQL Server Native Client uses shared
memory. And shared memory is a local pipe. So when you fixed something in
named pipes, it does make sense that it might have fixed shared memory.
--
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Wes" <Wes@.discussions.microsoft.com> wrote in message
news:4D86C15C-5B9D-4DEB-B61B-FF392985D5B1@.microsoft.com...[vbcol=seagreen]
>I can’t explain it (yet) and it may just be coincidence but I was testing
> connections to the database in the ODBC Data Source Administrator and
> created
> a connection using the sqlsrv33 driver and then the sqlncli driver and now
> all my local connections work and the processes run fine. One thing is
> the
> default value for the pipe name came up incorrectly. I corrected the pipe
> there and the test connection worked. It doesn’t make sense to me that
> this
> value would be persisted anywhere and that this fixed the problem. Anyway
> it
> is working now and we’ll see for how long. If anyone has a valid
> explanation
> on if this is just a coincidence or if it actually corrected the issue,
> please share. Thx.
> "Wes" wrote:
>