MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Fix SQL Server 2008 Client

with 65 comments

I finally got back to my Microsoft SQL Server 2008 Express installation for a bit of comparison documentation in the sqlcmd.exe (the client tool peer to sqlplus and mysql). After all the energy to install it, I found it didn’t work.

Update for SQL Server 2012 – it’s still broken the same way and this fix works.

When I tried to connect with:

C:\>sqlcmd.exe

It raised the following error:

HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific
error has occurred while establishing a connection to SQL Server. Server is not found or not
accessible. Check if instance name is correct and if SQL Server is configured to allow remote
connections. For more information see SQL Server Books Online..
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

A bit of poking around yielded an answer on Chris Rasmussen’s blog and a bit more about .NET pipes on Jesse Johnston’s blog. It appears Microsoft SQL Server 2008 Express edition ships and installs with their client tool effectively shutoff.

The client executable uses a pipe to communicate to the server. You need to change the pipe from this default configuration:

\\.\pipe\MSSQL$SQLEXPRESS\sql\query

To this working version

\\.\pipe\sql\query

Then, you need to enable it and restart the service. Here are the step-by-step instructions, so you don’t have to poke around.

You’ll need to launch the SQL Server Configuration Manager to make these changes. You’ll find it by navigating to Start and then open the Microsoft SQL Server 2008 folder. You’ll then select Configuration Tools and launch SQL Server Configuration Manager, as shown in the screen shot.

startmenu

Launching that provides you the following screen shot:

netpipeconfiguration

Open the SQL Server Network Configuration detail and choose Protocols for SQLEXPRESS. Double click on the Named Pipes protocol name.

netpipedefault

You then enable the protocol and change the Pipe Name as noted above and shown in the screen shot below:

netpipefixed

Click the Apply button, and the only remaining step requires you to restart the service. The easiest way (thanks to Griffth) is to click on SQL Server Services in the left dialog, then right click the SQL Server (SQLEXPRESS) service and choose Restart.

sqlrestarteasy1

An alternative would be to open a command line window (cmd.exe) and launch the services console with the following command:

C:\> services.msc

Click on SQL Server (SQLEXPRESS), then click the Restart the services link in the middle column, as shown.

sqlserverservice

Now you should be able to launch the SQL Server 2008 Express command line console. You can get some basic help with the tool by doing the following:

C:\Data>sqlcmd -?
Microsoft (R) SQL Server Command Line Tool
Version 10.0.1600.22 NT INTEL X86
Copyright (c) Microsoft Corporation.  All rights reserved.
 
usage: Sqlcmd            [-U login id]          [-P password]
  [-S server]            [-H hostname]          [-E trusted connection]
  [-d use database name] [-l login timeout]     [-t query timeout]
  [-h headers]           [-s colseparator]      [-w screen width]
  [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
  [-c cmdend]            [-L[c] list servers[clean output]]
  [-q "cmdline query"]   [-Q "cmdline query" and exit]
  [-m errorlevel]        [-V severitylevel]     [-W remove trailing spaces]
  [-u unicode output]    [-r[0|1] msgs to stderr]
  [-i inputfile]         [-o outputfile]        [-z new password]
  [-f <codepage> | i:<codepage>[,o:<codepage>]] [-Z new password and exit]
  [-k[1|2] remove[replace] control characters]
  [-y variable length type display width]
  [-Y fixed length type display width]
  [-p[1] print statistics[colon format]]
  [-R use client regional setting]
  [-b On error batch abort]
  [-v var = "value"...]  [-A dedicated admin connection]
  [-X[1] disable commands, startup script, enviroment variables [and exit]]
  [-x disable variable substitution]
  [-? show syntax summary]

After you’ve configured this, you can schedule routine operations, like the daily submission process in this external post.

Written by maclochlainn

May 16th, 2009 at 12:26 am

Posted in sql,SQL Server 2008

65 Responses to 'Fix SQL Server 2008 Client'

Subscribe to comments with RSS or TrackBack to 'Fix SQL Server 2008 Client'.

  1. Good explanation.

    Just a bit of additional information, you can restart the SQL Server (SQLEXPRESS) service from within the Configuration Manager. Pick the SQL Server Services item in the left panel then right click the SQL Server (SQLEXPRESS) item in the right panel. A context menu will offer the option to restart the service along with other useful actions.

    Again, good article. Thanks for taking the time to explain what was going on.

    griffith

    16 May 09 at 5:41 pm

  2. @Griffith thanks for the comment. I updated the article because that’s clearly easier. ;-)

    Another quick update for those new to Microsoft SQL Server 2008 Express. If you configured the server for NT-AUTHENTICATION during product installation, you can’t login as a user to a database like this:

    C:\> sqlcmd -U student -P student

    Any attempt generates a login security error like this:

    Msg 18456, Level 14, State 1, Server WIN-BIX95J1IL3W\SQLEXPRESS, Line 1
    Login failed for user 'student'.

    You can review the log files using the Microsoft SQL Server Management Studio. Launch the application, then navigate to Management and SQL Server Logs, where you’ll find a more descriptive message:

    Login failed for user 'student'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: <named pipe>]

    maclochlainn

    16 May 09 at 7:22 pm

  3. Thanks for this post — helped solve problems with my scheduled backups which rely on sqlcmd :)

    Mike

    19 May 09 at 12:57 pm

  4. Thank you so much! Been banging my head against a brick wall for a few hours till I found this…

    RIchard

    30 Jun 09 at 10:51 am

  5. thank you. i’m still banging my head ….

    lilany

    21 Jul 09 at 8:04 am

  6. Does it work now or did you run into an additional headache?

    maclochlainn

    21 Jul 09 at 9:34 am

  7. I am not able run command client. When i try C:\>sqlcmd.exe in cmd prompt, it gives message like ‘sqlcmd.exe’ is not recognized as an internal or external command,operable program or batch file.

    Ramesh

    23 Jul 09 at 5:54 pm

  8. Check your path environment variable to see if it’s there:

    C:\> echo %PATH%

    You can add it to your system environment variables if it’s not there.

    maclochlainn

    23 Jul 09 at 9:09 pm

  9. Thanks Mac, it is there.

    Ramesh

    24 Jul 09 at 7:47 am

  10. Works! Thanks for posting the fix.

    cobiacomm

    18 Aug 09 at 7:20 am

  11. Nice to know posting solutions sometimes helps people isn’t it? ;)

    Chris Rasmussen

    27 Aug 09 at 9:51 am

  12. Thank You! So simple.
    I tried a similar solution earlier but the key is obviously in removing that MSSQLSQLEXPRESS string.

    Catherine

    22 Feb 10 at 11:13 pm

  13. Hi,

    How can i resolve the problem of not being able to login via:

    sqlcmd -U username ?

    Do i need to change authentication? And if so how can i do that?

    Aija

    24 Mar 10 at 8:34 am

  14. Wow – thank you so much for the outstanding fix!!
    It worked like a charm.

    Dana

    11 May 10 at 11:10 am

  15. Thanks for this. I was about to throw in the towel and go for MySQL. I can’t believe MS would put together such a hack job on releasing SQL 2008 express and SSMS. I can’t see how anyone could have installed these two products without consulting at least 5 or more help sites like this one.

    I can’t believe this was easy even for a seasoned database manager (which is not me!).

    Eugene

    15 Jun 10 at 11:42 am

  16. Thanks again for sharing this! I was trying to convert the WSUS internal database over to SQL 2008 using this article:
    http://technet.microsoft.com/en-us/library/cc708558(WS.10).aspx

    and had to get sqlcmd working first. Everything seemed to work once I got sqlcmd working.

    Josh

    18 Jan 11 at 11:32 am

  17. There is a free tool “SQLS*Plus” which is an SQL*Plus for SQL Server. Works with SQL Server 2000/2005 and 2008

    Very flexible with data formatting (set lines size, pagesize, etc), variables (&, &&), spool, etc – light years better than isql, osql or sqlcmd

    I downloaded SQLS*Plus from http://www.memfix.com

    svdba

    3 Feb 11 at 8:53 pm

  18. Hi,
    Thank Thank you very much !!!!!
    You make my day. I tried lot many thing from other post. almost everything – 1) Protocol 2) Firewall etc.. but nothing works.

    In my case, for sql server 2008 I just changed Named Pipe Name to “\\.\pipe\sql\query” and restart service and everything works.

    Guys ! This is very important if you are using Sql Server 2008. I tried everything from google.

    Thanks again.

    Vraj

    19 Mar 11 at 9:57 am

  19. Does anyone know if this fix also applies to 2005′s Express edition?

    AS

    29 Apr 11 at 4:24 pm

  20. I tried this and still cannot connect via sqlcmd. Any other ideas?

    thank you

    Howard

    28 May 11 at 2:45 pm

  21. i am unable to connect my database created in sqlexpress to a crystal report using visual studio 2010. when i create a odbc connection it only shows the default 4 databases, i copied my dbase to the sqlexpress folder but in vain..plz help

    Faiza

    1 Jun 11 at 11:00 am

  22. \\.\pipe\MSSQL$SQLEXPRESS\sql\queryTo this working version
    \\.\pipe\sql\query

    Thank you for the above Tip. Finally works OK.

    douwa_ishin

    5 Jul 11 at 12:26 am

  23. The \\.Pipe\sql\query saved my day, my week

    Thanks so much, I really broke down

    KLR

    22 Jul 11 at 10:56 am

  24. thank u very much it worked

    derrick

    23 Aug 11 at 12:38 pm

  25. Is it not stupid that we have to change named pipe string by hand, after regular clean instalation of sql express 2008. They talk about Clean environment? Just calculate time*W of pc’s * number of users for solving that issue.

    igor

    4 Sep 11 at 3:56 am

  26. Thank you for the fix! I have spent days on this. And “thanks” to Microsoft for delivering a product that is configured in such a way that it doesn’t work!

    DW

    11 Oct 11 at 12:28 pm

  27. Much appreciated. Fixed my problem. Thanks for taking the time to post this

    Jude

    19 Oct 11 at 2:17 am

  28. Thank you very much! I couldn’t execute a simple query from the command line until I found this. Sixty seconds later and I’m in business. Why I have to change a default parameter on a clean install of SQL Express 2008 is beyond me.

    I don’t know what we’d do without Google and people like you.

    Nancy

    26 Oct 11 at 6:31 pm

  29. You are my savior. Been struggling for several minutes to make it work. Now, its working. Thanks a lot.

    Sathya Rajan

    15 Dec 11 at 12:58 pm

  30. Thanks, this really helped.

    Matthew

    Matthew

    15 Jan 12 at 2:54 am

  31. Thanks a lot, it is a very useful information and my sqcmd is working fine now.

    Parvathy

    1 Feb 12 at 3:43 am

  32. I needed the Northwind db & pubs for an instructional video but couldn’t install them. Your fix got me going. The instructions were clear and conscise – thanks for taking the time to post.

    Edward

    4 Feb 12 at 5:01 pm

  33. You just made my day.
    tytytytyty!

    Kyleiscoding

    16 Feb 12 at 5:09 am

  34. Short and simple – Thanks mate

    Shane

    4 Apr 12 at 1:22 am

  35. Like all the others on here, I have to add my thanks, I’ve been banging my hear for 2 freakin hours on this! Not a sql server guru, just didn’t make sense why it wouldn’t work. Why the heck do they install it like that????

    Dave Gilliam

    20 Apr 12 at 3:21 pm

  36. Thank you Maclochlainn, I was, kinda stranded with this problem and you Blog solved the Issue. It was very helpful.I appreciate your time and effort to help others. Great job Buddy. Keep it up. A double thumbs-up.

    Dinkar Reddy

    9 May 12 at 10:03 pm

  37. Thank you very much , I looked everywhere but couldn’t find any solution. but you solved. You are a genius!

    Rashini

    16 May 12 at 9:38 am

  38. Extremely helpful. This was bang on!

    Leo R.

    6 Jul 12 at 7:19 am

  39. 3 years from original post and this is an awesome fix!
    I am an old-timer from Sybase/MS days
    Win2008 SQLS2008 Express named pipes fix worked!
    Many thanks.
    Zabit

    Zabit Greenan

    10 Jul 12 at 4:04 pm

  40. Made my day!! Thanks a ton!!

    Venky

    1 Aug 12 at 6:05 am

  41. OMG, thank you!

    Mark

    5 Aug 12 at 10:14 am

  42. That fixed my problem! Thanks!

    BT

    13 Aug 12 at 11:05 am

  43. [...] Microsoft SQL Server 2012 Express Edition [...]

  44. [...] verify that the sqlcmd command is working properly [...]

  45. Awesommme , thanks a load

    Abhishek

    13 Sep 12 at 7:50 am

  46. Esto me ayudó bastante, después de tanto buscar esto solucionó mi problema :D!!!

    Finally it’s working thanks a lot.
    NOTE:
    Enviroment

    • SQL Server 2008 R2
    • ASP Web 2.0
    • both on same server

      i’m using a classic ASP web, with a connection string like this:

      ''Conexion to my Data Base
      Set Conn = Server.CreateObject("ADODB.Connection") Conn.open ("Driver={SQL Server Native Client 10.0};Server=127.0.0.1;Database=DBname;UID=Username;PWD=PasswordDB;Trusted_Connection=NO;")

      and then i followed the steps of this post and works, thanks

    César Ríos

    13 Sep 12 at 2:59 pm

  47. Thank you so much your solution works fine for me

    hadi

    23 Sep 12 at 1:36 am

  48. Everybody is welcome naturally but I’d like to know why Microsoft continues to disable the pipe because the same problem exists in the SQL Server 2012 version.

    maclochlainn

    23 Sep 12 at 8:43 am

  49. Brilliant stuff, you just saved me an afternoon!

    Edward Spencer

    3 Dec 12 at 10:20 am

  50. This helped. Immensely. Thank you for posting this

    Lara

    12 Dec 12 at 10:18 am

  51. Thank You Sooooooooooooo Very Much!!! You seriously saved my day and my hero for today!! Thank you loads! After going through so many links, yours is actually what helped me out!

    Dee

    27 Apr 13 at 7:35 am

  52. thanks- great simple article! good work!

    Harvy

    24 May 13 at 9:24 am

  53. This is exactly what I was looking for. Thank You!!!

    Timothy

    29 May 13 at 5:50 am

  54. You saved my night!! Thanks

    Stan

    5 Jun 13 at 1:39 am

  55. Thanks champ! This really helped!!

    Jake

    2 Jul 13 at 9:53 pm

  56. Thank you..
    When I tried sqlcmd in command prompt, “sqlcmd is not recognized as an internal or external command..” so I paste the sqlcmd.exe in that location. It’s OK to use sqlcmd.. But the problem is it’s not connected to SQLEXPRESS. It connet to server.. What should I do.. I tried many way.. Please answer me..
    Thank you all, good luck.

    Myat Thet Htwe

    13 Aug 13 at 5:16 am

  57. You’re a Life Saver.

    Congrats ;-)

    OSAT

    8 Oct 13 at 8:22 am

  58. MAn you’re my hero!

    ThankU

    15 Oct 13 at 2:55 am

  59. SQLCMD -E -S servername -Q “BACKUP DATABASE databasename TO DISK=”d:\adw.bak””

    rajesh

    17 Oct 13 at 4:21 am

  60. for restoring the database we can use

    SQLCMD -E -S servername -Q “RESTORE DATABASE databasename FROM DISK=”d:\adw.bak””

    rajesh

    17 Oct 13 at 4:22 am

  61. Thanks a lot. It was exactly what i was looking for. :) :)

    Eshant Sahu

    24 Feb 14 at 3:29 am

  62. Hi, Can I ask a question my sqlcmd script wont let me write to a folder on the desktop of my win XP PC any suggestions.

    The script work on another win xp with no problem!

    Divjoy

    26 Mar 14 at 2:37 pm

  63. Divjoy, It’s probably an issue with privileges. Check the directory privileges.

    maclochlainn

    27 Mar 14 at 12:20 am

  64. please I need you help

    how to change the name pipes from SMO (server management objet)?

    or

    how to change the name pipes from command of TSQL?

    Wilfredo

    29 Apr 14 at 8:46 am

  65. Wilfredo,

    I’m not aware of a way to change them from inside the instance, which means through you use PowerShell. Check this page for information.

    maclochlainn

    30 Apr 14 at 9:15 pm

Leave a Reply