Solved

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

Posted on 2014-04-11
16
3,348 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
  • 8
  • 7
16 Comments
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
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
Comment Utility
Jim:
Please see the image.A few 'snips'
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
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
 
LVL 1

Author Comment

by:midfde
Comment Utility
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.
Comment Utility
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
Comment Utility
>>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.
Comment Utility
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
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 38

Accepted Solution

by:
Jim P. earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
I am so impressed!
The question was "Why?..." however. It remains unanswered...
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
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
Comment Utility
>>...if you(???) weren't using the standard one.

The default SQL Server port is 1433
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

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…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now