Can't see *all* databases in a remote SQL Server instance

Question

I can connect to a remote SQL 2008 Server instance, but I cannot see all the databases in the instance using SQL Server 2008 Management Studio.

I suspect that my problem has something to do with not all database in the remote instance having the same version. For example, I "upgraded" a 2005 database to 2008 by doing a backup from 2005 and importing it into 2008.

When I realized that this was not one of the database that I could see from my other PC, I noticed that the compatibility level of the imported was still 2005, so I changed it to 2008. Still I could not see the database.

On that remote server, the instance node is named "sql2008", and is "Version 10".

When I connect to the sql2008 remote instance from my local PC, the connection is shown locally as being a "SQL Server Version 8.0" instance.

I suspect that locally, I am only being shown databases that are somehow in the remote 2008 instance but have not been upgraded.

I guess I don't know what constitutes an upgraded database and I don't know how to connect to see all the databases, even if this requires multiple connections from the source PC.

The local machine is Windows 7 Ultimate. The remote host is Windows XP Pro.

Related -not being able to see the SQL Server 2008 instance.

Update:

This is what I see on the remote server using Mgt Studio, namely, a SQL 2008 instance and a 2000 instance.

When I log onto the SQL 2008 instance and perform the following query

SELECT @@VERSION As Version, SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as ComputerNamePhysicalNetBIOS, SERVERPROPERTY('ServerName') As ServerName, SERVERPROPERTY('MachineName') As MachineName

The result re-confirms that the 2008b instance shown above with a version of 10 is a 2008 instance:

    Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)   Mar 29 2009 10:27:29   Copyright (c) 1988-2008 Microsoft Corporation  Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3) 

USPL-DOKMANC2   USPL-DOKMANC2\SQL2008B  USPL-DOKMANC2

OK, now from my LOCAL box, I used SQL Mgt Studio to try and connect to USPL-DOKMANC2 (the remote box.)

...I connect to the remote w/o the port I get this error:

But if I enter the DEFAULT port, I am able to connect. Note that the SQL2008 connection that I just added appears as being version "8" (SQL 2000).

I seem to be connecting to my SQL2000 instance again even though I specified in my connection params to connect to SQL2008b, a 2008 instance.

I can't seem to see my remote 2008 instance on uspl-dokmanc2. I keep getting my 2000 default instance databases showing up under sql2008b.

2nd Update:

Based on excellenf feedback below that SQL is most likely disregarding the name of the instance when tryingt o connect and determining the instance from the port specified. Now I just need to know the port that the SQL2008b instance is running on. I expected that SQL Mgt Config would tell me. The port is blank. Does this imply a default port? What would it be for 2008, not ethat I already have another 2008 instance, so if they all have to have separate ports, the normal 2008 default is probably not it.

Here's what I see in SQL Mgt Config for the 2008b instance. No port mentioned:

Answer 1

The remote instance (8.0) is a SQL Server 2000 instance

There is no such concept as "database version" that relates to visibility or permissions: every DB on a given server (version, SP and hotfix included) is at some internal version. This is why you can't restore or attach a SQL Server 2008 DB onto SQL Server 2005

I would run this to get the full details

SELECT
   @@VERSION,
   SERVERPROPERTY('ComputerNamePhysicalNetBIOS'),
   SERVERPROPERTY('ServerName'),
   SERVERPROPERTY('MachineName')

For example, you may have a client alias that means you're connecting to the wrong server.

Also, you have to explicitly DENY ANY VIEW DATABASE TO public to "hide" databases

Answer 2

Are you sure your 2008 instance uses 1433 port? MS SQL seem to disregard a name of a instance and connect with the given port in most cases. Use the Configuration Manager to check the port.

Also, check if the SQL Browser is available from the remote machine - you'll have to add 1434 on UDP to your firewall settings. Remember to make sure that the SQL Browser is running on your server of course. See: http://support.microsoft.com/kb/914277