Help, Transferring XML data under SSL

I need some advice on how to securely transfer data between two servers.
Here is the situation. We have two sql servers that hold student data.
I have full access to my sql server, but only write access to the main
sql server on campus. I hope to use XML and SSL to transfer student
data to the main server.

As for generating the XML, I'll have an asp.net page set up in a secure
directory that will generate the data in xml format. The data people
will access this page and get the xml file with all the data.

What's the best way to turn a sql server table into XML format?

What about the SSL part? Do all we need to do is access it with
https:// just like you would a typical secure page?

As for accessing the page, how will the data people authenticate to my
server? What's the best way? I'm sure they'll want to script it so
that this can be done automatically.

--
..
http://sf-f.org, weblog and search engine for fans and writers of
speculative fiction.
..

re: Help, Transferring XML data under SSL

I hope that you are using SQL 2000 and then you can use the FOR XML things.
If not, then you should just return all the rows from ADO in your asp page
and generate a response.write with your xml. Access the page over https and
make sure that you are validated via nt authentication for your web page.

The web page should accept query string params so you can modify your return
as needed.

Yes, just access your page just like any other https page.

Authentication to your server should be via NT security. Setup a username
and password for them in your user manager and have them use that.

Scripting this is not a problem. Use xp_cmdshell and call out to an
application called "curl". Curl is free for download and it installs like
nothing. Once this is installed you can call your xp_cmdshell to curl, then
use your favorite method to import the data. Here's one example:

1) Put your ASP page on server1 that will return XML.

2) The ASP page will just create an ADO connection to your database and
response.write out the xml of your choice.

3) The ASP page will be protected by an NT user (not anonymous) and will be
https.

4) The sql server (server2) will have curl installed on it.

5) Create a BAT file that will accept %1 %2 %3 %4 %5 and such parameters
that will use curl with the passed in username, password, website, and
such.

6) The BAT file will use the curl command and redirect " > " to another file
the actual XML that is returned.

7) The BAT file will then call osql with the parameters needed to import the
XML into server2.

8) done.

.... please note that XML is a little inefficient in this manner and if you
are just attempting to pass data from one machine to the next the BEST way
to do this is not use XML but rather have your asp page generate a SQL
script that osql can just execute. So your BAT file just calls osql with
the correct logon params and executes the script that it just downloaded.
The script would start with "begin tran" and then all the inserts or
whatever statements then end with "commit". Simple.



jimb wrote:
[color=blue]
> I need some advice on how to securely transfer data between two servers.
> Here is the situation. We have two sql servers that hold student data.
> I have full access to my sql server, but only write access to the main
> sql server on campus. I hope to use XML and SSL to transfer student
> data to the main server.
>
> As for generating the XML, I'll have an asp.net page set up in a secure
> directory that will generate the data in xml format. The data people
> will access this page and get the xml file with all the data.
>
> What's the best way to turn a sql server table into XML format?
>
> What about the SSL part? Do all we need to do is access it with
> https:// just like you would a typical secure page?
>
> As for accessing the page, how will the data people authenticate to my
> server? What's the best way? I'm sure they'll want to script it so
> that this can be done automatically.
>[/color]

re: Help, Transferring XML data under SSL

See below.

Best regards
Michael

"jimb" <spam@spamity.spam> wrote in message
news:Xns9569529944AA0spamspamityspam@68.12.19.6...[color=blue]
>I need some advice on how to securely transfer data between two servers.
> Here is the situation. We have two sql servers that hold student data.
> I have full access to my sql server, but only write access to the main
> sql server on campus. I hope to use XML and SSL to transfer student
> data to the main server.
>
> As for generating the XML, I'll have an asp.net page set up in a secure
> directory that will generate the data in xml format. The data people
> will access this page and get the xml file with all the data.
>
> What's the best way to turn a sql server table into XML format?[/color]

Use FOR XML on your SELECT statement. If you cannot write statements
directly against the database, use the SQLXML component.
[color=blue]
> What about the SSL part? Do all we need to do is access it with
> https:// just like you would a typical secure page?[/color]

Yes. But your webserver needs to be configured to accept SSL connections
(enable the SSL port etc.).
[color=blue]
> As for accessing the page, how will the data people authenticate to my
> server? What's the best way? I'm sure they'll want to script it so
> that this can be done automatically.
>
> --
> .
> http://sf-f.org, weblog and search engine for fans and writers of
> speculative fiction.
> .[/color]