Why are databases showing up in a newly created instance?

Question

Post reply

Why are databases showing up in a newly created instance?

stanlyn

Say Hey Kid

Points: 701
More actions
September 18, 2015 at 5:06 pm

#300761

Hi,

Why are the user databases that were created in the MSSQLSERVER default instance showing up in the newly created ALPHAONE instance? I'm successfully logging into the alphaone database as it shows as "DAS\AlphaOne,1xxxPport) at the top of the treeview in ssms. I'm logging in as sa and can edit anything.

The issue here is that all the user databases are shown and can even be edited. I created this instance in an effort to hide databases from whoever is not supposed to see.

I was expecting a clean instance with only the system databases... not so here... Is there something that can be set to keep each instance's databases private into itself?

Any help, links, white papers would be appreciated,

Thanks,

Stanley

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 1

stanlyn (9/18/2015)

Hi,

Why are the user databases that were created in the MSSQLSERVER default instance showing up in the newly created ALPHAONE instance? I'm successfully logging into the alphaone database as it shows as "DAS\AlphaOne,1xxxPport) at the top of the treeview in ssms. I'm logging in as sa and can edit anything.

The issue here is that all the user databases are shown and can even be edited. I created this instance in an effort to hide databases from whoever is not supposed to see.

I was expecting a clean instance with only the system databases... not so here... Is there something that can be set to keep each instance's databases private into itself?

Any help, links, white papers would be appreciated,

Thanks,

Stanley

Quick thought, either you're connecting to the wrong instance or the databases were created in the wrong instance. To check this, compare the content of sys.master_files in both instances. Also run cliconfg.exe and check for conflicting aliases.

😎

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

You're connecting to the default instance, not the new one. When you specify a port number, you connect to the SQL instance that's listening on that port. If it's not the one you're expecting to connect to, then you'll be seeing DBs that you aren't expecting.

Why are you specifying an explicit port number? Why not just the instance name and let the SQL Browser service identify the port that is needed automatically?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

stanlyn Say Hey Kid Points: 701 More actions · Answer 3

Hi,

>> either you're connecting to the wrong instance or the databases were created in the wrong instance

The name at the top of the treeview in ssms shows DAS\AlphaOne,1xxx. The newly created instance is named AlphaOne and there has been no databases added to it. When I log into that instance, I see and can edit all the user databases that were created in the default instance of MSSQLSERVER,1xxx. The databases were only created in the default instance.

When exploring the file system files and folder structures the new instance created, the newly created instance does not have any user databases within, which is correct. So why is my "sa" login to DAS\AlphaOne (the new instance), 1) show I'm logged into DAS\AlphaOne and, 2) be showing all the databases that were created in the default instance instead of being empty of user databases?

Does the default instance's databases automatically show and have to be excluded or something?

Does the user "sa" cause the default's databases show everywhere because it is sql server's account, not just an instance's account? Anything I should look at in this area?

Am I correct in thinking that if I log into a newly created instance using the "sa" account, I should NOT be seeing any user databases, until one is created within that instance?

Thanks,

Stanley

stanlyn Say Hey Kid Points: 701 More actions · Answer 4

Hi Gail,

>> You're connecting to the default instance, not the new one. When you specify a port number, you connect to the SQL instance that's listening on that port. If it's not the one you're expecting to connect to, then you'll be seeing DBs that you aren't expecting.

Can't I trust what ssms is showing at the top of the treeview pane? I is clearly showing in in the newly created instance. I'm explicitly logging into the instance also, so how can I possibly NOT be in the newly created instance?

>> Why are you specifying an explicit port number? Why not just the instance name and let the SQL Browser service identify the port that is needed automatically?

I changed it's listening port because 1433 is automatically know as sql server, and changing it to a non standard number seems like a good security measure? It worked great in the single default instance I was previously using.

I thought SQL Server listens to the port and not the instance. Am I correct?

Is 1433 a security risk, as to me a port scan that finds 1433 would cause an intruder to auto assume sql server was deployed, therefore changing its default would add another layer to the intruders work load, correct?

Thanks,

Stanley

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

You're missing the point. I don't care what port you use (and to be honest, port scanners aren't exactly hard to use, so minor security by obscurity). I didn't ask why you're not using the default port. I asked why you're explicitly specifying a port for a named instance and not allowing the SQLBrowser service to look up the port name based on the instance name.

Yes, SQL listens on port number, not instance name. That means that if you have a default instance listening on port 12345 (for example) and the named instance MyNewInstance listening on port 23456 and you connect to MyServer\MyNewInstance, 12345, you will actually be connecting to the default instance, because that's the instance listening on port 12345. You explicitly specified the port and so you get the instance listening on that port, regardless of the name you specified.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

stanlyn (9/19/2015)

Does the default instance's databases automatically show and have to be excluded or something?

No. The default instance's databases show when you connect to the default instance.

Does the user "sa" cause the default's databases show everywhere because it is sql server's account, not just an instance's account?

No. sa is a login for an instance, not all instances on a server.

Am I correct in thinking that if I log into a newly created instance using the "sa" account, I should NOT be seeing any user databases, until one is created within that instance?

Correct. You won't. If you're seeing the databases on InstanceX, then you're connected to InstanceX. There is no other way you could be seeing InstanceX's databases.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

WayneS SSC Guru Points: 95405 More actions · Answer 7

Everything that Gail has said is correct (naturally). If you're seeing the databases from another instance, you are connected to it.

Have you tried running these to see what you are connected to?:

SELECT @@SERVERNAME;

And



SELECT name, filename

FROM sys.dm_server_services;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Perry Whittle SSC Guru Points: 233792 More actions · Answer 8

stanlyn (9/18/2015)

I'm successfully logging into the alphaone database as it shows as "DAS\AlphaOne,1xxxPport) at the top of the treeview in ssms.

when connecting, for the instance name and port number, you use either\or not both

Use either

DAS\AlphaOne

or

DAS, 1xxxx

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉