September 18, 2015 at 5:06 pm
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
September 19, 2015 at 2:50 am
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.
😎
September 19, 2015 at 11:23 am
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
September 19, 2015 at 12:49 pm
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
September 19, 2015 at 1:03 pm
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
September 19, 2015 at 1:53 pm
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
September 19, 2015 at 1:56 pm
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
September 21, 2015 at 8:44 am
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
September 21, 2015 at 9:14 am
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" 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply