Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Server 2012 express

Posted on 2016-10-12
24
Medium Priority
?
65 Views
Last Modified: 2016-10-13
I did the install and enabled TCP in the SQL server manager,  The IP or port 1433 does not show up in netstat.  What am I missing??
0
Comment
Question by:asrvwiz
  • 12
  • 7
  • 3
  • +1
24 Comments
 
LVL 20

Expert Comment

by:Russ Suter
ID: 41841027
Have you opened port 1433 in your firewall?

Also, if you are using a named instance rather than the default instance it will be using a different port.
0
 

Author Comment

by:asrvwiz
ID: 41841040
I did open 1433, that did not work so I shut off the firewall.

I am using the default instance so it is servername\sqlserverexpress.  correct?
0
 
LVL 20

Expert Comment

by:Russ Suter
ID: 41841041
The default instance name is usually just "SQLEXPRESS". If your instance is "sqlserverexpress" you probably have a named instance. Go to the SQL Server Configuration Manager and right click on the TCP/IP protocol and select "Properties". If there's a custom port in use you can find it there. Disabling the firewall completely seems to rule out port issues. You may just need to refer to the server by its named instance. Just edit your connection to use the name. Even the default instance will work that way.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:asrvwiz
ID: 41841053
Yes that is it sqlexpress.  only sqlserver running in services.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 41841061
seems like you have dynamic port enabled on your settings (i think by default it is dynamic ). Go to the sql server configuration manager -> SQL Server Network configuration .> Protocols for XXXX -> TCP/IP -> IP Addresses
scroll down to the bottom and put the port number 1433 on TCP port and remove the 0 from the Dynamic ports
0
 

Author Comment

by:asrvwiz
ID: 41841075
Aneesh,

I have then set that way.  My concern is with IP1 and IP2
sql-2012-settings.docx
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 41841078
I would set all dynamic ports to blank , and you need to restart the services,
0
 

Author Comment

by:asrvwiz
ID: 41841145
Aneesh,

Still the same, no 1433 in netstat.
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 1000 total points
ID: 41841315
can you check the port number in sql  configuration manager again.
Alternatively you can check the sql error log, or run the following statements

SELECT DISTINCT
    local_tcp_port
FROM sys.dm_exec_connections
WHERE local_tcp_port IS NOT NULL

or

USE master
GO
xp_readerrorlog 0, 1, N'Server is listening on'
GO
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41841506
The IP or port 1433 does not show up in netstat.
Did you try to connect to the SQL Server instance or you're only worried with netstat?

I have then set that way.  My concern is with IP1 and IP2
You'll only need to set on the IPAll section. Btw, was port 1433 already configured or you changed it?
0
 

Author Comment

by:asrvwiz
ID: 41841697
We can connect to the SQL instance with SQL server connection manager.  I believe it had 1433 and a 5 digit dynamic port.
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41841700
Did you try to connect from a remote computer?
0
 

Author Comment

by:asrvwiz
ID: 41841707
I am  trying to install an app and it is saying it can not connect on port 1433.  I used netstat to see if 1433 was listening.
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41841708
What's the app connection string?
0
 

Author Comment

by:asrvwiz
ID: 41841709
Aneesh,

Here is the result.  It says it is listening, but there is nothing in netstat that lists 1433

2016-10-12 19:33:32.630      spid12s      Server is listening on [ 'any' <ipv6> 1433].
2016-10-12 19:33:32.630      spid12s      Server is listening on [ 'any' <ipv4> 1433].


The other query returned local_tcp_port
0
 

Author Comment

by:asrvwiz
ID: 41841723
I have created a DB in the instance, changed the SA password, and created a new login, so SSMS does connect to the instance.
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41841741
but there is nothing in netstat that lists 1433
netstat will only return data if you have some remote connection to the database and that's why I asked if you tried to connect from a remote computer.
0
 

Author Comment

by:asrvwiz
ID: 41841748
Should the command osql -L show the connection?

I will check when I get to work if I can see the DB from another SQL server.
0
 
LVL 52

Accepted Solution

by:
Vitor Montalvão earned 1000 total points
ID: 41841754
Should the command osql -L show the connection?
No. -L parameter is for listing purpose only so it won't connect to your SQL Server instance.
You may try the following:
osql -E -q "select * from sysdatabases"  

Open in new window

0
 

Author Comment

by:asrvwiz
ID: 41841759
But should it show a connection available?
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41841763
It should. The command will connect to sql server instance and run the query in parameter (-q).
You can also try to run it in a remote computer that has the OSQL installed but then you'll need to provide the SQL Server instance name (-s):
osql -S Servername\SQLEXPRESS -E -q "select * from sysdatabases"  

Open in new window

0
 

Author Comment

by:asrvwiz
ID: 41841806
Vitor,

That worked.  I could connect and see the DB I created.  I tried osql - L from another SQL server and I could see the instance.  I hit next on the install for the application and it started to create the tables in the DB I created.  I tried the osql -L on the server locally, and the instance now shows up.

 I am not sure what fixed the issue.
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41841831
I am not sure what fixed the issue.
A service restart after you changed the Port to static, I guess.
0
 

Author Closing Comment

by:asrvwiz
ID: 41841844
Thank you all!  I appreciate everyone's input.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

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…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

783 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