Solved

sql server 2012 named instance not working

Posted on 2014-02-03
23
587 Views
Last Modified: 2014-02-10
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.
0
Comment
Question by:tiptechs
  • 14
  • 4
  • 4
  • +1
23 Comments
 
LVL 19

Expert Comment

by:strivoli
Comment Utility
Firewall? Did you allow the traffic on that port?
0
 
LVL 19

Expert Comment

by:strivoli
Comment Utility
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
 

Author Comment

by:tiptechs
Comment Utility
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
 
LVL 16

Expert Comment

by:Surendra Nath
Comment Utility
did you try to connect giving the port number along with the instance name

tcp:SERVER\INSTANCE,52424

Open in new window

0
 

Author Comment

by:tiptechs
Comment Utility
I added the 1443 and restarted the browser server.  Still unable to connect via the named instance on TCP.
0
 
LVL 19

Expert Comment

by:strivoli
Comment Utility
The default and the named instance must use different ports.
0
 
LVL 19

Expert Comment

by:strivoli
Comment Utility
You must restart the SQL server. Why only restarting the browser server?
0
 

Author Comment

by:tiptechs
Comment Utility
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
 

Author Comment

by:tiptechs
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 

Author Comment

by:tiptechs
Comment Utility
Namespace works with shared memory, but not TCP/IP.  named pipes is disabled.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:tiptechs
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 

Author Comment

by:tiptechs
Comment Utility
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
 

Author Comment

by:tiptechs
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 

Author Comment

by:tiptechs
Comment Utility
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
 

Author Comment

by:tiptechs
Comment Utility
i also checked the authentication and it is using NTLM and not kerberos.
0
 

Author Comment

by:tiptechs
Comment Utility
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
 

Author Comment

by:tiptechs
Comment Utility
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
 

Accepted Solution

by:
tiptechs earned 0 total points
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
wow .... good finding
0
 

Author Closing Comment

by:tiptechs
Comment Utility
No other solutions were correct.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

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…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

743 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

18 Experts available now in Live!

Get 1:1 Help Now