Remote database connection problem?

Question

I have two systems with Windows XP. I have installed SQL Server 2005 on a system and tried to use SQL Server Management Studio Express on both to use the database.

I am able to use the database on the system on which I have installed it, but on the other system I get the error:

remote database access problem

I am using Windows authentication mode on SQL Server 2005.

Is it possible to connect the database remotely or not? If yes, then how I can do it? Any other ideas to resolve this issue?

Answer 1

Yes, you can use SQL Server Management Studio, even if it's an Express version to connect to any SQL Server,you have TCP/IP connectivity to. Just launch it, enter the DNS host name or IP address in the 'Server Name' box and hit Connect.

Two things may prevent this from working:

Your SQL Server isn't set up for TCP/IP connectivity. This is the default setting from version 2005 onwards, and can be changed using the SQL Server Configuration Manager.

There is a firewall between you and your SQL Server blocking TCP/IP traffic. This is an entirely sensible construction: you do NOT want your database server to be available from the general Internet, as this is a huge security risk. In fact, if your hosting company allows this kind of access by default, I'd be looking for a different provider...

Anyway, what seems to be needed in your scenario is: a) the hosting company enabling TCP/IP on your SQL Server instance, b) them providing you with secure access to the IP address that instance is running on. This will involve some kind of VPN or SSH port-forwarding soluition, or at least an IP filter, where only 'trusted' IP addresses can access the SQL Server (which is not a great solution, but can be used if nothing else can be implemented). You'll have to discuss these requirements with your hosting company.

• Ghost Answer: I used only two system, not any hosting location.
• Nidzaaaa: It doesnt mind, you have to setup TCP/IP connectivity, just try it :)
• Ghost Answer: yes I have enable the TCP/IP in "Protocols for SQLEXPRESS" and in "Client Protocols" but it gives an error like "A network-related or instance-specific error occurred while establishing a connection to sql server. The server was not found or was not accessible. Verify that the instance name is correct and that sql server is configured to allow remote connections."
• Nidzaaaa: Did you setup your network for sharing?
• Ghost Answer: yes I can access my system with sharing folders.

Answer 2

Take a look at the configuration manager and look at the following. It will be in "start menu->All Programs->Microsoft SQL Server 2005->Configuration tools".

Go into the SQL Server 2005 configuration manager. Select "SQL Server Network Configuration" and then "Protocols for MSSQLSERVER". Right click on "tcp/ip" and select enabled. This will ensure that the instance accepts connections over tcp/ip. If "Enabled" is greyed out then it is already enabled.

Also under "SQL Native Client Configuration" Select "Client protocols" and then select TCP/IP again and make sure it is enabled.

Also under "SQL Server 2005 services" make sure tat the "SQL Server (MSSQLSERVER)" service is running.

• Ghost Answer: I have tried this. It gives an error "A network-related or instance-specific error occurred while establishing a connection to sql server. The server was not found or was not accessible. Verify that the instance name is correct and that sql server is configured to allow remote connections."
• Brandon leach: Check again to make sure the service is running and also check the event logs. Also is this a named instance?
• Ghost Answer: yes I checked this. any other idea