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?
Best Answer
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.