VPN connection to an SQL Server and/or database

Hi All

Can we connect remotely using SQL Server Enterprise Manager from our
home PCs using a VPN connection, across the Internet, to an instance
of SQL Server(or a database on it) that resides on an ISP's Server?
Can this be done using a VPN IP connection? I believe a static IP is
required for this?

Thanks in Advance, MichaelIn article <8a68c00d.0310180406.41af69bf@.posting.google.com>,
MikeTheBike@.tpgi.com.au says...
> Hi All
> Can we connect remotely using SQL Server Enterprise Manager from our
> home PCs using a VPN connection, across the Internet, to an instance
> of SQL Server(or a database on it) that resides on an ISP's Server?
> Can this be done using a VPN IP connection? I believe a static IP is
> required for this?
> Thanks in Advance, Michael

You don't need a fixed IP to use VPN, but if the destination has a fixed
IP it makes it easier to find the next time.

If the SQL server doesn't have a fixed IP then you are asking for
trouble.

It's standard practice for workers at home to have dynamic IP's and the
office to have fixed IP's.

--
--
spamfree999@.rrohio.com
(Remove 999 to reply to me)|||Hi

There is not reason why this should not work, although if you are not
part of the domain or on a trusted domain then you will probably have
to use SQL authentication.

I don't think a static IP is absolutely necessary, but it may be
required by the firewall/router for authentication.

John

MikeTheBike@.tpgi.com.au (Michael Holberton) wrote in message news:<8a68c00d.0310180406.41af69bf@.posting.google.com>...
> Hi All
> Can we connect remotely using SQL Server Enterprise Manager from our
> home PCs using a VPN connection, across the Internet, to an instance
> of SQL Server(or a database on it) that resides on an ISP's Server?
> Can this be done using a VPN IP connection? I believe a static IP is
> required for this?
> Thanks in Advance, Michael|||Hi John,

Thanks for your response. I couldn't set up a VPN connection but I was
able to connect directly, using the IP Address, using Enterprise
Manager. I had a brainwave earlier today and discovered I could also
setup an ODBC connection using the IP Address and hence link to Tables
from Access.

Regards Michael

jrm_bell@.hotmail.com (John Bell) wrote in message news:<59c6a934.0310181029.1ffcf5ed@.posting.google.com>...
> Hi
> There is not reason why this should not work, although if you are not
> part of the domain or on a trusted domain then you will probably have
> to use SQL authentication.
> I don't think a static IP is absolutely necessary, but it may be
> required by the firewall/router for authentication.
> John|||Hi

If you are not using the VPN and have access restricted to those connecting
though the VPN then anyone can connect!!

John

"Michael Holberton" <MikeTheBike@.tpgi.com.au> wrote in message
news:8a68c00d.0310200113.2586be5d@.posting.google.c om...
> Hi John,
> Thanks for your response. I couldn't set up a VPN connection but I was
> able to connect directly, using the IP Address, using Enterprise
> Manager. I had a brainwave earlier today and discovered I could also
> setup an ODBC connection using the IP Address and hence link to Tables
> from Access.
> Regards Michael
> jrm_bell@.hotmail.com (John Bell) wrote in message
news:<59c6a934.0310181029.1ffcf5ed@.posting.google.com>...
> > Hi
> > There is not reason why this should not work, although if you are not
> > part of the domain or on a trusted domain then you will probably have
> > to use SQL authentication.
> > I don't think a static IP is absolutely necessary, but it may be
> > required by the firewall/router for authentication.
> > John

...unable to begin a distributed transaction

I have two SQL Server 2003 servers with SQL Express 2005 installed. The
servers are not on the same network (and not on a VPN), so they are
communicating across the Internet. On both servers, MSDTC is properly
configured, the Windows firewall is turned off, credentials are setup
properly, etc. The servers are linked. Suppose the servers are named A and B.
When I execute the following query from within SQL Server Management Studio
Express on server A, it works:
begin transaction
set xact_abort on
select * from B.MyDatabase.Dbo.MyTable
commit transaction
But when I execute the following query, it does not work:
begin transaction
set xact_abort on
update B.MyDatabase.Dbo.MyTable set MyColumn = 'NewValue' where MyColumn =
'OldValue'
select * from B.MyDatabase.Dbo.MyTable
commit transaction
I get the following error:
OLE DB provider "SQLNCLI" for linked server "B" returned message "No
transaction is active.".
Msg 7391, Level 16, State 2, Line 3
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "B" was unable to begin a distributed transaction.
Can someone help me with this issue?
Corey Young
Hi
I see you have posted this in many locations without much success! It sounds
like you have your servers open to the internet without a firewall, this
would not be recommended!
Did you check out
http://support.microsoft.com/default...b;en-us;306212 on how to
debug 7391 errors. I suggest that you try and get it working on two servers
on the same network before making the issue wider. My guess would be that it
could be a permissions issue. Can you do your updating with a remote call to
a stored procedure with the EXECUTE ... AT linked server command?
John
"Young, Corey" wrote:

> I have two SQL Server 2003 servers with SQL Express 2005 installed. The
> servers are not on the same network (and not on a VPN), so they are
> communicating across the Internet. On both servers, MSDTC is properly
> configured, the Windows firewall is turned off, credentials are setup
> properly, etc. The servers are linked. Suppose the servers are named A and B.
> When I execute the following query from within SQL Server Management Studio
> Express on server A, it works:
> begin transaction
> set xact_abort on
> select * from B.MyDatabase.Dbo.MyTable
> commit transaction
> But when I execute the following query, it does not work:
> begin transaction
> set xact_abort on
> update B.MyDatabase.Dbo.MyTable set MyColumn = 'NewValue' where MyColumn =
> 'OldValue'
> select * from B.MyDatabase.Dbo.MyTable
> commit transaction
> I get the following error:
> OLE DB provider "SQLNCLI" for linked server "B" returned message "No
> transaction is active.".
> Msg 7391, Level 16, State 2, Line 3
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "B" was unable to begin a distributed transaction.
> Can someone help me with this issue?
> --
> Corey Young

...unable to begin a distributed transaction

I have two SQL Server 2003 servers with SQL Express 2005 installed. The
servers are not on the same network (and not on a VPN), so they are
communicating across the Internet. On both servers, MSDTC is properly
configured, the Windows firewall is turned off, credentials are setup
properly, etc. The servers are linked. Suppose the servers are named A and B
.
When I execute the following query from within SQL Server Management Studio
Express on server A, it works:
begin transaction
set xact_abort on
select * from B.MyDatabase.Dbo.MyTable
commit transaction
But when I execute the following query, it does not work:
begin transaction
set xact_abort on
update B.MyDatabase.Dbo.MyTable set MyColumn = 'NewValue' where MyColumn =
'OldValue'
select * from B.MyDatabase.Dbo.MyTable
commit transaction
I get the following error:
OLE DB provider "SQLNCLI" for linked server "B" returned message "No
transaction is active.".
Msg 7391, Level 16, State 2, Line 3
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "B" was unable to begin a distributed transaction.
Can someone help me with this issue?
Corey YoungHi
I see you have posted this in many locations without much success! It sounds
like you have your servers open to the internet without a firewall, this
would not be recommended!
Did you check out
http://support.microsoft.com/defaul...kb;en-us;306212 on how to
debug 7391 errors. I suggest that you try and get it working on two servers
on the same network before making the issue wider. My guess would be that it
could be a permissions issue. Can you do your updating with a remote call to
a stored procedure with the EXECUTE ... AT linked server command?
John
"Young, Corey" wrote:

> I have two SQL Server 2003 servers with SQL Express 2005 installed. The
> servers are not on the same network (and not on a VPN), so they are
> communicating across the Internet. On both servers, MSDTC is properly
> configured, the Windows firewall is turned off, credentials are setup
> properly, etc. The servers are linked. Suppose the servers are named A and
B.
> When I execute the following query from within SQL Server Management Studi
o
> Express on server A, it works:
> begin transaction
> set xact_abort on
> select * from B.MyDatabase.Dbo.MyTable
> commit transaction
> But when I execute the following query, it does not work:
> begin transaction
> set xact_abort on
> update B.MyDatabase.Dbo.MyTable set MyColumn = 'NewValue' where MyColumn =
> 'OldValue'
> select * from B.MyDatabase.Dbo.MyTable
> commit transaction
> I get the following error:
> OLE DB provider "SQLNCLI" for linked server "B" returned message "No
> transaction is active.".
> Msg 7391, Level 16, State 2, Line 3
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "B" was unable to begin a distributed transaction.
> Can someone help me with this issue?
> --
> Corey Young

...unable to begin a distributed transaction

I have two SQL Server 2003 servers with SQL Express 2005 installed. The
servers are not on the same network (and not on a VPN), so they are
communicating across the Internet. On both servers, MSDTC is properly
configured, the Windows firewall is turned off, credentials are setup
properly, etc. The servers are linked. Suppose the servers are named A and B.
When I execute the following query from within SQL Server Management Studio
Express on server A, it works:
begin transaction
set xact_abort on
select * from B.MyDatabase.Dbo.MyTable
commit transaction
But when I execute the following query, it does not work:
begin transaction
set xact_abort on
update B.MyDatabase.Dbo.MyTable set MyColumn = 'NewValue' where MyColumn = 'OldValue'
select * from B.MyDatabase.Dbo.MyTable
commit transaction
I get the following error:
OLE DB provider "SQLNCLI" for linked server "B" returned message "No
transaction is active.".
Msg 7391, Level 16, State 2, Line 3
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "B" was unable to begin a distributed transaction.
Can someone help me with this issue?
--
Corey YoungHi
I see you have posted this in many locations without much success! It sounds
like you have your servers open to the internet without a firewall, this
would not be recommended!
Did you check out
http://support.microsoft.com/default.aspx?scid=kb;en-us;306212 on how to
debug 7391 errors. I suggest that you try and get it working on two servers
on the same network before making the issue wider. My guess would be that it
could be a permissions issue. Can you do your updating with a remote call to
a stored procedure with the EXECUTE ... AT linked server command?
John
"Young, Corey" wrote:
> I have two SQL Server 2003 servers with SQL Express 2005 installed. The
> servers are not on the same network (and not on a VPN), so they are
> communicating across the Internet. On both servers, MSDTC is properly
> configured, the Windows firewall is turned off, credentials are setup
> properly, etc. The servers are linked. Suppose the servers are named A and B.
> When I execute the following query from within SQL Server Management Studio
> Express on server A, it works:
> begin transaction
> set xact_abort on
> select * from B.MyDatabase.Dbo.MyTable
> commit transaction
> But when I execute the following query, it does not work:
> begin transaction
> set xact_abort on
> update B.MyDatabase.Dbo.MyTable set MyColumn = 'NewValue' where MyColumn => 'OldValue'
> select * from B.MyDatabase.Dbo.MyTable
> commit transaction
> I get the following error:
> OLE DB provider "SQLNCLI" for linked server "B" returned message "No
> transaction is active.".
> Msg 7391, Level 16, State 2, Line 3
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server "B" was unable to begin a distributed transaction.
> Can someone help me with this issue?
> --
> Corey Young