Fix SQL Server 2008 Client
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.
When I tried to connect with:
C:\>sqlcmd.exeIt 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.
Launching that provides you the following screen shot:
Open the SQL Server Network Configuration detail and choose Protocols for SQLEXPRESS. Double click on the Named Pipes protocol name.
You then enable the protocol and change the Pipe Name as noted above and shown in the screen shot below:
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.
An alternative would be to open a command line window (cmd.exe) and launch the services console with the following command:
C:\> services.mscClick on SQL Server (SQLEXPRESS), then click the Restart the services link in the middle column, as shown.
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.
Subscribe via RSS
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
@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 studentAny 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:
maclochlainn
16 May 09 at 7:22 pm
Thanks for this post — helped solve problems with my scheduled backups which rely on sqlcmd
Mike
19 May 09 at 12:57 pm
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
thank you. i’m still banging my head ….
lilany
21 Jul 09 at 8:04 am
Does it work now or did you run into an additional headache?
maclochlainn
21 Jul 09 at 9:34 am
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
Check your path environment variable to see if it’s there:
You can add it to your system environment variables if it’s not there.
maclochlainn
23 Jul 09 at 9:09 pm
Thanks Mac, it is there.
Ramesh
24 Jul 09 at 7:47 am
Works! Thanks for posting the fix.
cobiacomm
18 Aug 09 at 7:20 am
Nice to know posting solutions sometimes helps people isn’t it?
Chris Rasmussen
27 Aug 09 at 9:51 am
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
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
Wow – thank you so much for the outstanding fix!!
It worked like a charm.
Dana
11 May 10 at 11:10 am
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
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
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
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
Does anyone know if this fix also applies to 2005′s Express edition?
AS
29 Apr 11 at 4:24 pm
I tried this and still cannot connect via sqlcmd. Any other ideas?
thank you
Howard
28 May 11 at 2:45 pm
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
\\.\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
The \\.Pipe\sql\query saved my day, my week
Thanks so much, I really broke down
KLR
22 Jul 11 at 10:56 am
thank u very much it worked
derrick
23 Aug 11 at 12:38 pm
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
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
Much appreciated. Fixed my problem. Thanks for taking the time to post this
Jude
19 Oct 11 at 2:17 am
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
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
Thanks, this really helped.
Matthew
Matthew
15 Jan 12 at 2:54 am
Thanks a lot, it is a very useful information and my sqcmd is working fine now.
Parvathy
1 Feb 12 at 3:43 am
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
You just made my day.
tytytytyty!
Kyleiscoding
16 Feb 12 at 5:09 am
Short and simple – Thanks mate
Shane
4 Apr 12 at 1:22 am
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
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
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