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.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.
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.msc
Click 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]
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:
Any attempt generates a login security error like this:
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