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
401 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Job - date manual 1 48
MS SQL Server select from Sub Table 14 49
point in time restore in SQL server 26 53
SQL Server set parent recort in select with row number 8 27
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

734 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