0

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?

2 Answers 2

2

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.

5
  • I used only two system, not any hosting location. Dec 28, 2012 at 10:34
  • It doesnt mind, you have to setup TCP/IP connectivity, just try it :)
    – Nidzaaaa
    Dec 28, 2012 at 10:36
  • 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." Dec 28, 2012 at 10:40
  • Did you setup your network for sharing?
    – Nidzaaaa
    Dec 28, 2012 at 10:46
  • yes I can access my system with sharing folders. Dec 28, 2012 at 10:47
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.

3
  • 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." Dec 28, 2012 at 10:30
  • Check again to make sure the service is running and also check the event logs. Also is this a named instance? Dec 28, 2012 at 14:21
  • yes I checked this. any other idea Dec 29, 2012 at 4:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.