Could not open a connection to SQL Server [53]

I found this in an earlier thread and had a similar problem.
On Aug 25 2006, 4:29 pm, Erland Sommarskog <esq...@.sommarskog.se>
wrote:
> If you are really desperate, you can get the port number for the
> instance from SQL Configureation Manager, and then connect with:
>
> SQLCMD -S MACHINE,2056
>
> where you replace 2056 with the actual port number.
This was a very helpful post. Thanks. I am also using SQL Server
Express 2005 on an XP machine. I have Pipes, TCP and remote
connections enabled, with the SQL Browser service running and I'm
still confused why this is only working if I specify a port number.
I am still having trouble understanding why this works:
sqlcmd -S localhost,1295 -E -d users -i sql_server_deploy.sql
and this fails:
sqlcmd -S localhost\SQLEXPRESS -E -d users -i sql_server_deploy.sql
with error: Named Pipes Provider: Could not open a connection to SQL
Server [53].
I'm baffled why this doesn't work. My ERRORLOG indicates that TCP and
Pipes are open for business and has no errors. Any advice on what
area to investigate next is appreciated.> I am still having trouble understanding why this works:
> sqlcmd -S localhost,1295 -E -d users -i sql_server_deploy.sql
> and this fails:
> sqlcmd -S localhost\SQLEXPRESS -E -d users -i sql_server_deploy.sql
> with error: Named Pipes Provider: Could not open a connection to SQL
> Server [53].
It sounds like you installed SQL Server as a default instance, not a named
instance.
A|||On Sep 11, 1:36 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> > I am still having trouble understanding why this works:
> > sqlcmd -S localhost,1295 -E -d users -i sql_server_deploy.sql
> > and this fails:
> > sqlcmd -S localhost\SQLEXPRESS -E -d users -i sql_server_deploy.sql
> > with error: Named Pipes Provider: Could not open a connection to SQL
> > Server [53].
> It sounds like you installed SQL Server as a default instance, not a named
> instance.
> A
I am not experienced enough to discern that difference. How can use
sqlcmd without having to chased down the port number?|||Once you know the port number, why do you need to chase it down? Can't you
just create batch files with the port # embedded?
"cleveridea" <cleveridea.net@.gmail.com> wrote in message
news:1189539618.961963.303600@.e34g2000pro.googlegroups.com...
> On Sep 11, 1:36 pm, "Aaron Bertrand [SQL Server MVP]"
> <ten...@.dnartreb.noraa> wrote:
>> > I am still having trouble understanding why this works:
>> > sqlcmd -S localhost,1295 -E -d users -i sql_server_deploy.sql
>> > and this fails:
>> > sqlcmd -S localhost\SQLEXPRESS -E -d users -i sql_server_deploy.sql
>> > with error: Named Pipes Provider: Could not open a connection to SQL
>> > Server [53].
>> It sounds like you installed SQL Server as a default instance, not a
>> named
>> instance.
>> A
> I am not experienced enough to discern that difference. How can use
> sqlcmd without having to chased down the port number?
>|||On Sep 11, 2:42 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Once you know the port number, why do you need to chase it down? Can't you
> just create batch files with the port # embedded?
For me, on one particular host, for today that's fine - but what if I
want anyone (or anywhere) else to run my batch file? Embedding the
port number in my batch file is a kludge of the highest order.
(I'm assuming) SQL Server has to be configurable so that I don't need
it in my batch file. I've followed the directions on configuring my
server to the best of my ability and studied using the sqlcmd usage
docs - but it isn't jibing.
How can I get the port number out of my batch files that invoke
sqlcmd? What configuration choice am I neglecting?|||cleveridea wrote:
> On Sep 11, 2:42 pm, "Aaron Bertrand [SQL Server MVP]"
> <ten...@.dnartreb.noraa> wrote:
>> Once you know the port number, why do you need to chase it down? Can't you
>> just create batch files with the port # embedded?
> For me, on one particular host, for today that's fine - but what if I
> want anyone (or anywhere) else to run my batch file? Embedding the
> port number in my batch file is a kludge of the highest order.
> (I'm assuming) SQL Server has to be configurable so that I don't need
> it in my batch file. I've followed the directions on configuring my
> server to the best of my ability and studied using the sqlcmd usage
> docs - but it isn't jibing.
> How can I get the port number out of my batch files that invoke
> sqlcmd? What configuration choice am I neglecting?
>
I don't know about the SQL Server configuration, but it seems trivial to
me to make the batch file portable. Just pass in the port number, either
as a command-line argument, or as an environment variable. That should
be a workable solution, I 'd say.
Regards,
Ruud de Koter.|||On Sep 12, 3:17 am, Ruud de Koter <nob...@.internet.org> wrote:
> cleveridea wrote:
> > On Sep 11, 2:42 pm, "Aaron Bertrand [SQL Server MVP]"
> > <ten...@.dnartreb.noraa> wrote:
> >> Once you know the port number, why do you need to chase it down? Can't you
> >> just create batch files with the port # embedded?
> > For me, on one particular host, for today that's fine - but what if I
> > want anyone (or anywhere) else to run my batch file? Embedding the
> > port number in my batch file is a kludge of the highest order.
> > (I'm assuming) SQL Server has to be configurable so that I don't need
> > it in my batch file. I've followed the directions on configuring my
> > server to the best of my ability and studied using the sqlcmd usage
> > docs - but it isn't jibing.
> > How can I get the port number out of my batch files that invoke
> > sqlcmd? What configuration choice am I neglecting?
> I don't know about the SQL Server configuration, but it seems trivial to
> me to make the batch file portable. Just pass in the port number, either
> as a command-line argument, or as an environment variable. That should
> be a workable solution, I 'd say.
> Regards,
> Ruud de Koter.
Thanks but no thanks. Yet another kludgy workaround for something
that shouldn't need a workaround. Doesn't anyone know how to execute
a sql script against an completely vanilla SQL Server Express Edition
with TCP & Name Pipes enabled?
Either I've made some unfortunate installation/configuration choice
with SQL Server - that I don't yet understand how to fix, or SQL
Server Express is basically a kludge. I prefer to think it is the
former; any advice on how I might approach resolving the real issue is
appreciated.|||> I am not experienced enough to discern that difference. How can use
> sqlcmd without having to chased down the port number?
You shouldn't have to specify a port number. But you do need to know if this is a default or a named
instance. Check out what services you have, for instance. If you tell us whether it is a default
instance, or a named instance, we can possibly suggest further.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"cleveridea" <cleveridea.net@.gmail.com> wrote in message
news:1189539618.961963.303600@.e34g2000pro.googlegroups.com...
> On Sep 11, 1:36 pm, "Aaron Bertrand [SQL Server MVP]"
> <ten...@.dnartreb.noraa> wrote:
>> > I am still having trouble understanding why this works:
>> > sqlcmd -S localhost,1295 -E -d users -i sql_server_deploy.sql
>> > and this fails:
>> > sqlcmd -S localhost\SQLEXPRESS -E -d users -i sql_server_deploy.sql
>> > with error: Named Pipes Provider: Could not open a connection to SQL
>> > Server [53].
>> It sounds like you installed SQL Server as a default instance, not a named
>> instance.
>> A
> I am not experienced enough to discern that difference. How can use
> sqlcmd without having to chased down the port number?
>|||> For me, on one particular host, for today that's fine - but what if I
> want anyone (or anywhere) else to run my batch file? Embedding the
> port number in my batch file is a kludge of the highest order.
Why is that a kludge? It's pretty much the same thing as accepting username
/ password as parameters instead of hard-coding them.
A|||> Thanks but no thanks. Yet another kludgy workaround for something
> that shouldn't need a workaround. Doesn't anyone know how to execute
> a sql script against an completely vanilla SQL Server Express Edition
> with TCP & Name Pipes enabled?
Sorry, but to execute a SQL script against a SQL Server, you need to direct
the script at the location where SQL Server is running. If this is on
another machine, you need to specify the machine name. If this is a named
instance, you need to specify that as well. And if you are not running on
port 1433, then you will need to specify the port number. Sorry, this isn't
a kludge, this is just the way it is.
How much confidence do you have in the postal service delivering your letter
if you forget the zip code? What if you forget the city, too? Is a zip
code a kludge?|||On Sep 12, 1:40 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> > For me, on one particular host, for today that's fine - but what if I
> > want anyone (or anywhere) else to run my batch file? Embedding the
> > port number in my batch file is a kludge of the highest order.
> Why is that a kludge? It's pretty much the same thing as accepting username
> / password as parameters instead of hard-coding them.
> A
I appreciate your insight and I really am trying to solve my own
problem with the wonderful help here.
What is the kludge? I suppose involving the port isn't the kludge;
you are right on that. For me having to use the batch file in the
first place is the kludge.
What I really want is is a starter .sql script file, that the end user
executes via sqlcmd (using whatever credentials, connection
information that is appropriate for them). I want that starter file
to be able to chain and continue executing a handful of other
scripts. It isn't administratively practical to put everything into a
single .sql script.|||On Sep 12, 9:12 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> You shouldn't have to specify a port number. But you do need to know if this is a default or a named
> instance. Check out what services you have, for instance. If you tell us whether it is a default
> instance, or a named instance, we can possibly suggest further.
That is my understanding as well, but it isn't so in my environment,
as best I can tell. I will use your links and see if I can't figure
out where my configuration has gone wrong.|||The links are just my signature, so there's nothing about your particular problem in them.
If you have a default instance, then you shouldn't need SQL Server Browser and connecting to the
machine name only should do it. The client components will try to connect to 1433 when only machine
name is given, and the server listens to 1433 by default for a default instance.
If it is a named instance (suggested by your attempt to connect to localhost\SQLEXPRESS) then if you
want to connect using the instance name, you need to have SQL Server Brower running. When a client
connects using a machine *and* instance name, it will connect through 1434 to the SQL Server browser
and pass the instance name. Browser will find out the port number and pass it back to the client
connectivity layer and the client will use that port number to connect.
I do suggest that you verify the client config using cliconfg.exe and well as the server config
using SQL Server Configuration Manager.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"cleveridea" <cleveridea.net@.gmail.com> wrote in message
news:1189626800.659324.241700@.r34g2000hsd.googlegroups.com...
> On Sep 12, 9:12 am, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> You shouldn't have to specify a port number. But you do need to know if this is a default or a
>> named
>> instance. Check out what services you have, for instance. If you tell us whether it is a default
>> instance, or a named instance, we can possibly suggest further.
> That is my understanding as well, but it isn't so in my environment,
> as best I can tell. I will use your links and see if I can't figure
> out where my configuration has gone wrong.
>|||cleveridea wrote:
> On Sep 12, 3:17 am, Ruud de Koter <nob...@.internet.org> wrote:
>> cleveridea wrote:
>> On Sep 11, 2:42 pm, "Aaron Bertrand [SQL Server MVP]"
>> <ten...@.dnartreb.noraa> wrote:
>> Once you know the port number, why do you need to chase it down? Can't you
>> just create batch files with the port # embedded?
>> For me, on one particular host, for today that's fine - but what if I
>> want anyone (or anywhere) else to run my batch file? Embedding the
>> port number in my batch file is a kludge of the highest order.
>> (I'm assuming) SQL Server has to be configurable so that I don't need
>> it in my batch file. I've followed the directions on configuring my
>> server to the best of my ability and studied using the sqlcmd usage
>> docs - but it isn't jibing.
>> How can I get the port number out of my batch files that invoke
>> sqlcmd? What configuration choice am I neglecting?
>> I don't know about the SQL Server configuration, but it seems trivial to
>> me to make the batch file portable. Just pass in the port number, either
>> as a command-line argument, or as an environment variable. That should
>> be a workable solution, I 'd say.
>> Regards,
>> Ruud de Koter.
> Thanks but no thanks. Yet another kludgy workaround for something
> that shouldn't need a workaround. Doesn't anyone know how to execute
> a sql script against an completely vanilla SQL Server Express Edition
> with TCP & Name Pipes enabled?
> Either I've made some unfortunate installation/configuration choice
> with SQL Server - that I don't yet understand how to fix, or SQL
> Server Express is basically a kludge. I prefer to think it is the
> former; any advice on how I might approach resolving the real issue is
> appreciated.
>
Great idea, finding out how things work. And then again, occasionally, I
'll want to keep at bay those workmates/managers/customers that like to
see a result, even while I 'm trying to solve the real issue.
Regards,
Ruud de Koter|||On Sep 12, 3:35 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> If it is a named instance (suggested by your attempt to connect to localhost\SQLEXPRESS) then if you
> want to connect using the instance name, you need to have SQL Server Brower running. When a client
> connects using a machine *and* instance name, it will connect through 1434 to the SQL Server browser
> and pass the instance name. Browser will find out the port number and pass it back to the client
> connectivity layer and the client will use that port number to connect.
> I do suggest that you verify the client config using cliconfg.exe and well as the server config
> using SQL Server Configuration Manager.
There was no such cliconfig.exe that I could find; maybe that is
something not included with the Express edition.
When I installed 2005 Express, I don't remember any prompting to
choose between default or named instance mode. The SSCM doesn't
appear to reveal this distinction either. How does one determine this
distinction - or does the Express edition only have one mode?
If it is running in named instance mode, then the SQL Browser isn't
doing its job.
Well, I have determined that I can go into the SSCM and adjust the
properties for TCP/IP such that it now has a blank for TCP Dynamic
Ports and 1433 for TCP Ports. This solves my problem, essentially -
since I can now use sqlcmd in default instance mode, which is good
enough for me I guess.
The next host I install 2005 Express, I will pay more attention to the
choices offered in respect to default vs. named instance.|||> There was no such cliconfig.exe that I could find; maybe that is
> something not included with the Express edition.
the name is cliconfg.exe, not cliconfig.exe. Read carefully to see the difference. It is installed
with the operating system.
> When I installed 2005 Express, I don't remember any prompting to
> choose between default or named instance mode. The SSCM doesn't
> appear to reveal this distinction either. How does one determine this
> distinction - or does the Express edition only have one mode?
If you post the names of your SQL Server related services we can tell you.
> If it is running in named instance mode, then the SQL Browser isn't
> doing its job.
Possibly...
> Well, I have determined that I can go into the SSCM and adjust the
> properties for TCP/IP such that it now has a blank for TCP Dynamic
> Ports and 1433 for TCP Ports.
Which is how it look like by default for a named instance...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"cleveridea" <cleveridea.net@.gmail.com> wrote in message
news:1189638796.695061.109830@.57g2000hsv.googlegroups.com...
> On Sep 12, 3:35 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> If it is a named instance (suggested by your attempt to connect to localhost\SQLEXPRESS) then if
>> you
>> want to connect using the instance name, you need to have SQL Server Brower running. When a
>> client
>> connects using a machine *and* instance name, it will connect through 1434 to the SQL Server
>> browser
>> and pass the instance name. Browser will find out the port number and pass it back to the client
>> connectivity layer and the client will use that port number to connect.
>> I do suggest that you verify the client config using cliconfg.exe and well as the server config
>> using SQL Server Configuration Manager.
> There was no such cliconfig.exe that I could find; maybe that is
> something not included with the Express edition.
> When I installed 2005 Express, I don't remember any prompting to
> choose between default or named instance mode. The SSCM doesn't
> appear to reveal this distinction either. How does one determine this
> distinction - or does the Express edition only have one mode?
> If it is running in named instance mode, then the SQL Browser isn't
> doing its job.
> Well, I have determined that I can go into the SSCM and adjust the
> properties for TCP/IP such that it now has a blank for TCP Dynamic
> Ports and 1433 for TCP Ports. This solves my problem, essentially -
> since I can now use sqlcmd in default instance mode, which is good
> enough for me I guess.
> The next host I install 2005 Express, I will pay more attention to the
> choices offered in respect to default vs. named instance.
>|||> What I really want is is a starter .sql script file, that the end user
> executes via sqlcmd (using whatever credentials, connection
> information that is appropriate for them). I want that starter file
> to be able to chain and continue executing a handful of other
> scripts. It isn't administratively practical to put everything into a
> single .sql script.
Nobody is suggesting that, but it really sounds like you should have a batch
file (or another kind of wrapper, say a VBScript file or a C# command-line
app) that wraps this all together anyway. Are you really going to hand the
user a pile of .sql scripts, and tell them "go execute these, in this
order..." ?|||> When I installed 2005 Express, I don't remember any prompting to
> choose between default or named instance mode. The SSCM doesn't
> appear to reveal this distinction either. How does one determine this
> distinction - or does the Express edition only have one mode?
Tibor has already answered this (more than once, I think). Go into the
services control panel applet on the machine and look for "SQL Server" and
tell us what those are. Named instances will be followed by
(instance_name)...
> If it is running in named instance mode, then the SQL Browser isn't
> doing its job.
Maybe because SQL Browser is not started? You can check this status in the
same place (services applet).
A|||On Sep 13, 7:16 am, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Nobody is suggesting that, but it really sounds like you should have a batch
> file (or another kind of wrapper, say a VBScript file or a C# command-line
> app) that wraps this all together anyway. Are you really going to hand the
> user a pile of .sql scripts, and tell them "go execute these, in this
> order..." ?
That is exactly what I don't want to do, we agree there. However, I
do find it desirable in my situation to say - "hey run this ONE sql
script." Which I have setup and have working just like I want now.|||On Sep 13, 2:55 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > There was no such cliconfig.exe that I could find; maybe that is
> > something not included with the Express edition.
> the name is cliconfg.exe, not cliconfig.exe. Read carefully to see the difference. It is installed
> with the operating system.
typo on my part. there is no such executable with the express edition
that I see.|||On Sep 13, 7:22 am, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> > When I installed 2005 Express, I don't remember any prompting to
> > choose between default or named instance mode. The SSCM doesn't
> > appear to reveal this distinction either. How does one determine this
> > distinction - or does the Express edition only have one mode?
> Tibor has already answered this (more than once, I think). Go into the
> services control panel applet on the machine and look for "SQL Server" and
> tell us what those are. Named instances will be followed by
> (instance_name)...
> > If it is running in named instance mode, then the SQL Browser isn't
> > doing its job.
> Maybe because SQL Browser is not started? You can check this status in the
> same place (services applet).
> A
My disconnect on knowing if a server is in fact named or default was
answered but not in a way that made sense to me; that's why I didn't
see it.
I'm an "administrator-type" person that deals with dozens of different
products - none of them SQL Server up to this point. I tend to think
that a product should be able to describe itself when it comes to
important and little things. Looking at the Services window to
determine a product's very nature seems non-intuitive to me. I should
be able to ask the SQL Server, "hey are you setup as named or default"
via GUI, command line, or some config file. It doesn't seem like that
is the case with SQL Server. SQL Server Express is more of a "black
box" than I'm used to in database servers. Being a black box, for
express-type products, is just fine with me - unless there's a problem
and that can get in the way of solving things.
It is a named server (SQLEXPRESS), which is what I imagine is the
default for all SQL Server 2005 Express Edition installations.
Yes, SQL Browser is running. No it isn't doing its job, as far as I
can discern from the behaviors of how I am allowed to connect via
sqlcmd.
I am running this on my laptop, so I can only imagine something I've
done on my aggressively configured system is interfering - so for me
the issue is moot. Until I have a clean environment that I can
reliably reproduce this issue where named instances cannot be
referenced, I don't think there's anything that can/should be done
about it.
Thank you everyone for your extremely timely and very helpful
assistance on this one.|||> typo on my part. there is no such executable with the express edition
> that I see.
Again, it doesn't come with SQL Server, it comes with the operating system. I just started a clean
XP installation, selected RUN and entered cliconfg and the tool started. It isn't on a clean NT4 (I
just tried). I don't have a clean W2K box to try in, I'm afraid...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"cleveridea" <cleveridea.net@.gmail.com> wrote in message
news:1189703467.395521.64390@.r34g2000hsd.googlegroups.com...
> On Sep 13, 2:55 am, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> > There was no such cliconfig.exe that I could find; maybe that is
>> > something not included with the Express edition.
>> the name is cliconfg.exe, not cliconfig.exe. Read carefully to see the difference. It is
>> installed
>> with the operating system.
> typo on my part. there is no such executable with the express edition
> that I see.
>|||On Sep 13, 1:24 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > typo on my part. there is no such executable with the express edition
> > that I see.
> Again, it doesn't come with SQL Server, it comes with the operating system. I just started a clean
> XP installation, selected RUN and entered cliconfg and the tool started. It isn't on a clean NT4 (I
> just tried). I don't have a clean W2K box to try in, I'm afraid...
I'm running XP too; and I've found it. Unfortunately, It doesn't tell
me anything I didn't already know:
TCP/IP is enabled on port 1433
Named Pipes is enabled
Nice to know this utility is there, but it doesn't explain why the
Named Instance & SQL Browser combination isn't performing as billed.
Thanks again, I no longer wish to troubleshoot this matter in my
obviously funky fresh laptop environment. I'll worry about this
defect only if I can reproduce it in another clean environment.|||> determine a product's very nature seems non-intuitive to me. I should
> be able to ask the SQL Server, "hey are you setup as named or default"
> via GUI, command line, or some config file.
The problem is you can have multiple instances of SQL Server running on a
box. So just because you've identified the server, does not mean you are
done. If you have 16 instances of SQL Server running, and you ask your
question, which one should answer? *YOU* need to make some decisions here
about which instance of SQL Server you connect to. It is not "any SQL
Server will do," and for reasons which should be obvious.
Someone installed SQL Server, and chose named instance or default instance,
which port to run on, what kind of authentication, etc. etc. They did not
just magically appear in the services applet. But that is how you tell what
has been set up if you weren't the one who did it and it is not documented.
A