소프트웨어/DataBase

IP works but not Named Pipes, Connecting to SQL 2005 (SQL Auth)

falconer 2009. 12. 29. 15:51
[Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [1326]

IP works but not Named Pipes, Connecting to SQL 2005 (SQL Auth)

I have SQL 2005 Std Edition, Mixed Security, and remotely connecting by TCP/IP works great.

Named Pipes (and the SQL Native Client) is the only issue.

 

The problem _appears_ to be that I'm not authenticated under the SQL Machine; however, I'm not attempting to use Windows Authentication, I'm strictly using SQL, so I do not understand why I'm having a problem.

 

My reasoning for the above conclusion, is that the command "net use \\[server_name]\ipc$" will allow me to connect to the server, and Named Pipes will begin working.  Again, remember that I'm only trying to access named pipes using SQL Authentication.

 

I've read through everything on the forum and tried everything I could find.  Below are the answers from the most common suggestions posted here:

------------------------------------------------------------------------
Version: SQL Server 2005 - 9.00.1399.06 (Intel X86)
This is the Default & Only Instance of SQL 2005, named: MSSQLSERVER
------------------------------------------------------------------------

1. The error message with the SQL Native driver (v 9.00.3042):
   Error 1326, SQL State 08001
   [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [1326]

 

2. The error messgae with the ODBC SQL driver (v 03.85.1117):

   Error 1326, SQL State 01000
   [Microsoft][ODBC SQL Server Driver][Named Pipes]Connection Open (Connect())

   Connection Failed: SQL Error 17, State 08001
   [Microsoft][ODBC SQL Server Driver][Named Pipes]SQL Server does not exist or access denied.

 

3. Local Connection on Win2003 Server works fine for IP & Named Pipes

 

4. Remote Connection using SQL Authentication
   WORKS for IP, does not work for Named Pipes

 

5. Absolutely *no firewalls* or blocked ports are being used on either machine

 

6. Configuration Manager & Surface Area Config -- both enabled TCP/IP & Named Pipes
   Currently listening on ALL IP's.   The LOG reports:

    Server is listening on [ 'any' <ipv4> 1433].
    Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ].
    Server is listening on [ 127.0.0.1 <ipv4> 1434].
    Dedicated admin connection support was established for listening locally on port 1434.

 

7. Can telnet to server on 1433

 

8. Status of NETSTAT on server:

   C:\...>netstat -ano|findstr 1433
     TCP    0.0.0.0:1433           0.0.0.0:0              LISTENING       656
   C:\...>netstat -ano|findstr 1434
     TCP    127.0.0.1:1434         0.0.0.0:0              LISTENING       656
     UDP    0.0.0.0:1434           *:*                                    2544

 

9. Tried both named instance and unnamed instance (although I'm not sure
   if I'm passing the 'named' instance properly):

   Tested: \\MYSERVER\pipe\sql\query  
   Tested: \\MYSERVER\pipe\MSSQL$MSSQLSERVER\sql\query
   (although the named instance doesn't work on the server either)

 

10.I've setup an alias for the named pipe connection.  This was based on a suggestion
   posted here, but didn't work.

 

11.I've removed the shared-memory protocol, on another suggestion here, but no luck.

 

12.If I execute "net use \\[server_name]\ipc$" it asks for credentials and if I
   enter the server credentials I have no problem with executing that command. 
   In other words the logged-on  user is not an authenticated member of the server;
   however, I'm not attempting  to use windows authentication, I'm using
   SQL Authentication.