question

imakedon avatar image
imakedon asked

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 (remote access is allowed). 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 (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 (Build 7601: Service Pack 1) ---------------------------------------------------------------------------------------- FDE-HQ-WK4\SQLEXPRESS 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 (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>
sql server 2014
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This site works by voting. Please indicate all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next to that answer. You can mark your own answer too.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
Connect to the instance locally using SQL Server Management Studio. Right click on the instance in the Object Explorer window. Select Properties from the context menu. Go to the Connections page. Click on "Enable remote connection." Also, go to the Configuration Manager and ensure that TCPIP is enabled for the instance.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The first step had no effect for me but step 2, enabling TCPIP, solved the issue for me. I had to restart the SQL server before it took effect.
1 Like 1 ·
imakedon avatar image
imakedon answered
Please see attached. ![alt text][1] [1]: /storage/temp/1465-_.png

_.png (28.3 KiB)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Nothing changed in my problem after I enabled TCP/IP protocol and restarted the service (EST). d:\1>time/t&&sqlcmd -S 10.0.0.100\sql2014 -U sa -P dbasql -Q "select @ 08:45 AM HResult 0xFFFFFFFF, Level 16, State 1 SQL Network Interfaces: Server doesn't support requested protocol [xFF Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred whi the default settings SQL Server does not allow remote connections.. Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
0 Likes 0 ·
imakedon avatar image
imakedon answered
This does work: d:\1>time/t&&sqlcmd -Sfde-hq-wk4\sql2014,1433 -Usa -Pdbasql -Q"select @@version select @@servername" 10:34 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 (Build 7601: Service Pack 1) --------------------- FDE-HQ-WK4\SQLEXPRESS d:\1> Thanks, Mr.Grant Fritchey anyway.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Venkataraman avatar image
Venkataraman answered
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks, Venkataraman. Alas, nothing of what you mentions did help in my case, until I explicitly specified the port number.
0 Likes 0 ·
was the sql browser service running? that is the piece that automatically resolves port numbers so you don't have to specify them.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.