ServersAccess database on Remote SQL

Access database on Remote SQL

ServerWatch content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.




by Jessie Cui

Is that feasible to dynamically create DSN and
check data via internet from any computer? This small application,
named as SQLmap, is valuable for those who is
responsible for building or maintaining a web site and SQL Server
database. This apllication helps to access all servers and then
access all databases stored in each server. Then with the interface
provided you can view tables, records in each table through browser
from any computer, You can even write SQL query to retrieve data
from table that meet the criteria specified.
I have developed
many intranet and internet projects using ASP and SQL Server. After
projects are delivered, it is highly possible that customers suggest
further revisions for the deliverred project as they get more
experience with the project. Any revision should be tested by
accessing remote database. In most cases, we need to immediately
know table data on remote SQL Server for successful test. For
example, I finished shopping cart project with SQL 6.5 database and
ASP in Dallas then ftp the whole project and database to Whasington
D.C. Later, I was asked to modify some pages related to database. As
I need real data to test ASP pages, in this situation, we can ftp
SQLmap (list on table below) to Server in D.C. So
we can view table data on remote SQL Server through
internet.

Allthough there are a bunch of softwares that allow you
perform the same tasks, such as PC Anywhere, and Remote Possible,
etc., you are limited on the machines with those softwares
installed. Therefore, SQLmap extends your
accessibity to all server databases through internet.
Is that feasible to dynamically create DSN and check data via internet from any computer?

I use following files in SQLmap application.

File Name: File Type: Description:
1)   default.htm html file (1k) login page to server machine
2)  sqlDBlist.asp asp file  (2k) database list on SQL server
3)  List.asp asp file  (5k) table, view list and SQL query form
4)  showTable.asp asp file  (3k) table structure and table data
5)  querySQL.asp asp file  (1k) query result
6) SQLmap.zip zip file  (5k) first five files in this zip file
 

Demo

Download
   SQLmap.zip


sqlDBlist.asp  users may connect to
several servers on their network, depending on the information they
need. Here, we create refrence to the SQL Server OLE Object. SQL
Server 6.5 client utilities provide a number of OLE Object that can
be used to access SQL Server service and list all database on the
server user submitted.


‘ Refernce SQL Server OLE
Server
‘( SQL Server must be registered on your system for
this to work)

Set OServer =
Server.CreateObject(“sqlole.sqlserver”)
gServer =
request.form(“ServerName”)
gLogin =
request.form(“Login”)
gPassword =
request.form(“Password”)
OServer.Connect
gServer,gLogin,gPassword
%>

Database Map

       

           
response.write “


           
response.write “


response.write “


response.write


       
%>
       

Server
Name
Database
Name
Login ID
Password
” & gServer &


           
%>
           

           
           
For Each SQLDB In
OServer.Databases
               
If Not SQLDB.SystemObject
Then
               
Response.Write “” &
SQLDB.Name
                   
End
If
           
Next
           
response.write
“”
           
response.write “”
           
response.write “”
           
response.write “”
           
oServer.close
           
set Oserver =
Nothing
           
%>
           

       

       
       
response.write “


& gLogin & “

        if
gPassword=”” then response.write ” ” else
response.write
gPassword
       
response.write



list.asp is respond to submitting the
previous page. We create  the connection definition ‘on the
fly’, using the server name and databse name submitted by the user.
So we do not need  to set up ODBC Data Source Name
(DSN) for each database.  Also, user is allowed to
input SQL query (read only) in this page and get result from remote
SQL server database.



‘ Refernce SQL
Server OLE Server


Dim OServer
Dim
gServer
Dim gLogin
Dim sDatabase
Dim gPassword
Set
OServer = Server.CreateObject(“sqlole.sqlserver”)
gServer =
trim(request.form(“ServerName”))
sDatabase =
trim(request.form(“DatabaseName”))
gLogin =
trim(request.form(“Login”))
gPassword =
trim(request.form(“Password”))

‘ Connect to the
server

OServer.Connect
gServer,gLogin,gPassword

‘to get all tables and views
from request.form(“DatabaseName”)

Dim rsTables, sql,
rsViews
Set rsTables =
Server.CreateObject(“ADODB.recordset”)
Set rsViews =
Server.CreateObject(“ADODB.recordset”)
table_sql = “SELECT
Name FROM Sysobjects WHERE TYPE=’U’ ORDER BY 1”
view_sql =
“SELECT Name FROM Sysobjects WHERE TYPE=’V’ ORDER BY
1”

rsTables.Open table_sql, _
“Driver={SQL
Server};Server=” & gServer & “;uid=” & gLogin
& _
    “;pwd=” & gPassword &
“;Database=” & sDatabase & “;DSN=;”
rsViews.Open
view_sql, _
“Driver={SQL Server};Server=” & gServer
& “;uid=” & gLogin & _
   
“;pwd=” & gPassword & “;Database=” & sDatabase
& “;DSN=;”

%>

Tables<br /> in the Database<br />

response.write


response.write “


response.write “


response.write “


       
%>

Server Name
Database Name
Login ID
Password
” & gServer &
” & sDatabase
& “
” &
gLogin & “

& gPassword &
” 

Please
Select
A Table Name from the
List:

 
   

Table Name
name=”TableNames”
size=”1″>
Set oSQLdb = oServer.Databases(
sDatabase
)
           

           
‘another way to get
tables
           

For Each oSQLTable In oSQLdb.Tables
   
Response.Write ”    oSQLTable.Name & “”” > ” &
_
    oSQLTable.Name &
“”
Next
%>response.write
“”
response.write “”
response.write “”
response.write “”
response.write “”
oServer.Close
%>
  
Execute a
SQL
Statement

Tables

Wend %>
   

Views

Wend %>

   

SQL
Query:

       
>


showTable.asp show table structure and
data using the number of Field objects in thedata
source’s Fields collection .

           

               

           

       

       

       

bgcolor=”yellow” >
                   

                       

                   

       

           
           
RS.MoveNext
%>
               

       

   


Dim DBConn
Dim
RS
Dim gServer
Dim gLogin
Dim sDatabase
Dim
gPassword
Dim iCount
Dim iRow
Set RS =
Server.CreateObject(“ADODB.recordset”)
gServer =
request.form(“ServerName”)
sDatabase =
request.form(“DatabaseName”)
gLogin =
request.form(“Login”)
gPassword = request.form(“Password”)

sTable = request.form(“TableNames”)
RS.Open “select *
from ” & sDatabase & “.dbo.” &
request.form(“TableNames”), _
“Driver={SQL Server};Server=”
& gServer & “;uid=” & gLogin &
_
    “;pwd=” & gPassword &
“;Database=” & sDatabase & “;DSN=;”

%>

Tables<br /> in the Database<br /> Selected

Here is your
Table

Table
Name:

   

       

           

           

           

           

           

       

           
           
iRow =
RS.Fields.Count
           
For iCount = 0 to (iRow –
1)
               
Set Fld =
RS.Fields(iCount)
               
response.write “



               
response.write “


               
response.write “


               
response.write “


               
If Int(Fld.Precision) >= 255
Then
               
response.write “



               
Else
               
response.write “


               
End
If
               
If Int(Fld.NumericScale) >= 255
Then
               
response.write “


            

               
Else

               
response.write “


               
End
If
               
response.write


           
Next
           
%>
       

Field
Name
           
Type
Length
Precision
Scale
” & Fld.Name &
” & Fld.Type &
” & Fld.ActualSize &
0 ” & Int(Fld.Precision) &
0 ” & Int(Fld.NumericScale) &

   
   

   


       
m=0
    RS.MoveFirst
%>
    <table border="1" cols="”>
       

                               

                       

Set RS = Nothing
%>


querySQL.asp show result of SQL query
user submitted.


‘***********Establish a
connection to the database***************
gServer =
trim(request.form(“ServerName”))
sDatabase =
trim(request.form(“DatabaseName”))
gLogin =
trim(request.form(“Login”))
gPassword =
trim(request.form(“Password”))
Dim Conn
Set Conn =
Server.CreateObject(“ADODB.Connection”)
   
ConnStr=”Driver={SQL Server};Server=” & gServer &
“;uid=” & gLogin & “;pwd=” & gPassword &
“;Database=” & sDatabase & “;DSN=;”

response.write connstr
    Conn.Open
ConnStr
sql = Request.Form(“querySQL”)

Set rs =
Server.CreateObject(“ADODB.Recordset”)
rs.Open sql,
Conn
%>   

SQL
Query Results

Your Query:

            For j = 0
to rs.Fields.Count-1 %>

           

       

   
Dim i
    Do While Not rs.EOF
%>
   

        for i = 0
to rs.Fields.Count-1
%>
           

   

   

        Loop %>

               

Get the Free Newsletter!

Subscribe to Daily Tech Insider for top news, trends & analysis

Latest Posts

Related Stories