sql server 2012 named instance not working

I have a named instance on a SQL Server 2012 express setup that won't connect via TCP/IP.  From the server I can connect to "SERVER\INSTANCE" using shared memory.    If I put in "tcp:SERVER\INSTANCE" it will not connect.  It will say the "server is not found or inaccessible".    If I put in "tcp:SERVER,52424" it will connect right up.   I went through the TCP / IP properties under SQL Server Configuration Manager and it is enabled.  I also checking the "IP Addresses" tab and IP1 - IP5 are listed as

ACTIVE = YES
Enabled = NO
TCP Dynamic Port = 0
TCP Port = blank

Under IPALL it shows the below

TCP Dynamic Ports = 52424
TCP Port = blank

The issue seems to be just with the TCP/IP connection for the named instance.     Not sure if it matters since it is a local on the server, but the SQL BROWSER is running.

Thanks.
tiptechsAsked:
Who is Participating?
 
tiptechsConnect With a Mentor Author Commented:
I found the issue was due to an orphaned instance with the same name under 32 bit.   I ended up uninstalling everything and reinstalling as I couldn't find the 32 bit version to uninstall it.
0
 
strivoliCommented:
Firewall? Did you allow the traffic on that port?
0
 
strivoliCommented:
In my server (Ia also have 2 instances: default and named) the named one is set:

Under IPALL it shows the below

TCP Dynamic Ports = 52424
TCP Port = 1443

As you see I've set to 1443 and not blank.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
tiptechsAuthor Commented:
firewall is disabled and I am running the sql connection locally which I would think would bypass any firewalls.     Also, i have no problems connecting on that port when I specify that port.  Seems to be something with the named instance setup.
0
 
Surendra NathTechnology LeadCommented:
did you try to connect giving the port number along with the instance name

tcp:SERVER\INSTANCE,52424

Open in new window

0
 
tiptechsAuthor Commented:
I added the 1443 and restarted the browser server.  Still unable to connect via the named instance on TCP.
0
 
strivoliCommented:
The default and the named instance must use different ports.
0
 
strivoliCommented:
You must restart the SQL server. Why only restarting the browser server?
0
 
tiptechsAuthor Commented:
I tried the namespace, port , but that didn't work .   I thought the namespace instance was suppose to act like dns and tell the sql client which port to use.
0
 
tiptechsAuthor Commented:
Added 1443 back in and restarted the SQL Server.   Still the something with the namespace.

I also tried "tcp:SERVER,1443" and that worked, but not the namespace part.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
from what I remember: you connect to EITHER the names instance (without port), or to a port (but then not to the "named" instance).
so, trying to connect to "named instance : port" is not supported, but I might be wrong.
0
 
tiptechsAuthor Commented:
Namespace works with shared memory, but not TCP/IP.  named pipes is disabled.
0
 
tiptechsAuthor Commented:
correct,  the named space with the port doesn't work.    

Are there commands to query SQL to test namespaces in any ways.  Just to see what port it shows, etc..
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think you want to checked the methods listed here:
http://sqlandme.com/2013/05/01/sql-server-finding-tcp-port-number-sql-instance-is-listening-on/
Method 4: sys.dm_exec_connections DMV:

DMVs return server state that can be used to monitor SQL Server Instance. We can use sys.dm_exec_connections DMV to identify the port number SQL Server Instance is listening on using below T-SQL code:

SELECT local_tcp_port

FROM   sys.dm_exec_connections

WHERE  session_id = @@SPID

GO

Result Set:

local_tcp_port

61499
(1 row(s) affected)

Open in new window

0
 
tiptechsAuthor Commented:
I ran the below query on the server instance and got a NULL value for local_tcp_port.

SELECT local_tcp_port

FROM   sys.dm_exec_connections

WHERE  session_id = @@SPID

GO


I checked the logs and they show below

2014-02-03 12:02:01.91 spid12s     Server is listening on [ 'any' <ipv6> 52424].
2014-02-03 12:02:01.91 spid12s     Server is listening on [ 'any' <ipv4> 52424].

2014-02-03 12:02:01.91 Server      SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.
2014-02-03 12:02:01.91 spid12s     SQL Server is now ready for client connections. This is an informational message; no user action is required.
2014-02-03 12:02:01.93 Server      The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/SERVER:INSTANCE ] for the SQL Server service. Windows return code: 0x2098, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.
2014-02-03 12:02:01.94 Server      The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/SERVER:57625 ] for the SQL Server service. Windows return code: 0x2098, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

Not sure if the above SPN messages are a concern.
0
 
tiptechsAuthor Commented:
Looking into the query, that only shows the connection information for the current connection which is using shared memory instead of tcp / ip.  I can't connect via tcp.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you might want to run.
SELECT local_tcp_port FROM   sys.dm_exec_connections

anyhow, the error message might indicate that indeed your sql named instance cannot register it's "name" on the network...
0
 
tiptechsAuthor Commented:
I ran the "SELECT local_tcp_port FROM   sys.dm_exec_connections" and got 2 NULL results.  I have to enable shared memory in order to login to the database or login via a specific port.

The sql named instance does work via shared memory but not tcp/ip.
0
 
tiptechsAuthor Commented:
i also checked the authentication and it is using NTLM and not kerberos.
0
 
tiptechsAuthor Commented:
I ran some commands using "sqlcmd"

I was able to connect using "sqlcmd -S tcp:server\instance,port# -E"

but kept getting "server doesn't support requested protocol [xfffffffff]" when running "sqlcmd -S tcp:server\instance -E"

Any help is appreciated.
0
 
tiptechsAuthor Commented:
This is on a server 2012 essentials box.   I installed another instance of SQL and that connected with no issues after enabling tcp/ip and connecting via tcp:server\testname.   I found that the issue with name instance having the problem seems to be with the root instance directory.

When I installed the new instance it was installed in C:\Program Files\Microsoft SQL Server.  The one with the issue is installed in C:\Program Files (x86)\Microsoft SQL Server\Database directory.   Not sure if there any permissions settings to try.  I tried to give "everyone" full rights to the database folder, but no change.

Thanks
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
wow .... good finding
0
 
tiptechsAuthor Commented:
No other solutions were correct.
0
All Courses

From novice to tech pro — start learning today.