SQL / MySQL Remote Server

Here are the details about how to use SQL / MySQL Database from the remote server including example code (PHP and C#).

SQL server 2000 remote connection system

Start > All Programs > Microsoft SQL Server > Profiler

Click Alias Tab and Add..

For custom port (some stupid isp blocked 1433 default port)


client side:

Server side: (need restart SQL server to active new port)

Open Enterprise Manager:

Here is our alias DB to connect from remote place!

Intentionally I added router screen shot here (end part), because it is less important and I hope every IT guys knows this. BUT important thing is we must add the router port first else ALIAS server can not be registered with local server

Code:

C# example

using System;

using System.Data.SqlClient;

namespace ConsoleCSharp

{

            class DataReader_SQL

            {

[STAThread]

static void Main(string[] args)

{

try

{

SqlConnection thisConnection = new SqlConnection(@”Network Library=DBMSSOCN;Data Source=24.251.165.241,3249;database=TMSdemo;User id=nipu;Password=abc123;”);

thisConnection.Open();

SqlCommand thisCommand = thisConnection.CreateCommand();

thisCommand.CommandText = “SELECT bankId,bankName FROM BankInfo“;

SqlDataReader thisReader = thisCommand.ExecuteReader();

while (thisReader.Read())

{

Console.WriteLine(“\t{0}\t{1}”, thisReader[“bankId“], thisReader[“bankName“]);

}

thisReader.Close();

thisConnection.Close();

}

catch (SqlException e)

{

Console.WriteLine(e.Message);

}

}

}

}

PHP example

<?

$server=”24.251.165.241,3249“;

$username=”nipu“;

$password=”abc123”;

$sqlconnect=mssql_connect($server, $username, $password) ;

mssql_select_db(TMSdemo”,$sqlconnect);

$sqlquery=”SELECT bankId,bankName FROM BankInfo;”;

$results= mssql_query($sqlquery);

while ($row=mssql_fetch_array($results)){

echo $row[0].”  “.$row[1].”\n”;}

mssql_close($sqlconnect);

?>

MySql server remote connection system

Server:

Just give permission to remote client machine who intend to connect your server.

Example:

mysql> grant select,insert,update, delete on TMSdemo.* to [email protected] IDENTIFIED BY “pass”;

Note: 63.253.165.124 is a remote client and need to add 3306 port need to be forwarded.

Client:

<?php

$connection = mysql_connect(“28.251.165.241”,”kuddos”,”pass”)

or die(“couldn’t connect with to the server”);

mysql_select_db(TMSdemo”,$connection) or die (“couldn’t select database”);

$sql=”SELECT * FROM `BankInfo`;“;

$sql_result=mysql_query($sql,$connection) or die(“couldn’t execute query”);

while ($row=mysql_fetch_array($sql_result)){

echo $row[0].”  “.$row[1].”\n”;}

mysql_close($connection);

?>

Note: 28.251.165.241 is server machine.