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

x
?
Solved

sql server 2012 named instance not working

Posted on 2014-02-03
23
Medium Priority
?
641 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 20

Expert Comment

by:strivoli
ID: 39829805
Firewall? Did you allow the traffic on that port?
0
 
LVL 20

Expert Comment

by:strivoli
ID: 39829820
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
ID: 39829822
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39829824
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
ID: 39829831
I added the 1443 and restarted the browser server.  Still unable to connect via the named instance on TCP.
0
 
LVL 20

Expert Comment

by:strivoli
ID: 39829836
The default and the named instance must use different ports.
0
 
LVL 20

Expert Comment

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

Author Comment

by:tiptechs
ID: 39829842
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
ID: 39829853
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39829871
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
ID: 39829880
Namespace works with shared memory, but not TCP/IP.  named pipes is disabled.
0
 

Author Comment

by:tiptechs
ID: 39829883
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39830150
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
ID: 39830565
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
ID: 39830574
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39830650
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
ID: 39830678
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
ID: 39830930
i also checked the authentication and it is using NTLM and not kerberos.
0
 

Author Comment

by:tiptechs
ID: 39830976
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
ID: 39833552
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
ID: 39835764
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39835841
wow .... good finding
0
 

Author Closing Comment

by:tiptechs
ID: 39846812
No other solutions were correct.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

886 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