Solved

SQL Server 2012 express

Posted on 2016-10-12
24
31 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
 

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

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 45

Expert Comment

by:Vitor Montalvão
ID: 41841700
Did you try to connect from a remote computer?
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

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 45

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 45

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 45

Accepted Solution

by:
Vitor Montalvão earned 250 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 45

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 45

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

747 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

12 Experts available now in Live!

Get 1:1 Help Now