Non-trusted transactional replication with remote distributor?

I'm trying to find out if it is possible to set up transactional replication
over the internet (non-trusted domains) with a remote distributor which is
also a subscriber, and one additional subscriber. Initial snapshot should be
delivered using FTP. Is this possible or do the distributor and publisher
always both need direct access to the snapshot folder?
Note that we very much prefer not to use the so-called pass-through
autentication hack for the SQL Server Agent accounts, and preferably all
replication agents should run using SQL Authentication. In other words assume
the distributor/subscribers have no (non-FTP) access whatsoever to the
publisher's filesystem, and vice versa. Is this possible or will I run into
connectivity problems?
Thanks in advance.
It should be possible, but its madness for high volume applications. The
problem is if your link between your publisher and subscriber goes down your
tlog will start getting large in a hurry. Not to mention the network hop
between the publisher and the distributor/subscriber.
Why do you feel the need to have a remote distributor? Normally you do this
to offload the distribution agent from the publisher.
You don't really need to worry about pass through authentication when using
FTP as well.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jeremy" <Jeremy@.discussions.microsoft.com> wrote in message
news:C8917D4D-88F6-4CC6-AF06-80A8CB1C2F85@.microsoft.com...
> I'm trying to find out if it is possible to set up transactional
replication
> over the internet (non-trusted domains) with a remote distributor which is
> also a subscriber, and one additional subscriber. Initial snapshot should
be
> delivered using FTP. Is this possible or do the distributor and publisher
> always both need direct access to the snapshot folder?
> Note that we very much prefer not to use the so-called pass-through
> autentication hack for the SQL Server Agent accounts, and preferably all
> replication agents should run using SQL Authentication. In other words
assume
> the distributor/subscribers have no (non-FTP) access whatsoever to the
> publisher's filesystem, and vice versa. Is this possible or will I run
into
> connectivity problems?
> Thanks in advance.
>
sql

No support for SQL Server Authentication and SSIS !!!!

Hi

I want to manage my servers from a central location and have the ability to manage my packges too. Unfortunatley my servers are across domains which means I have to use SQL Server Login, which isn't a bad thing. However for some bizzare reason I can't connect to Integration Services using this method, only windows authentication which is a complete pain in the butt from a management stand point as I have to copy packages to various servers rather than add them from a central point.

Does anyone know if this bug/feature or what ever Microsoft is calling it will be fixed or know of any workarounds.

Thanks

I have no problem with SSIS and SQL-Server-Authentification.|||

I do not even get the option to change the authentication method to SQL Server and the box is greyed out so I can't change the Windows authentication user either.

I do not have any problems inside SSIS, only when I try to connect to the SSIS server from within SQL Server Management Studio in a different domain.

|||I am assuming you are trying to connect to Integration Services from SQL Server Management Studio. You are right, you cannot change the authentication method to SQL Server authentication in this case. This is because it is a separate service, and sql server authentication does not work in this case.|||

This is indeed problematic... if developers for SSIS or even SSAS for that matter have workstations in a domain different from the domain that hosts the Server you cannot deploy packages to the server, you can't create/access SSAS Dbs either.

This means then that any developer must work in the same domain as the server they are trying to deploy to or access (SQL Server mgmt. studio to monitor SSIS packages or to even open a SSAS DB in Visual Studio)

Anyone find a way around this?

Joe.

|||

I just found this the hard way. I work for client in a different domain to my workstation and I cannot deploy the app. Like Joe, if someone has already researched this, plxz let us know.

I also have problems with restricted VPN access with locked down ports at the client.

Thanks

|||

I have exactly the same problem and also found out the hard way. In fact my problem is a little worse - our client doesn't even HAVE a domain (yes they are a little backward)!

I managed to get the package installed on the client's server after much trial and error (by copying the VS solution to the server, editing my connection managers and debugging then deploying from there. Thankfully they had installed VS on the server).

The bigger problem is now that the clients can never run the SSIS package remotely. What is worse is that SSAS has the same problem - my clients can't browse their cube from Excel or SSMS! The only way I can think of around this is to remote desktop into the server, which is obviously bad.

Thanks in advance for any help / suggestions / comments.

|||

If you want to execute a SSIS package remotely, set up a SQL Server Agent job to execute the package. That job can be executed from wherever you like by anyone with SSMS.

-Jamie

|||

Aranda wrote:

The bigger problem is now that the clients can never run the SSIS package remotely.

SSIS Service does not provide remote execution, as Jamie suggested - use Agent Jobs instead.

The usual way to get package installed on a machine you can access remotely is to build deployment utility (option in VS SSIS project), copy the folder with deployment utility and run it on the target machine.

What you'll be missing due to lack of domain is
1) ability to remotely store packages, and
2) ability to remotely monitor packages as they are being run, and to terminate these packages.

|||

I just came across this.

I to am frustrated that you can only connect to the SSIS service via windows authentication. I am attempting to connect from my computer(a client running Management Studio) not directly from the machine 'hosting' the SQL Server and SSIS services. When I do this, I obviously have to set myself up with an operating system(Win 2k3 server )account on the server machine but I am only able to connect to SSIS if I am a member of the administrators group. Is this true? Can I connect as someone with less rights/privledges?

No support for SQL Server Authentication and SSIS !!!!

Hi

I want to manage my servers from a central location and have the ability to manage my packges too. Unfortunatley my servers are across domains which means I have to use SQL Server Login, which isn't a bad thing. However for some bizzare reason I can't connect to Integration Services using this method, only windows authentication which is a complete pain in the butt from a management stand point as I have to copy packages to various servers rather than add them from a central point.

Does anyone know if this bug/feature or what ever Microsoft is calling it will be fixed or know of any workarounds.

Thanks

I have no problem with SSIS and SQL-Server-Authentification.|||

I do not even get the option to change the authentication method to SQL Server and the box is greyed out so I can't change the Windows authentication user either.

I do not have any problems inside SSIS, only when I try to connect to the SSIS server from within SQL Server Management Studio in a different domain.

|||I am assuming you are trying to connect to Integration Services from SQL Server Management Studio. You are right, you cannot change the authentication method to SQL Server authentication in this case. This is because it is a separate service, and sql server authentication does not work in this case.|||

This is indeed problematic... if developers for SSIS or even SSAS for that matter have workstations in a domain different from the domain that hosts the Server you cannot deploy packages to the server, you can't create/access SSAS Dbs either.

This means then that any developer must work in the same domain as the server they are trying to deploy to or access (SQL Server mgmt. studio to monitor SSIS packages or to even open a SSAS DB in Visual Studio)

Anyone find a way around this?

Joe.

|||

I just found this the hard way. I work for client in a different domain to my workstation and I cannot deploy the app. Like Joe, if someone has already researched this, plxz let us know.

I also have problems with restricted VPN access with locked down ports at the client.

Thanks

|||

I have exactly the same problem and also found out the hard way. In fact my problem is a little worse - our client doesn't even HAVE a domain (yes they are a little backward)!

I managed to get the package installed on the client's server after much trial and error (by copying the VS solution to the server, editing my connection managers and debugging then deploying from there. Thankfully they had installed VS on the server).

The bigger problem is now that the clients can never run the SSIS package remotely. What is worse is that SSAS has the same problem - my clients can't browse their cube from Excel or SSMS! The only way I can think of around this is to remote desktop into the server, which is obviously bad.

Thanks in advance for any help / suggestions / comments.

|||

If you want to execute a SSIS package remotely, set up a SQL Server Agent job to execute the package. That job can be executed from wherever you like by anyone with SSMS.

-Jamie

|||

Aranda wrote:

The bigger problem is now that the clients can never run the SSIS package remotely.

SSIS Service does not provide remote execution, as Jamie suggested - use Agent Jobs instead.

The usual way to get package installed on a machine you can access remotely is to build deployment utility (option in VS SSIS project), copy the folder with deployment utility and run it on the target machine.

What you'll be missing due to lack of domain is
1) ability to remotely store packages, and
2) ability to remotely monitor packages as they are being run, and to terminate these packages.

|||

I just came across this.

I to am frustrated that you can only connect to the SSIS service via windows authentication. I am attempting to connect from my computer(a client running Management Studio) not directly from the machine 'hosting' the SQL Server and SSIS services. When I do this, I obviously have to set myself up with an operating system(Win 2k3 server )account on the server machine but I am only able to connect to SSIS if I am a member of the administrators group. Is this true? Can I connect as someone with less rights/privledges?

No support for SQL Server Authentication and SSIS !!!!

Hi

I want to manage my servers from a central location and have the ability to manage my packges too. Unfortunatley my servers are across domains which means I have to use SQL Server Login, which isn't a bad thing. However for some bizzare reason I can't connect to Integration Services using this method, only windows authentication which is a complete pain in the butt from a management stand point as I have to copy packages to various servers rather than add them from a central point.

Does anyone know if this bug/feature or what ever Microsoft is calling it will be fixed or know of any workarounds.

Thanks

I have no problem with SSIS and SQL-Server-Authentification.|||

I do not even get the option to change the authentication method to SQL Server and the box is greyed out so I can't change the Windows authentication user either.

I do not have any problems inside SSIS, only when I try to connect to the SSIS server from within SQL Server Management Studio in a different domain.

|||I am assuming you are trying to connect to Integration Services from SQL Server Management Studio. You are right, you cannot change the authentication method to SQL Server authentication in this case. This is because it is a separate service, and sql server authentication does not work in this case.|||

This is indeed problematic... if developers for SSIS or even SSAS for that matter have workstations in a domain different from the domain that hosts the Server you cannot deploy packages to the server, you can't create/access SSAS Dbs either.

This means then that any developer must work in the same domain as the server they are trying to deploy to or access (SQL Server mgmt. studio to monitor SSIS packages or to even open a SSAS DB in Visual Studio)

Anyone find a way around this?

Joe.

|||

I just found this the hard way. I work for client in a different domain to my workstation and I cannot deploy the app. Like Joe, if someone has already researched this, plxz let us know.

I also have problems with restricted VPN access with locked down ports at the client.

Thanks

|||

I have exactly the same problem and also found out the hard way. In fact my problem is a little worse - our client doesn't even HAVE a domain (yes they are a little backward)!

I managed to get the package installed on the client's server after much trial and error (by copying the VS solution to the server, editing my connection managers and debugging then deploying from there. Thankfully they had installed VS on the server).

The bigger problem is now that the clients can never run the SSIS package remotely. What is worse is that SSAS has the same problem - my clients can't browse their cube from Excel or SSMS! The only way I can think of around this is to remote desktop into the server, which is obviously bad.

Thanks in advance for any help / suggestions / comments.

|||

If you want to execute a SSIS package remotely, set up a SQL Server Agent job to execute the package. That job can be executed from wherever you like by anyone with SSMS.

-Jamie

|||

Aranda wrote:

The bigger problem is now that the clients can never run the SSIS package remotely.

SSIS Service does not provide remote execution, as Jamie suggested - use Agent Jobs instead.

The usual way to get package installed on a machine you can access remotely is to build deployment utility (option in VS SSIS project), copy the folder with deployment utility and run it on the target machine.

What you'll be missing due to lack of domain is
1) ability to remotely store packages, and
2) ability to remotely monitor packages as they are being run, and to terminate these packages.

|||

I just came across this.

I to am frustrated that you can only connect to the SSIS service via windows authentication. I am attempting to connect from my computer(a client running Management Studio) not directly from the machine 'hosting' the SQL Server and SSIS services. When I do this, I obviously have to set myself up with an operating system(Win 2k3 server )account on the server machine but I am only able to connect to SSIS if I am a member of the administrators group. Is this true? Can I connect as someone with less rights/privledges?