none
Named Pipes Provider: Could not open a connection to SQL Server [2]

    質問

  • Hi

    I'm a complete sql/asp.net newbie and want to try this tutorial:
    http://beta.asp.net/GuidedTour/

    First I installed:
    Visual Web Developer 2005 Express Edition Beta 2

    systemsettings\software shows the "sql server 2005 express edition ctp (sqlexpress)" installed

    mmc\service shows me the SQL Server (SQLExpress) is running

    Following the guided tour I use the commandline, type cmd, and type in the commandbox:
     
    "C:\Program Files\Microsoft SQL Server\90\Tools\binn\SQLCMD90" -S "localhost\SqlExpress"

    Instead of localhost I also tried computername.smallbusiness.local (thats my fully domain name).

    However I always receive the error:

    Named Pipes Provider: Could not open a connection to SQL Server [2]

    Thank you very much for all your help

    2005年8月2日 17:23

回答

  • I was trying to install the Northwind database into SQLServer 2005 express server when I got the error,

    'Named Pipes Provider: Could not open a connection to SQL Server [2]'.

     

    This solution worked for me

     

      1)    Start SQL Server Configuration Manager

     

                Right-click on the sql server name that you created

                     Select 'Properties'

     

                     On the 'Log On' tab, set the 'Log on as: '

     

                            Built in account, Local System

     

                     Restart Service and close Sql Server Config Mgr

     

     

       2)     Go to the DOS command prompt

     

                 CD <directory where .sql file to load databases resides>

     

                 sqlcmd -E -S <computername\SQL Servername> -i instnwnd.sql

     

     

    Hope this helps someone else.

     

    "T"

     

      

     

     

              

    2007年5月8日 2:31

すべての返信

  • I have the same error. Anybody has a solution for this? I did the standard install too!

    2005年8月23日 18:47
  • I get the same (I did enable the Named Pipes provider in Configuration Manager, but it made no difference) only if I omit the server name completely; if I use the sqlcmd -S server\instance format that everywhere tells you to, I get:

    'Sqlcmd: Error: Internal error at OpenDataSourceDirect (Reason: Unspecified error).
    HResult 0x57, Level 16, State 1
    SQL Network Interfaces: Connection string is not valid [87].
    Sqlcmd: Error: Microsoft SQL Native Client : Client unable to establish connection.
    Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.'
     
    If I try to connect through VC# Express I get the message:

    'Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.' (but here it's using the .Net provider).

    Please, please, please can someone tell me what I'm doing wrong. I have spent days trying to get this to work. I even uninstalled and re-installed the whole of SQL Express, VC# Express and .Net, which was no easy task!!. I am DESPERATE!
    2005年8月24日 18:48
  •  LesleyW wrote:
    I get the same


    I have actually just noticed that the message I get is:

    Named Pipes Provider: Could not open a connection to SQL Server [53], rather than [2], so I guess it's not quite the same error...
    2005年8月24日 18:56
  • Get Computer Name from System Properties->Full computer Name.
    after this

    from command prompt run >sqlcmd -S<computerName>\SQLEXPRESS -E

    This worked for me, hope it does for you.
    2005年9月1日 8:43
  • Having uninstalled SQL Express, VC# and .Net AGAIN. I discovered that if I let VC# install SQL Express, rather than me doing it first (as I had done previously), then I can connect no problem. Don't know why...
    • 回答の候補に設定 Sblackfo 2010年3月4日 18:39
    2005年9月7日 12:56
  • I was trying to install the Northwind database into SQLServer 2005 express server when I got the error,

    'Named Pipes Provider: Could not open a connection to SQL Server [2]'.

     

    This solution worked for me

     

      1)    Start SQL Server Configuration Manager

     

                Right-click on the sql server name that you created

                     Select 'Properties'

     

                     On the 'Log On' tab, set the 'Log on as: '

     

                            Built in account, Local System

     

                     Restart Service and close Sql Server Config Mgr

     

     

       2)     Go to the DOS command prompt

     

                 CD <directory where .sql file to load databases resides>

     

                 sqlcmd -E -S <computername\SQL Servername> -i instnwnd.sql

     

     

    Hope this helps someone else.

     

    "T"

     

      

     

     

              

    2007年5月8日 2:31
  • guys!!! you got to add sqlsvr to the exception in windows firewall.. why?? coz you have installed a server and communication with the port to the server is blocked by the firewall.

    2007年8月14日 17:14
  • 2007年8月14日 19:13
    モデレータ
  • I got the same error when I tried to create the Northwind example database.

    " Named Pipes Provider: Could not open a connection to SQL Server [2]"

     

    osql -E -S computer\sqlserver -i instnwnd.sql

     

    First I enabled the Named Pipes Protocol in the Server Network Configuration using the SQL Server Configuration Manager but I got accees denied.

    After some tries I check the default user privileges and I noticed that my user didn't have access to the server so I granted it the access using the SQL Server Surface Area Configuration.

     

    I hope this will be helpful

     

    2008年4月25日 18:02
  • I ran into this issue too.  I think the parameters are case sensitive.

     

    2008年8月15日 3:58
  • I also have the same doubt sharps...

    microsoft crm 3.0 customization
    2008年8月18日 9:43
  • Yes its works

    SQLCMD -S abc\xyz -E

    ---> abc is full computer name (or virtual name in case of cluster)

    --->xyz is instance name (in case of named instance)

    2009年1月27日 11:59
  • OK for people who still don't have this problem solved ... This is the ultimate solution


    When trying to backup to your database ensure your ports are correctly set and set in the configuration manager...

    Eg using port 1433
    sqlcmd -S <ServerName>\<InstanceName>,<Port(1433)> -E -i "<Scriptlocation"

    This way you ensure your command uses the Tcp/IP protocol Named Pipes aren't favorable in many cases... so it's best to disable them

    Regards
    2009年10月20日 20:16
  • [SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2].
    [SQL Native Client]Login timeout expired
    [SQL Native Client]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.

     

    ** The above error would also occur when there is no SQL Server hosted on the server. **


    Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!
    2010年4月6日 6:24
  • I’ve just installed SQL 2008 R2 and I was having the same issues.

    I follow all the tips without any luck.

    The solution in my case was very simple: instead of connecting to “server\instance_name” I connected to “server”

    I suppose during installation process I didn’t create any instances. I was misguided with the name “SQL Server (MSSQLSERVER)” in SQL Configuration Manager. There was no MSSQLSERVER instance.

     

    ---

     

    Jorge Moura [MVP]

    http://ojmoura.wordpress.com/

    2010年10月21日 9:12
  • Typically I have found problems with connection strings as one of a few things:

     

    1. Your instance is named different than the default.  I labeled mine BrettSQLServer instead of the regular instance.

    2. Your default storage of the your database has a user the system does not like and won't give it access.

    3. You have ports blocked that SQL Server is trying to access.

     

    1. Your instance should show up under 'SQL Server Configuration Manager'.  Start>MS SQL Server 2008 > Configuration Tools> SQL Server Configuration Manager.  You should see a left view panel where you can click 'SQL Server Services'.  This will show on the right all of your SQL services that are running.  The most important one is SQL Server({Your SQL Server Name}).  You will see column headers's displaying: "Name, State, Start Mode, Log on as,  ProcessID" by default.  If you don't see a version of this you may have no installed SQL server properly and gotten SQL management studio but not the server.  If you have the server and it's off or not running that is another problem.  If your "LOG On AS" is a service account is "NT ...." or something besides "Local System" that may be a problem.  Without getting into the symantecs of protection suffice to say you log in with less than system, yes you are more secure but some built in functions for connections may not work.

    2. Going off of rights of your 'Log On As' I WOULD USE 'System' NOT "NT Authority....".  The reason is that you can access less things with 'NT Authority....' but yes you are more secure for that reason.  If you are just trying to connect at home to test non secure things I don't see the harm.  If you are setting up a database for a multinational corporation than, Yes I would use a more secure account.  If you need to change it while still in SQL Server Confi Manager: Right Click> SQL Server(Your Version) > On 'Log On' tab > Choose drop down for 'Built-in Account' select 'Local System'.  As extra steps make sure by some strange reason your database locales have this user by default(They should).  MS likes to install database as default to: C:\Program Files\Microsoft SQL Server\MSSQL10.{instanceName}\MSSQL.  If you go to this location by going: right click>Properties>Security Tab .. and you fail to see your user that SQL server is defined as you may have lots of issues.

    3. Turn off Windows Firewall during SQL installation.  If you are scared of being attacked during this down window, disconnect your NIC and reconnect later.  I have not added a port manually in a while but it is simply easier to just turn off your firewall during install.  Having your TCP/IP connection from SQL Server blocked is kind of bad if you need to connect to it that way later.

     

    Problems most commonly encountered with SQL Server by users I have seen and I myself encountered a lot with full blown SQL Server Enterprise as well as Express versions of SQL:

    Connection string DO not work for BIDS, ADO.net, Linq, etc...

    Once you follow steps above just go {ComputerName}\{SQL Server Instance} as your connection name.   That mostly always works.  As a preliminary test you should install SQL Management Studio first before doing connection strings for a basic test that it can access your server.  When you first boot it up and click 'New Database Engine Query' it will display Server Type: "Database Engine" and a blank field or autopopulated field for 'Servername'.  The {ComputerName}\{SQL Server Instance} almost always works.  If it works and you can connect, you know that you have access, if you don't start there first before getting into connection strings through .NET languages.   I suggest just adding an easy alias, my local SQL box is simply called 'Brett'.  Go again back to SQL Server Configuration Manager.  On the left side click 'SQL Native Client ...' > 'Aliases' > New > type in whatever you want.  Go back to the left pane click on 'SQL Server Services' ensure the service 'SQL Server Browser' is running and set to run automatic.  Have fun accessing SQL Server with your alias.  

    Help I can't get FileStream to work and I ran 'spconfigure' in SQL and it still doesn't work.

    Kind of annoying that more sites don't list that it is entirely in the Configuration manager and has zilch to do with setting the option if the config has it off.  I am not a master of the sp_configure TSQL access method but I know during installs turning it on fails to REALLY turn it on.  Once again go to SQL Config Manger > SQL Server Services > Right click your instance > Select FILESTREAM tab > enable both check boxes.  Restart SQL Server.  Install Adventureworks 2008 and whatever else failed due to filestream access levels.


    Medium to Advanced on some things, but always wanting to know more
    2010年11月12日 22:02
  • Easiest solution of all....

    Ensure you name your instance in your connection.  In my case I was driving myself mad using different versions of localhost, machine name, etc. and forgot that most important part of my connection being "localhost\SQLEXPRESS".  I'm not using full blown SQL, so that was the simple "fix" for me.  User error, most typically, is the reason we have these issues.

    • 回答の候補に設定 Teena24 2011年7月21日 12:47
    2011年2月7日 17:52
  • I tried every suggestion, above, until the last one .... and it worked!
    2011年7月16日 21:58
  • This tip did the trick!

    Rather than typing 

    sqlcmd -S . -d <database>
    
    I used 
    sqlcmd -S .\sqlexpress -d <database>
    
    and that fixed the problem!  Thanks for the help!

     

    2011年7月25日 13:05
  • After trying all the day, I found ur solution thnx to u and the person asked the question.
    thanx all.
    2012年12月22日 5:43
  • i got the error of [64]

    im using primavera 6, can someone help me with this??

    thanks in advANCE

    trykedg

    2013年2月26日 5:41
  • I am a relative newbie here so please forgive any misstatements or transgressions.

    I *finally* solved this problem but I'm not entirely certain what I did.  So here is my configuration:

    SQLServer Services / SQL Server PropertieS:  Logon as built-in account, LocalSystem

    Protocols for SQLEXPRESS:  shared memory, named pipes and TCP/IP all ENABLED

    SQL Native Client 11.0 Configuration:  Client protocols:  Shared Memory, TCP/IP and Named Pipes all ENABLED

    Added System Environment Variable Name =SQLCMDSERVER  value = <machine name>\SQLEXPRESS

    Cheers

    Carl

    2013年7月9日 12:15
  • This solved my problem.  Thanks.

    SQLCMD command line flags ARE case sensitive.  Stupid, I know, but this is Microsoft we're talking about...

    sqlcmd -s server\instance -E   << DOESN'T WORK

    sqlcmd -S server\instance -E   << WORKS!

    2013年7月28日 5:50