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
389 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
  • 7
  • 6
  • 2
15 Comments
 
LVL 20

Expert Comment

by:dsacker
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 1

Author Comment

by:rhiancohen
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 20

Expert Comment

by:dsacker
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
The ports are different

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

Accepted Solution

by:
dsacker earned 250 total points
Comment Utility
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
Comment Utility
Guys Thankyou very much for your help this afternoon. I really appreciate it :-)
0
 
LVL 20

Expert Comment

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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
This video discusses moving either the default database or any database to a new volume.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

762 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

8 Experts available now in Live!

Get 1:1 Help Now