Connecting to SQL Server with Remote Access Disabled using RazorSQL via ASP

Many times, web hosting providers include SQL Server as part of their hosting packages, but do not allow users to connect to the SQL Server database from a remote location. This forces users to use a web based application to access their SQL Server database instance, or resort to complex solutions such as SSH tunneling.

Also, in some cases, ISPs may block port 1433, the default port used by SQL Server, because of a virus that used that port in an exploit.

The RazorSQL SQL Server ASP Bridge allows users to connect to a SQL Server database with the simple deployment of a ASP page, and allows them to get the added functionality of a desktop application like RazorSQL as opposed to the limited functionality of web-based database access. And since the bridge operates over standard HTTP or HTTPS ports, there are no firewall or ISP blocking issues to worry about.

The RazorSQL SQL Server ASP Bridge provides the ability for users to interact with a SQL Server database without making a direct connection to the database. Instead, the user connects to the RazorSQL SQL Server ASP Bridge, which then connects to the database. The RazorSQL SQL Server ASP Bridge is a ASP program that can be deployed on any web server that supports ASP and has the necessary ADO libraries installed. Since the ASP page resides on the hosting provider's web server, even if remote access is disabled on the SQL Server database, the ASP page will still be able to connect.

As an added bonus, if your web server supports SSL, the RazorSQL SQL Server ASP Bridge can be used to securely transmit your data from your desktop to your SQL Server database instance.

Requirements

The RazorSQL SQL Server ASP Bridge requires a web server with ASP support and the ADO libraries for SQL Server must be installed and configured.

Deployment

All of the RazorSQL PHP and ASP bridges are located in the following zip file. The SQL Server ASP bridge is named razorsql_sqlserver_bridge.asp.

RazorSQL Bridges

The RazorSQL SQL Server ASP Bridge file is also located here:

<RazorSQL Install Directory>/bridge/razorsql_sqlserver_bridge.asp

To deploy the bridge, simply upload this file to a ASP enabled web server. This can be done using either an FTP client or your web hosting provider's control panel.

Also included is a test page to check to see if SQL Server can be accessed via the web server. The test page is located here:

<RazorSQL Install Directory>/bridge/test_sqlserver_setup.asp

To test the SQL Server setup, simply copy this file to a ASP enabled web server, access the page, and follow the instructions.

Security

The RazorSQL SQL Server ASP Bridge can work via either HTTP or HTTPS. If retrieving sensitive data, it is recommended that HTTPS be used.

The RazorSQL SQL Server ASP Bridge provides the ability to supply a bridge password. Only calls to the brige that include a correct password will be handled. The bridge ships with a default password of radmin. It is recommended that this password be changed. To change the password, simply replace the radmin in the following line of the bridge ASP page with another password.

checkPassword = "radmin"

Accessing the Bridge

Once the bridge ASP page is deployed to a web server, the bridge can be called via either HTTP or HTTPS if your web server supports SSL.

To set up a connection profile that communicates via the SQL Server ASP bridge, simply access the Connections -> Add Connection Profile menu option, select SQL Server as the database type and RazorSQL SQL Server ASP Bridge as the connection type. There are then fields displayed such as Bridge URL, Bridge Password, etc. Listed below is information on these fields.

Bridge URL: Enter the web address of the deployed bridge page in this field. For example, http://www.myhost.com/razorsql_sqlserver_bridge.asp

Bridge Password: Enter the bridge password in this field. The default is radmin. Enter your own custom password if you modified the bridge php page to change the default password.

Login: This is your SQL Server user id.

Password: This is your password to your SQL Server database.

Host or IP Address: This is the host or IP address of your SQL Server database. This is different depending on the web host. For example, the host name could be localhost, or an actual server name.

Port: This is the port the SQL Server database is listening on. The default is 1433, but many times web hosts change the port to another value.

Database Name: The name of the SQL Server database you wish to connect to.

Basic Auth. / .htaccess Info.: For added security, some users protect the razorsql bridge page with some form of basic authentication protection such as using a .htaccess file to require a user name and password before the web server will allow access to the bridge page. If the bridge page is protected in this manner, click the button next to this field and add the appropriate user name and password to access the bridge page.

Since the bridge does not hold on to connections, but instead opens and closes connections for each call, the bridge cannot do multiple fetches to the database for the same query. Because of this, there is a field call "max rows to return" located in the add connection profile screen when setting up the bridge. The value of this field tells the bridge the maximum number of rows to fetch per query. If set too high, this could cause performance problems.

Limitations

There are certain features of RazorSQL that are not available when using the RazorSQL SQL Server ASP Bridge. The Execute Procedure/Function tool is not available. Also, all connections via the bridge are set to auto commit since connections are not held onto by the bridge.