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

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
LVL 1
rhiancohenVMware Senior ConsultantAsked:
Who is Participating?
 
dsackerConnect With a Mentor Contract ERP Admin/ConsultantCommented:
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
 
dsackerContract ERP Admin/ConsultantCommented:
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
 
erik_nodlandConnect With a Mentor Commented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
rhiancohenVMware Senior ConsultantAuthor Commented:
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
 
rhiancohenVMware Senior ConsultantAuthor Commented:
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
 
dsackerContract ERP Admin/ConsultantCommented:
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
 
erik_nodlandCommented:
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
 
rhiancohenVMware Senior ConsultantAuthor Commented:
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
 
dsackerContract ERP Admin/ConsultantCommented:
Hmmmm, xp_regread should have given you a list of installed instanced, including MSSQLSERVER, if you have a default instance installed.
0
 
rhiancohenVMware Senior ConsultantAuthor Commented:
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
 
rhiancohenVMware Senior ConsultantAuthor Commented:
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
 
dsackerContract ERP Admin/ConsultantCommented:
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
 
rhiancohenVMware Senior ConsultantAuthor Commented:
The ports are different

1st Instance = Port 1433
2nd Instance = Port 65065
0
 
rhiancohenVMware Senior ConsultantAuthor Commented:
Guys Thankyou very much for your help this afternoon. I really appreciate it :-)
0
 
dsackerContract ERP Admin/ConsultantCommented:
I fine-tuned that query so that it would run on SQL 2000 as well, and added it to my blog.
0
All Courses

From novice to tech pro — start learning today.