Solved

Should a Database installed under a named instance show up when connecting through SQL Management Studio with just the server name?

Posted on 2013-06-04
15
403 Views
Last Modified: 2013-06-04
I am just wondering if this is normal. Ill put the basic steps I followed here

1. Installed SQL Server 2008 R2 on Windows Server 2008 R2
2. On the SQL Server Installation set up an Instance  - ABC rather than use the default Instance  - MSSQSERVER
3. Installed a SharePoint Database to the named Instance - SQLSERVERNAME\ABC
4. I can connect and see the Sharepoint Databases on SQLSERVERNAME\ABC in SQL Management Studio but I can also see the Sharepoint Databases if I just connect to SQLSERVERNAME in SQL Management Studio

The problem is I don't know if this is expected behaviour or not?

Any help appreciated
0
Comment
Question by:rhiancohen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
15 Comments
 
LVL 20

Expert Comment

by:dsacker
ID: 39219036
Is it possible that when you first installed SQL Server on that machine you did NOT select an instance name, then later added one (ABC)?

You description sound like SQL was installed with a default installation, and that later a named instance (ABC) was added (instead of having first installed as a named instance).
0
 
LVL 4

Assisted Solution

by:erik_nodland
erik_nodland earned 250 total points
ID: 39219049
Hi

That could be standard behaviour depending on how you have things set up. Just connecting to the Server name will connect on port 1433 which is the default port. When you call a server using a named instance (e.g. "SQLSERVERNAME\ABC"), the SQL client calls SQL Server's management service on port 1434. The management service replies with a dynamically assigned port (chosen at sql engine service startup) for the instance and the client connection proceeds using that new port. You could have your named instance configured as port 1433 also

HTH
Erik
0
 
LVL 1

Author Comment

by:rhiancohen
ID: 39219104
That sounds like a great explanation. Thanks v much

Is it a security issue that you can connect to the Sharepoint Databases via 2 connections though? - the server name and the instance name. It is locked down permission wise, I would have just thought the Sharepoint DBs would only show up when connecting to the named instance?
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 1

Author Comment

by:rhiancohen
ID: 39219110
Definitely installed with an instance and not default to start with. Thats why I'm slightly confused! I'm not the most "knowledgeable" sql person ;-) but definitely one instance.
0
 
LVL 20

Expert Comment

by:dsacker
ID: 39219136
What does this tell you?
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
  @value_name = 'InstalledInstances'

Open in new window

0
 
LVL 4

Expert Comment

by:erik_nodland
ID: 39219139
There is no real security issue apart from the face that port 1433 is normally the one targeted by hackers as its the default port that sql listens on although your SQL server should never be directly accessible externally anyway. That might be one reason it is listening on 1433 is because somebody needed to get to it through a firewall. That might be worth checking.

The only issue I can think of is that it might be a little confusing as you have already discovered. :-)

Thanks
Erik
0
 
LVL 1

Author Comment

by:rhiancohen
ID: 39219171
The Execute Query just said Command (s) completed successfully with no results or output

If you want me to try anything just fire away cheers
0
 
LVL 20

Expert Comment

by:dsacker
ID: 39219183
Hmmmm, xp_regread should have given you a list of installed instanced, including MSSQLSERVER, if you have a default instance installed.
0
 
LVL 1

Author Comment

by:rhiancohen
ID: 39219193
However is I look in the registry location it shows

(Default)
(1st Instance originally created)
(2nd Instance which we set up to test)

Does this mean we do indeed have a default instance because I really didn't install one then install the instance we wanted.
0
 
LVL 1

Author Comment

by:rhiancohen
ID: 39219203
Sorry just re-ran the query using the Execute button, not the parse tick button and it just shows the 2 named instances we have created and no default instance of MSSSQLSERVER (Apolgies!)
0
 
LVL 20

Expert Comment

by:dsacker
ID: 39219243
Run this script. Tell me if any of the ports are the same:
DECLARE	@Machine			varchar(80),
		@ServerName			varchar(80),
		@KeyToInterogate	varchar(200),
		@Version			varchar(255),
		@PortNumber			varchar(8) 

DECLARE @tblInstances TABLE (
		Machine			varchar(16),
		Instance		varchar(16),
		Port			varchar(8) )

SET ANSI_WARNINGS OFF
INSERT INTO @tblInstances (Machine, Instance, Port)
EXECUTE xp_regread
  @rootkey	  = 'HKEY_LOCAL_MACHINE',
  @key		  = 'SOFTWARE\Microsoft\Microsoft SQL Server',
  @value_name = 'InstalledInstances'
SET ANSI_WARNINGS ON

SET @Machine = @@SERVERNAME
IF CHARINDEX('\', @@SERVERNAME) > 0
	SET @Machine = LEFT(@Machine, CHARINDEX('\', @@SERVERNAME) - 1)
SET @KeyToInterogate = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP'

UPDATE @tblInstances
SET Machine = @Machine

DECLARE csr CURSOR FOR
SELECT	REPLACE(@Machine + '\' + Instance, '\MSSQLSERVER', '')
FROM	@tblInstances

OPEN csr
WHILE 1 = 1
BEGIN
	FETCH FROM csr INTO @ServerName
	IF @@FETCH_STATUS <> 0 BREAK

    IF charindex('\',@ServerName) > 0
    BEGIN
        SET @KeyToInterogate = 'SOFTWARE\Microsoft\Microsoft SQL Server\'
        SET @KeyToInterogate = @KeyToInterogate + substring(@ServerName,charindex('\',@ServerName) + 1,len(@ServerName) - charindex('\',@ServerName)) 
        SET @KeyToInterogate = @KeyToInterogate + '\MSSQLServer\SuperSocketNetLib\Tcp'
    END

    EXEC xp_regread 
        @rootkey = 'HKEY_LOCAL_MACHINE', 
        @key = @KeyToInterogate, 
        @value_name = 'TcpPort', 
        @value = @PortNumber output 

    UPDATE @tblInstances
    SET Port = CONVERT(varchar(8), @PortNumber)
    WHERE CURRENT OF csr

    PRINT @ServerName
    PRINT @PortNumber
END -- WHILE 1 = 1
CLOSE csr
DEALLOCATE csr

SELECT * FROM @tblInstances
ORDER BY Instance

Open in new window

0
 
LVL 1

Author Comment

by:rhiancohen
ID: 39219267
The ports are different

1st Instance = Port 1433
2nd Instance = Port 65065
0
 
LVL 20

Accepted Solution

by:
dsacker earned 250 total points
ID: 39219310
I have three instances on one of our boxes, which are as follows:

Machine     Instance          Port
-------------     -------------            ---------
SERVER12 Development  50538
SERVER12 Staging            49385
SERVER12 Testing            50481

That confirms erik_nodland's explanation. Your first named instance is using port 1433, which is the default port for an unnamed instance (aka, MSSQLSERVER). That's why you can connect to it either way.
0
 
LVL 1

Author Closing Comment

by:rhiancohen
ID: 39219359
Guys Thankyou very much for your help this afternoon. I really appreciate it :-)
0
 
LVL 20

Expert Comment

by:dsacker
ID: 39219491
I fine-tuned that query so that it would run on SQL 2000 as well, and added it to my blog.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

632 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