Connecting to remote database?

I am using SQL Server 2000 database in my VB.Net project.

My VB.net application is installed one two machines in different cities i.e. there are two separate databases.

First machine's IP address is say 202.33.44.55 and other machine IP address is say 203.33.55.66. Now I want to use each others databases i.e. I want to set their database group.

So that in my VB.Net's application which is installed on 202.33.44.55 I could see data of the 203.33.55.66 by just changing the connection string

You can include both connection strings in the web.config and then change the data source via dynamic code.

This method works well when there are applications running on the server with each using a different data source.

The web.config might look like this:

<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
<connectionStrings>
<clear />
<remove name="LocalSqlServer"/>
<add name="LocalSqlServer" connectionString="Data Source=servername;Initial Catalog=database;User ID=user;Password=password;" providerName="System.Data.SqlClient" />
<remove name="ConnectionString1"/>
<add name="ConnectionString1" connectionString="Data Source=servername1;Initial Catalog=database1;User ID=user;Password=password;" providerName="System.Data.SqlClient" />
<remove name="ConnectionString2"/>
<add name="ConnectionString2" connectionString="Data Source=servername2;Initial Catalog=database2;User ID=user;Password=password;" providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>

|||

harshal_shravgi:

I am using SQL Server 2000 database in my VB.Net project.

My VB.net application is installed one two machines in different cities i.e. there are two separate databases.

First machine's IP address is say 202.33.44.55 and other machine IP address is say 203.33.55.66. Now I want to use each others databases i.e. I want to set their database group.

So that in my VB.Net's application which is installed on 202.33.44.55 I could see data of the 203.33.55.66 by just changing the connection string

There is nothing dynamic in this Asp.net needs an account in the second server which should be added to SQL Server and the database, so you need to create a Windows account for Asp.net in the second server and grant that account permissions to SQL Server and the database. Hope this helps.

|||

Actually I want to set their database groups and as I said those two machines are not in my LAN. Those two machines are in different cities.

When I try setup a new sql group then I cann't see 203.33.55.66 from 202.33.44.55.

Above two IP addresses will be live IP address.

|||

harshal_shravgi:

Actually I want to set their database groups and as I said those two machines are not in my LAN. Those two machines are in different cities.

When I try setup a new sql group then I cann't see 203.33.55.66 from 202.33.44.55.

Above two IP addresses will be live IP address.

I have told you what you need to do location is not relevant if you do it your application will run, some of these security issues are created by developers who thinks database servers should let their application run without valid permissions.

|||

In my code I am using following connection string -

Provider=SQLOLEDB.1;Data Source=202.33.44.55;Initial Catalog= SLV2;UserId=sa;Password=;

It gives error that database does not exist.

What should I give datasource that database does not exist on my machine i.e. 203.33.55.66? And as I said when I tried to setup a new group in SQL Server then its not showing me that other machine database.

So I think its not the issue of coding, firstly I should be able set a group of databases in SQL Server then only I will be able to use it in my code.

|||

Hi,

You also need to check if the SQL Server instance you're connecting to is the default instance on 203.33.55.66. Is the instance on the default port 1433?

Please also check if the database named SLV2 exists on that machine and if sa with no password is a valid user name.

You can test this with SQL Server management studio or with a UDL file. Here are the steps creating a UDL file.

1. Create a text file on your desktop and rename it to XXX.udl
2. Double click on the file and a Data Link Property dialog box will be shown.
3. Setup the connection and use Test Connection button to test it.

HTH.

|||

Exactly when I tried to connect using .UDL file I didn't found that other machine.

So what to do now?

|||

harshal_shravgi:

Exactly when I tried to connect using .UDL file I didn't found that other machine.

So what to do now?

You need to create a domain account for Asp.net then grant that account permissions in SQL Server and database, sa is not a Windows account.

http://support.microsoft.com/kb/328306

|||

Hi,

And you can also connect to the database using SQL authentication. But the server should enable SQL authentication first.

HTH.