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

x
?
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
Medium Priority
?
407 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
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 1000 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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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 1000 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

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.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

916 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