Solved

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

Posted on 2014-04-11
16
3,558 Views
Last Modified: 2014-04-13
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
0
Comment
Question by:midfde
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
16 Comments
 
LVL 38

Expert Comment

by:Jim P.
ID: 39995544
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
 
LVL 1

Author Comment

by:midfde
ID: 39996048
Jim:
Please see the image.A few 'snips'
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39996074
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 1

Author Comment

by:midfde
ID: 39996214
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
 
LVL 38

Expert Comment

by:Jim P.
ID: 39996355
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
 
LVL 1

Author Comment

by:midfde
ID: 39996404
>>Try it...
I certainly might should I not be familiar with sqlcmd for at least a decade.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39996690
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
 
LVL 1

Author Comment

by:midfde
ID: 39996797
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
 
LVL 38

Accepted Solution

by:
Jim P. earned 500 total points
ID: 39996807
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
 
LVL 1

Author Comment

by:midfde
ID: 39997351
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
 
LVL 1

Author Closing Comment

by:midfde
ID: 39997353
I am so impressed!
The question was "Why?..." however. It remains unanswered...
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39997412
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
 
LVL 1

Author Comment

by:midfde
ID: 39997481
>>...if you(???) weren't using the standard one.

The default SQL Server port is 1433
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39997539
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
 
LVL 1

Author Comment

by:midfde
ID: 39997673
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39997957
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question