[RESOLVED] SQL Server 2008 R2

[Doogle]

As this isn't a development issue I thought this was the best place to ask....

I have SQL Server 2008 R2 (Express) installed alongside SQL Server 2005 (Express) on one machine. Accessing the Databases on the 2005 system from a remote machine works well. However, whatever I try I can't access the 2008 Databases from the remote machine. (I can from the local machine). In case it's relevent, I also have MySQL on the same machine and can access those databases ok remotely.

In case you're wondering why so many database systems, we use the machine for testing deployment of applications, so we don't run into problems such as these when installing on Customer networks. In this particular case we've perfomed a migration of and Access Application to SQL Server 2008 R2.

I've trawled through the Internet and tried everything suggested but still no joy. Using SQL Server Management Express and SQL Server Management Studio, neither can 'see' the SQL Server from the 'Server Name' drop-down on the Management Studio 'Connect to Server' screen.

I can't create an ODBC data source, it just returns saying it can't find the server.

I've also tried installing SQL Server 2008 R2 Express on a second machine and tried to access that remotely, no joy. Both 2008 instances have Winsodw Authentication and SQL Authentication enabled and it makes no difference - I can't get in using the 'sa' account.

Logic dictates it's a configuration issue(?), as neither instances can be 'seen' it's likely to be network related (?) but I just can't seem to find the 'magic' setting(s). Yes, TCP/IP is enabled via SQL Server Manager on all systems and they've been re-started several times. I have allowed exceptions for ports 1433 and 1434 and the .exe in the Windows Firewall (and, as I said before, have tried with all Firewalls disabled)

To summarise:

Machine 'A' has SQL Server 2005 Express, SQL Server 2008 R2 Express and MySQL
Machine 'B' has SQL Server 2008 Express

Machine 'B' can access MySQL and SQL Server 2005 Express on Machine 'A'
Neither Machine can access the SQL Server 2008 R2 Express instance on the other.

Local Applications using SQL Server 2008 R2 Express on each machines are OK.

Both systems are running Vista Home Edition SP1.

Has anyone any ideas, or should I just try de-install and re-install ?

[Nightwalker83]

Are you using the same setup information, drivers etc on both machines? Do you receive any errors, if so what are they?

Edit:

Does this help?

[Doogle]

Nice one. I hadn't found that thread. In case anyone else has the problem, it appears that even if you enable TCP/IP you have to go and enable each IP element individually.

from the thread you posted

Go to All Programs -> Microsoft SQL Server 2008 -> Configuration Tools -> SQL Server Configuration Manager.

Now Sql Server Configuration manager window will appear. Expand SQL Server Netwoek Configuration, select Protocol for MSSQLSERVER & right click on TCP/IP in detail pane go to property and Select IP Address tab. Select in

IP1=> Active=Yes, Enabled=Yes,IP Address =<machine ip address>, TCP Dynamic Port=Empty, TCP Port=1433

IP2=> Active=Yes, Enabled=Yes,IP Address =127.0.0.1, TCP Dynamic Port=Empty, TCP Port=1433

IP3=> TCP Dynamic Port=Empty, TCP Port=1433

And click ok & Restart your SQL Server Service. Run your ASP.Net application its working.

[techgnome]

Use the management studio, connect to the sql2008 instances.... then check the server properties and make sure that the "Allow Remote Connections" is turned on... Express by default has the remote access feature turned off. Then try connecting again. Since you have multiple instances of SQL server... be sure you're connecting to the right named instance. If it was a fresh install of SQL2008R2.... that's probably the name of it. so your server name is {computername]\sql2008r2

-tg

[Doogle]

Thenks, techgnome, for the advice. Even though "Allow Remote Connections" was turned on, TCP/IP was disabled, enabling that didnt set the properties of the various IP entries to 'Enabled = Yes' I had to do that manually. Seems like a strange set-up for something that is meant to be a 'Server'.