Few days back I came across an intertesting scenario and was asked the above question, so I thought of sharing with you guys...
Scenario: Let’s say, you are using SQL Server
replication to replicate data between two sites which are connected over the
public network via VPN. You are able to successfully initialize the
subscription using the initial snapshot delivered over the FTP. Due to security
reasons, you would like to replace FTP by an encrypted solution (FTPS, SFTP, FTP over SSH). Now the question is which of
the these encrypted solutions (FTPS,SFTP,FTP
over SSH) is compliant with Microsoft SQL Server replication?
To answer the
above question:
In windows, the Secured version of FTP is “FTPS”. Whereas in UNIX
System, it was called “FTP over SSH” or “FTPS”. The "FTPS"
abbreviation is often mistakenly used to specify some kind of Secure FTP, by
which people most often mean FTPS. Another (similar) mistake is that FTPS is
thought to be some kind of FTP over SSL. In fact, FTPS is an abbreviation of
"SSH File Transfer Protocol." This is not FTP over SSL and not FTP
over SSH (which is also technically possible, but very rare).
Now I’ve successfully configured my FTPS site for snapshot folder
and ensured that the following conditions are met:
1.
The Publisher must be running SQL Server.
2.
An FTP/FTPS
server must be enabled and configured to point to the
C:\Program Files\Microsoft SQL
Server\MSSQL\Repldata\Ftp directory (default) or network share which you
specified while configuring your distributor or in the specific publication
properties SNAPHOT page.
For more information about configuring an FTP/FTPS server, see the Internet Information Services (IIS)
documentation.
3.
The publication is configured to use an FTP snapshot: FTP Snapshot
page in publication properties.
Note: This option is for delivery of Snapshot to subscriber using
the FTP site/address you specify, not for generation of snapshot on FTP/FTPS site.
But I’m
unable to successfully configure SQL Server replication. Though I’m able to
create either PUSH/PULL subscription but unable to initialize the subscription by
delivering an initial snapshot over the FTPS.
The
replication (Distribution/Merge) agent fails with the below error:
The process could not connect to FTP
site '<ftp_site_address>' using port n
(Source: MSSQL_REPL20032)
The operation times out.
You are
able to access the FTPS site from both the distributor and subscriber machine but
the initial snapshot delivery is still failing with above error.
You must be
wondering what could be the reason? Am I missing sometime? Did I misconfiguration
something? and so on….
The cause
behind this error is, SQL Server
replication, till the latest release of SQL Server 2008 R2 doesn’t have any
infrastructure to support FTPS. So what are my options, I’m concerned about
data security while replicating the data over the wire on public network.
You can only use
FTP for an initial snapshot delivery on to the subscriber in SQL Server
replication. So far the security of data is concerned, the solution is twofold:
1) If you are concerned about the
security of initial snapshot delivery via FTP using VPN on public network, You
use the following workaround which will require some custom code to be
implemented by you:
a.
Generate
an initial snapshot.
b.
Compress
the snapshot folder, and encrypt the zip file. Or you can write your own custom
code to encrypt the snapshot folder or compressed snapshot folder.
c.
Push
the encrypted zip file to the FTP and transfer it across to the subscriber.
d.
De-crypt
the zip file and uncompress the snapshot folder locally on the subscriber.
e.
Use –AltSnapshotFolder distribution/Merge
agent command line parameter by specifying the location of the snapshot folder
copied locally on the subscriber (step d above)
f.
Run
the Distribution/Merge agent to deliver an initial snapshot to initialize the
subscription.
2) To ensure secure replication of
on-going changes (TDS Streams) after the initial snapshot delivery, Use SSL
Encryption on the distribution server.
I hope this would
help you and/or could have clarified your doubts or have answered questions
that you may have related to use of secured version of FTP (FTPS) in SQL Server
replication. I look forward to your comments.
Happy Learning..!
Thanks for the information! I have been doing research on server replication. I have not come across anything else quite as detailed. Thanks for sharing the post!
ReplyDelete