Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3849
  • Last Modified:

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
0
midfde
Asked:
midfde
  • 8
  • 7
1 Solution
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now