Why does remote access to SQL Server Express Edition 2014 fail?

I’ve just installed SQL Server 2014 Express edition on my W7 work station. Please help me to connect to the new instance from my old kind Windows XP. Command line sessions below provide details (see also the attached image). Google search did not help much.

On computer that hosts the new instance (10.0.0.100, "server" workstation) both instances are accessible:
C:\1>hostname
FDE-HQ-WK4
[C:\1>sqlcmd -Sfde-hq-wk4\sql2014 -Usa -Pdbasql -Q"select @@version select @@servername"
----------------------------------------------------------------------------------------
Microsoft SQL Server 2014 - 12.0.2000.8 (Intel X86)
        Feb 20 2014 19:20:46
        Copyright (c) Microsoft Corporation
        Express Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
----------------------------------------------------------------------------------------
FDE-HQ-WK4\SQL2014

C:\1>sqlcmd -Sfde-hq-wk4 -Usa -Pdbasql -Q"select @@version select @@servername"
----------------------------------------------------------------------------------------
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
        Mar 29 2009 10:27:29
        Copyright (c) 1988-2008 Microsoft Corporation
        Express Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
----------------------------------------------------------------------------------------
FDE-HQ-WK4\SQLEXPRESS

Open in new window


The next session is on a computer (10.0.0.113 "client" workstation) that physically shares my desk with the previous one, old instance works fine but connection to the new one fails:
d:\1>sqlcmd -Sfde-hq-wk4 -Usa -Pdbasql -Q"select @@version select @@servername"
-------------------------------------------------------------------------------------------------
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
        Mar 29 2009 10:27:29
        Copyright (c) 1988-2008 Microsoft Corporation
        Express Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
-------------------------------------------------------------------------------------------------
FDE-HQ-WK4\SQLEXPRESS

d:\1>sqlcmd -Sfde-hq-wk4\sql2014 -Usa -Pdbasql -Q"select @@version select @@servername"
HResult 0xFFFFFFFF, Level 16, State 1
SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connectio
the default settings SQL Server does not allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

d:\1>

Open in new window

1.png
LVL 1
midfdeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim P.Commented:
Do you have the SQL Server Browser Service running on the Win7 machine?

Also on the W7 server I would disable Named Pipes in the configuration manager.
0
midfdeAuthor Commented:
Jim:
Please see the image.A few 'snips'
0
Jim P.Commented:
The last one in the top image is the SQL Server Browser service and it's not running. That needs to be running for the remote access to work.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

midfdeAuthor Commented:
I changed "Startup Type" of "SQL Server Browser" service from "Disabled" (BTW, only one of the two instances did not respond without this services, remember?) to automatic, and started it. I also restarted all SQL Server instances on W7 "server" workstation.
Nothing changed on my "client" XP workstation (see my initial post) with respect to described SQL Server nstances connections.

 
C:\1>tasklist /v|findstr -i brows
sqlbrowser.exe                2200 Services                   0      3,364 K [b]Unknown[/b]

C:\1>hostname
FDE-HQ-WK4

C:\1>sqlcmd -L
Servers:
    FDE-HQ-WK4
    FDE-HQ-WK4\SQL2012
    FDE-HQ-WK4\SQL2014
    FDE-HQ-WK4\SQLEXPRESS

Open in new window

0
Jim P.Commented:
Try it as:

sqlcmd -S fde-hq-wk4\sql2014 -U sa -P dbasql -Q "select @@version select @@servername"

Open in new window


It expects the spaces.
0
midfdeAuthor Commented:
>>Try it...
I certainly might should I not be familiar with sqlcmd for at least a decade.
0
Jim P.Commented:
Another one to try is by IP:

sqlcmd -S 192.168.x.xx\sql2014 -U sa -P dbasql -Q "select @@version select @@servername"

Open in new window

0
midfdeAuthor Commented:
I certainly tried it already:
d:\1>sqlcmd -S10.0.0.100 -Usa -Pdbasql -Q"select @@version select @@servername"
----------------------------------------------------------------------------------------------------
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
        Mar 29 2009 10:27:29
        Copyright (c) 1988-2008 Microsoft Corporation
        Express Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)


(1 rows affected)

-------------------------------------------------------------------------------------------------------------------------------
FDE-HQ-WK4\SQLEXPRESS

(1 rows affected)

d:\1>sqlcmd -S10.0.0.100\sql2014 -Usa -Pdbasql -Q"select @@version select @@servername"
HResult 0xFFFFFFFF, Level 16, State 1
SQL Network Interfaces: Server doesn't support requested protocol [xFFFFFFFF].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecti
the default settings SQL Server does not allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

d:\1>sqlcmd -S 10.0.0.100\sql2014 -U sa -P dbasql -Q "select @@version select @@servername"
HResult 0xFFFFFFFF, Level 16, State 1
SQL Network Interfaces: Server doesn't support requested protocol [xFFFFFFFF].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecti
the default settings SQL Server does not allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

d:\1>

Open in new window

0
Jim P.Commented:
Is the firewall on on the Win7 machine?

Either way go into the SQL Server Services in the configuration manager. For the SQL 2014 instance in the properties there will be a port listed. If the firewall is on punch a hole through for that port.

If it isn't then try connecting as

sqlcmd -S10.0.0.100\sql2014,port -Usa -Pdbasql -Q"select @@version select @@servername"

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
midfdeAuthor Commented:
Wow!
Thanks, Jim!
d:\1>time/t&&sqlcmd -S 10.0.0.100\sql2014 -U sa -P dbasql -Q "select @@version select @@servername"
10:07 AM
HResult 0xFFFFFFFF, Level 16, State 1
SQL Network Interfaces: Server doesn't support requested protocol [xFFFFFFFF].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the
the default settings SQL Server does not allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

d:\1>time/t&&sqlcmd -S 10.0.0.100\sql2014,1433 -Usa -Pdbasql -Q"select @@version select @@servername"
10:10 AM
----------------------------------------------------------------------------------------------------
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
        Mar 29 2009 10:27:29
        Copyright (c) 1988-2008 Microsoft Corporation
        Express Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
---------------------------------------------------------------------------------------------------------
FDE-HQ-WK4\SQLEXPRESS
d:\1>

Open in new window

0
midfdeAuthor Commented:
I am so impressed!
The question was "Why?..." however. It remains unanswered...
0
Jim P.Commented:
I've had this happen, once, with another program. Somehow the developers had programmed it that you had to change the port in the SW services setup if you weren't using the standard one.

It may also be that the SQL Browser service is the 2008 version and doesn't recognize the newer instances.
0
midfdeAuthor Commented:
>>...if you(???) weren't using the standard one.

The default SQL Server port is 1433
0
Jim P.Commented:
The way the SQL Server Browser service supposedly works is it sits on port 1433, and the default instance just ServerName gets it unless otherwise configured. Then the instances (10.0.0.100\sql2014) get assigned another set of concurrent TCP/UDP ports (e.g. 3392/3393) assigned.

So what happens is a connection call comes into the 1433/1434 ports. The Browser service says use ports 3392/3393 for that instance.

In this case it isn't happening the way it is supposed to. So without sitting down and doing a hands on diagnosis of the issue, I'm working off educated guesses.
0
midfdeAuthor Commented:
Your "educated guesses" look good, Jim P. Not that I agree, but thanks for discussion anyway.
Well, to recap:

Given:
Healthy OS Windows 7 runs MS Installation package;
The run succeeds with not a single warning.
Standard utility SQLCMD (Version 9.00.4035.00 NT INTEL X86) does not work on remote computer with the new instance (neither does SSMS); it works with the previous one on the same host.

Conclusion:
MS released a faulty product (at least installation package, maybe worse).
0
Anthony PerkinsCommented:
MS released a faulty product (at least installation package, maybe worse).
Don't you hate when that happens?  And the worse part about it, is that you can download the product for free.  I mean it is not like it was a retail version where one could call Microsoft PSS to get some decent help.  Perhaps it is time to look at Oracle.   Oh wait...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.