Can't open connection to SQL server using named pipe
I'm trying to follow procedures from Microsoft to move our WSUS (version 3) database from the Windows Internal Database it's running on now to our new, fully-fledged SQL 2005 server. Unfortunately, I'm getting stuck on step 3, which involves detaching the WSUS database. The instructions tell me to use the following command:
sqlcmd -S np:\\.\pipe\MSSQL$MICROSOF
However, when I try this from a command prompt I get 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 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..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired
I'm not especially au fait with SQL server but I know a bit and I've followed lots of instructions on the Internet which I turned up while looking into this problem. Specifically:
I've installed the SQL Browser Service and ensure it's running.
I've made sure that named pipes (and TCP/IP) are enabled for the SQL server I'm trying to connect to by using the SQL Server 2005 Surface Area Configuration tool.
I've tried editing the sqlcmd command a bit, entering the server name in where the '.' appears
I've tried to connect using TCP/IP instead, with the same error.
I've tried the command while at the server, not just over Remote Desktop.
I've tried connecting to the database using SQL Server Management Studio but I get a similar error message relating to the server not accepting remote connections.
I don't think I'm doing anything particularly wrong but I'm stumped by this. I assume that things are complicated by the fact that this an Windows Internal Database and not a standard SQL database, and by the fact that it was installed automatically when WSUS 3 was installed.
If anyone can give me some straightforward things to try, taking into account my limited experience with SQL (WSUS I'm good with!), I'd be very grateful!
sqlcmd -S np:\\.\pipe\MSSQL$MICROSOF
However, when I try this from a command prompt I get 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 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..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired
I'm not especially au fait with SQL server but I know a bit and I've followed lots of instructions on the Internet which I turned up while looking into this problem. Specifically:
I've installed the SQL Browser Service and ensure it's running.
I've made sure that named pipes (and TCP/IP) are enabled for the SQL server I'm trying to connect to by using the SQL Server 2005 Surface Area Configuration tool.
I've tried editing the sqlcmd command a bit, entering the server name in where the '.' appears
I've tried to connect using TCP/IP instead, with the same error.
I've tried the command while at the server, not just over Remote Desktop.
I've tried connecting to the database using SQL Server Management Studio but I get a similar error message relating to the server not accepting remote connections.
I don't think I'm doing anything particularly wrong but I'm stumped by this. I assume that things are complicated by the fact that this an Windows Internal Database and not a standard SQL database, and by the fact that it was installed automatically when WSUS 3 was installed.
If anyone can give me some straightforward things to try, taking into account my limited experience with SQL (WSUS I'm good with!), I'd be very grateful!
ASKER
Thanks for the link, that's a mine of information. I'd already read and tried most of the suggestions made there and I've now tried the rest but still no success. In regard to your suggestion about TCP/IP, I'm trying to connect using named pipes not TCP/IP, but in any case I've enabled the TCP/IP protocol in the Surface Area Configuration tool and set the port which the server is using to match that which the client is using. There is no firewall software running on that server.
This is so frustrating. It's almost as if I'm entering the wrong database name in my pipe command but I've checked it dozens of times, and when I try to connect in Server Management Studio the server I'm trying to connect to is listed when I try to browse in the login box. I just can't connect to it!
This is so frustrating. It's almost as if I'm entering the wrong database name in my pipe command but I've checked it dozens of times, and when I try to connect in Server Management Studio the server I'm trying to connect to is listed when I try to browse in the login box. I just can't connect to it!
I also had this problem once, when i acccidently turned off one of the services, so make sure all the services like SQL Server Agent are all on.
ASKER
I'd already checked that all the services were running, and they are. The browser service wasn't present to begin with but I installed more SQL features from the SQL Server 2005 disc and then enabled this service too. I'm still not having any joy with this at all. :(
Is this the article you're referring to when you say you followed the microsoft directions:
http://technet.microsoft.com/en-us/library/cc708558.aspx
http://technet.microsoft.com/en-us/library/cc708558.aspx
"SQL Server 2005 database requirements
WSUS requires SQL Server 2005 with Service Pack 1. If you use the full version of SQL Server, the database administrator should first verify that the nested triggers option is turned on before setting up the WSUS database.
You cannot use SQL authentication. WSUS supports Windows authentication only. WSUS setup creates a database named SUSDB. "
--Do you have SP1 installed on SQL Server 2005?
WSUS requires SQL Server 2005 with Service Pack 1. If you use the full version of SQL Server, the database administrator should first verify that the nested triggers option is turned on before setting up the WSUS database.
You cannot use SQL authentication. WSUS supports Windows authentication only. WSUS setup creates a database named SUSDB. "
--Do you have SP1 installed on SQL Server 2005?
Are you trying to move the database to a different physical server, aka a remote server from where your WSUS is currently installed.
If so, look in the microsoft link at this area:
title: Migrating the WSUS database from a Windows Internal Database instance to a SQL Server 2005 instance on a remote server
If so, look in the microsoft link at this area:
title: Migrating the WSUS database from a Windows Internal Database instance to a SQL Server 2005 instance on a remote server
ASKER
Yes, those are the instructions I'm following from Microsoft to migrate the database and yes, I'm migrating the WSUS database from one box to another, a physically different server, so I'm following the instructions under the heading you mentioned in your last post about moving to a remote server. The instructions don't say that SP1 for SQL Server 2005 needs to be installed on the WSUS box, only on the box where the database is being moved to, but I suppose it can't do any harm.
Btw, earlier we were looking for how to enable Named Pipes on the SQL Server 2005. You need to enable named pipes for the WSUS database. I dont know anything about WSUS. Make sure everything associated with WSUS (services, IIS, etc..) are working
ASKER
I know that there has been no activity on this thread for a while but I was still hopeful that a successful solution would be forthcoming at some point.
OK ... I've read back over this thread, but may have missed something.
So you have a checklist you're following to move the WSUS DB from one server to another, right? And you're getting stuck at the Detach step, right?
Would you try installing SQL Management Studio (possibly the Express edition) on the box that currently has the DB? Doing that, we should be able to walk you through graphically detaching the database.
So you have a checklist you're following to move the WSUS DB from one server to another, right? And you're getting stuck at the Detach step, right?
Would you try installing SQL Management Studio (possibly the Express edition) on the box that currently has the DB? Doing that, we should be able to walk you through graphically detaching the database.
ASKER
I am indeed stuck at the point of detaching the database. I've already installed SQL Manager Studio but haven't had any more joy using this than I have the command prompt.
OK, in Management Studio, you drill down to the DB, right-click it, and choose Detach.
What happens?
What happens?
ASKER
I get the error message as per the attached image, advising that database won't allow remote connections, which is the same as what I get when I try this from the command prompt. I've tried everything I can think of and plenty of suggestions from the Internet (see my original post for details) to get around this but no joy yet.
msg.JPG
msg.JPG
Do you have SQL Server Configuration Manager? does it show Named Pipes enabled?
SQLConfigManager.GIF
SQLConfigManager.GIF
And SQL Management Studio is on the machine hosting the WSUS DB? I'm wondering since the message refers to remote connections ...
ASKER
Yes, it definitely is. That's what I don't understand, the connection isn't remote at all. Out of interest I did try connecting from SQL Management Studio on another box too but, unsurprisingly, got the same error.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Sorry Daniel, I'm still interested, I've just been on holiday.
Your idea is interesting, I'll give it a try if you're sure it's safe to do it this way rather than detaching the database properly.
Your idea is interesting, I'll give it a try if you're sure it's safe to do it this way rather than detaching the database properly.
Should be safe ... if it doesn't work, you can always restart the Sql Server process and be back where you are right now. The service turning off & back on is no different from what happens during a reboot.
ASKER
Thanks for all your help Daniel. In the end I did as you suggested, stopped the SQL service and just moved the files manually. I was then able to attach them to my full SQL Server instance.
After that it didn't help that the instructions from Microsoft for moving the WSUS database are actually incorrect, as is well-documented on the Internet, but once I'd figured out adding the correct permissions to the moved database it all worked.
Thanks again.
After that it didn't help that the instructions from Microsoft for moving the WSUS database are actually incorrect, as is well-documented on the Internet, but once I'd figured out adding the correct permissions to the moved database it all worked.
Thanks again.
http://blogs.msdn.com/sql_protocols/archive/2007/03/31/named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server.aspx
One thing i noticed from this link is "enabling remote connections." Another thing, if TCP/IP isn't connecting make sure port "1433" is an exception in Windows Firewall.