3

We have an application where our clients are connecting to a SQL Server 2005 database - via a SQL Native Client ODBC data source. We are having some difficulties with the ODBC connection getting severed during program execution. After questioning a tech support person, he said that he had seen this type of error before, but they fixed the issue by configuring the clients to connect using Named Pipes (primarily), rather than TCP/IP.

So I did some research and found where to configure client access on the server - via the SQL Server Configuration Manager. However, there does not appear to be a way to configure the SQL Native Client ODBC data source on the client machine itself. The older SQL Server ODBC driver did allow you to configure it to use Named Pipes, or TCP/IP, but the SQL Native Client does not.

Does the SQL Native Client data source automatically decided which method to use to connect to the database? Is there a way to configure it?…and is there a way to find out which method a particular client machine is using to connect?

Any help would be appreciated.

--Thanks Mike C.

Clinemi
  • 906
  • 6
  • 20
  • 33

3 Answers3

5

Note you can set the protocol used in the connection string, there is no need to configure the machine. I would recommend NOT changing the machine configuration using cliconfg, since this impacts all applications running on the machine.

So there are 2 ways to set the network protocol.

  1. Use the protocol prefix:

    Server=tcp:myserver
    Server=np:myserver

    tcp: prefix means use tcp protocol. np: prefix means use named pipes protocol. Just stick this in front of the server name you are connecting to.

  2. Second ways is to set the Network keyword in the connection string:

    Network=dbmssocn
    Network=dbnmpntw

I prefer the protocol prefix because I can never remember these network type abbreviations.

agf
  • 171,228
  • 44
  • 289
  • 238
Matt Neerincx
  • 51
  • 1
  • 2
1

Maybe this will get you going:

4) If you are using SQL Native Client ODBC/OLEDB provider({SQL Native Client} or SQLNCLI), go to SQL Configuration Manager, click client protocols, make sure NP and TCP are both enabled. Right click properties of NP, make sure client is using the same pipe name as server for connection.

The node is actually 'SQL Native Client Configuration', You can change priority and disable from there.

from http://blogs.msdn.com/sql_protocols/archive/2007/03/31/named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server.aspx

Sam
  • 7,543
  • 7
  • 48
  • 62
  • I was aware of your information before posting my question, but having another person confirm it is good. However, configuring the 'SQL Native Client Configuration' on the server, is still missing something. In my tests it only works for some of the client computers. Am I missing something? – Clinemi Dec 08 '08 at 16:32
  • You are making this setting change on client machines, correct? You mention server - the only thing you need to do is enable named pipes in the surface area config. – Sam Dec 10 '08 at 00:33
0

  1. Not sure about Named Pipes being any sort of an answer for ODBC connection breaking. Without first identifying the source of the problem, I wouldn't count on named pipes. I might be wrong though. Can you see any error that may explain this problem? This may be network related.

  2. Otherwise, I think it should be quite easy to configure this on the client machine: just run cliconfg (SQL Server Client Network Utility). in the General tab make sure the Named Pipes protocol is enabled, then navigate to the Alias tab and simply create an alias for your server using Named Pipes.

Good luck.

adilei
  • 358
  • 3
  • 9
  • My understanding of cliconfg is that it is not intended to work with the SQL Native Client drivers - but only with previous versions of SQL Server ODBC drivers. Having said that, experience has taught me that if you have an incorrect setting in cliconfg it breaks sql native client connections. – Clinemi Jan 02 '09 at 18:36