Solved

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

Posted on 2014-04-11
16
3,496 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.
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
incorrect syntax near the order by 10 38
Nested Case statement 4 36
SSRS Deployment problem 5 63
RAISERROR WITH NOWAIT 2 14
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

790 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