Remote access to SQL server via website 2

[Biffy13]

Hi.

I am trying to find information on connecting to a remote SQL server, such as ADO connections, security issues, SQL server admin for setting this up, etc.

What I need to do is thus:
On webserver 1 (Windows 2000) is SQL server (2000). I need for other websites (on other webservers dotted around the globe) to be able to dynamically pick up the data stored in the SQL database and then display it on their websites (so as to provide a seamless integration).

I understand that this can be done but I am not sure how. Could someone please point me in the right direction?

Also, would the other websites need to do anything special to connect to my webserver 1, such as setting something on their webserver? I am not sure if they all have admin access to their servers - what I need is to give them a connection string as a bit of code that they can then plug into their websites.

Thanks in advance

 

[Ian2]

Hi Biffy

We use an include file in almost every application that contains the following:

<code>
set cnn = createObject(&quot;ADODB.Connection&quot
set rs = createObject(&quot;ADODB.Recordset&quot

strConn = &quot;DRIVER={SQL SERVER};Server=servername;Database=databasename;uid=SQLusername;password=SQLpassword;Network=networkname; OLE DB Services=-2&quot;

cnn.Provider = &quot;SQLOLEDB&quot;
cnn.Open strConn
</code>

For better security (and to reduce number of SQL users to be created) look at Application Login, using sp_setapprole. The application owner can then give permissions to the database objects without allowing direct access, but only through the application.

Hope this helps.
Ian

 

[Biffy13]

Hi Ian

Thanks for your help.

I do still have a question, do you not have to specify an IP address and port number in the connection (I think I have seen this done somewhere)? If I understand it correctly, your connection string is for a SQL server connected to a network whereas I need a connection from a completely remote server into my webserver. (Forgive me if I have mis-understood this.)

The application login idea is interesting and something I will have to look into.

My major concern is having to allow access to anyone to my database even with a username and password. To me this is just asking for trouble!

Thanks again for your help

Jo

 

[harmmeijer]

Right click on your desktop and choose new text document.
Rename the New Text Document.txt to New Text Document.udl.
double click on the udl file.
Now you have the gui to create your database connection and test it.
In the provider tab choose Microsoft OLE DB for SQL server
In the connection tab fill out the server's ip address (the top field)
In the all tab under Extended Properties fill out PORT=1433 (1433 is the default port but if the sql server admin is smart he/she would have changed this as well as the computers that are allowed to access)

 

[Biffy13]

Well I never - I haven't come across a neat trick like this before, thanks Harmmeijer!