Remotely Connect to Sql Server

Hi
i am trying to registering the remote sql server (MMC Sql EnterPrise
Manager) which is outside our Network . i.e the machine is located at ISP.
it is giving me error SQL server does not exist or access denied.
i am specifying the IP address of remote machine to whom i want to access
remotely,
(SQL Server Properties ) under connection tab - (configuration done to
remote sql server)
i had selected the option of allowing other sql server to connect remotely
to this sql server using RPC
even i am not able register remote sql server in my local sql server.
Can anyone help me out what exactly we have to do for remote access of sql
server.
Thanks
MiteshCan you ping the SQL server?
Can you telnet to port 1433 from your side ?
Is there a firewall in between ?
Make sure you can connect on port 1433 otherwise it will not work , you
would then need to check whats blocking that port somewhere!
HTH
Dylan
"Mitesh Patel" <mitesh@.viraat.com> wrote in message
news:OJExAfU0EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Hi
> i am trying to registering the remote sql server (MMC Sql EnterPrise
> Manager) which is outside our Network . i.e the machine is located at ISP.
> it is giving me error SQL server does not exist or access denied.
> i am specifying the IP address of remote machine to whom i want to access
> remotely,
> (SQL Server Properties ) under connection tab - (configuration done to
> remote sql server)
> i had selected the option of allowing other sql server to connect remotely
> to this sql server using RPC
> even i am not able register remote sql server in my local sql server.
> Can anyone help me out what exactly we have to do for remote access of sql
> server.
> Thanks
> Mitesh
>
>|||when i ping to ip address it is showing destination host unreachable (this
is due to internet provider had stopped ping )
yes i am able to do telnet to port 1433 after passing the telnet command at
port 1433. the telnet window shows connecting... and then telnet screen
becomes blank (might it been connected and waiting for commands )
remote server is located in data center then firewall will be there
............
Thanks
Mitesh
"Dylan Kruger" <carmellobear1@.hotmail.com> wrote in message
news:uaq5Y8U0EHA.1396@.tk2msftngp13.phx.gbl...
> Can you ping the SQL server?
> Can you telnet to port 1433 from your side ?
> Is there a firewall in between ?
> Make sure you can connect on port 1433 otherwise it will not work , you
> would then need to check whats blocking that port somewhere!
> HTH
> Dylan
>
> "Mitesh Patel" <mitesh@.viraat.com> wrote in message
> news:OJExAfU0EHA.1408@.TK2MSFTNGP10.phx.gbl...
ISP.[vbcol=seagreen]
access[vbcol=seagreen]
remotely[vbcol=seagreen]
sql[vbcol=seagreen]
>|||You should also make sure that it's the public IP you try to access or you
have the proper NAT settings in the Firewall in both ends. It could also be
the settings for your VPN connection (if that's what your using ) that needs
to be checked.
Regards
Steen
Dylan Kruger wrote:[vbcol=seagreen]
> Can you ping the SQL server?
> Can you telnet to port 1433 from your side ?
> Is there a firewall in between ?
> Make sure you can connect on port 1433 otherwise it will not work ,
> you would then need to check whats blocking that port somewhere!
> HTH
> Dylan
>
> "Mitesh Patel" <mitesh@.viraat.com> wrote in message
> news:OJExAfU0EHA.1408@.TK2MSFTNGP10.phx.gbl...sql

Remotely Connect to Sql Server

Hi
i am trying to registering the remote sql server (MMC Sql EnterPrise
Manager) which is outside our Network . i.e the machine is located at ISP.
it is giving me error SQL server does not exist or access denied.
i am specifying the IP address of remote machine to whom i want to access
remotely,
(SQL Server Properties ) under connection tab - (configuration done to
remote sql server)
i had selected the option of allowing other sql server to connect remotely
to this sql server using RPC
even i am not able register remote sql server in my local sql server.
Can anyone help me out what exactly we have to do for remote access of sql
server.
Thanks
Mitesh
Can you ping the SQL server?
Can you telnet to port 1433 from your side ?
Is there a firewall in between ?
Make sure you can connect on port 1433 otherwise it will not work , you
would then need to check whats blocking that port somewhere!
HTH
Dylan
"Mitesh Patel" <mitesh@.viraat.com> wrote in message
news:OJExAfU0EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Hi
> i am trying to registering the remote sql server (MMC Sql EnterPrise
> Manager) which is outside our Network . i.e the machine is located at ISP.
> it is giving me error SQL server does not exist or access denied.
> i am specifying the IP address of remote machine to whom i want to access
> remotely,
> (SQL Server Properties ) under connection tab - (configuration done to
> remote sql server)
> i had selected the option of allowing other sql server to connect remotely
> to this sql server using RPC
> even i am not able register remote sql server in my local sql server.
> Can anyone help me out what exactly we have to do for remote access of sql
> server.
> Thanks
> Mitesh
>
>
|||when i ping to ip address it is showing destination host unreachable (this
is due to internet provider had stopped ping )
yes i am able to do telnet to port 1433 after passing the telnet command at
port 1433. the telnet window shows connecting... and then telnet screen
becomes blank (might it been connected and waiting for commands )
remote server is located in data center then firewall will be there
............
Thanks
Mitesh
"Dylan Kruger" <carmellobear1@.hotmail.com> wrote in message
news:uaq5Y8U0EHA.1396@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Can you ping the SQL server?
> Can you telnet to port 1433 from your side ?
> Is there a firewall in between ?
> Make sure you can connect on port 1433 otherwise it will not work , you
> would then need to check whats blocking that port somewhere!
> HTH
> Dylan
>
> "Mitesh Patel" <mitesh@.viraat.com> wrote in message
> news:OJExAfU0EHA.1408@.TK2MSFTNGP10.phx.gbl...
ISP.[vbcol=seagreen]
access[vbcol=seagreen]
remotely[vbcol=seagreen]
sql
>
|||You should also make sure that it's the public IP you try to access or you
have the proper NAT settings in the Firewall in both ends. It could also be
the settings for your VPN connection (if that's what your using ) that needs
to be checked.
Regards
Steen
Dylan Kruger wrote:[vbcol=seagreen]
> Can you ping the SQL server?
> Can you telnet to port 1433 from your side ?
> Is there a firewall in between ?
> Make sure you can connect on port 1433 otherwise it will not work ,
> you would then need to check whats blocking that port somewhere!
> HTH
> Dylan
>
> "Mitesh Patel" <mitesh@.viraat.com> wrote in message
> news:OJExAfU0EHA.1408@.TK2MSFTNGP10.phx.gbl...

Remotely Connect to MSDE

Hi
I just installed MSDE over my Windows 2003 box and facing an small problem.
I installed it with SQL security mode and also set a password for user "sa".
Everything looks just fine using "osql" as it uses local address.
But I can't connect to my server from another computer using VS.NET Server Explorer.
It seems as MSDE is set to reject connections from outside by default.
My question is how to enable it so I can connect using my "sa" account.
Appreciate your help in advance.OK. are you trying to connect by IP address or server name? do you have a firewall installed?|||Since I am connecting from outside of the server, I am using IP address.
And also I don't have any firewall installed.|||at a guess I'd say you have only windows authentication enabled. not 100% sure how to change that in MSDE, but I'll scout around and see.

Remote ODBC Connection problem \

Hi

I think i have a very unique issue ...

Running 2 machines
Win2000 / WinXP
Both have Sql / Service packs
Identical ODBC Setup

The issue is as follows :
Both machines CAN access The internet server through ODBC ! (so i dont
think the problem lies here)
I CAN connect to a server on the internet through a local web page on
W2k Box; BUT NOT on the WINXP machine ?
I think there is a IIS Prob maybe .. Checked iUser Security settings on
both and are identical

Any 1 have a suggestion ?

Thanks in advancelighthammer (lighthammer@.webmail.co.za) writes:
> I think i have a very unique issue ...
> Running 2 machines
> Win2000 / WinXP
> Both have Sql / Service packs
> Identical ODBC Setup
> The issue is as follows :
> Both machines CAN access The internet server through ODBC ! (so i dont
> think the problem lies here)
> I CAN connect to a server on the internet through a local web page on
> W2k Box; BUT NOT on the WINXP machine ?
> I think there is a IIS Prob maybe .. Checked iUser Security settings
> on both and are identical

I'm having a hard time to understand what you are connecting to. Is
"the internet server" and "a server on the Internet" the same machine?
Where does IIS comes into the picture? (Warning: I know next to nothing
about IIS.)

What error messages do you get?

Could the Windows firewall have anything to do with it?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Remote Database updates take long time to complete

Hi
I have have two linked SQL Servers and I am trying to get remote writes
working correctly (fast).

I have configured the DB link on both machines to:
Point at each others DB.
I have security set up to map each others server logins
and Server Options: Collation Compatible, Data Access, RPC, RPC Out, Use
Remote Collation all checked

My problem is that when a SP performs

Begin Transaction

Update Local Table

Update Remote Table

Commit Tran

It takes several seconds to complete. (about 7 seconds not acceptable to
us)
This is due to the remote update - how can I improve the response time?

example of a stored procedures that takes time

where ACSMSM is a remote (linked) SQL Server.

procedure [psm].ams_Update_VFE
@.strResult varchar(8) = 'Failure' output,
@.strErrorDesc varchar(512) = 'SP Not Executed' output,
@.strVFEID varchar(16),
@.strDescription varchar(64),
@.strVFEVirtualRoot varchar(255),
@.strVFEPhysicalRoot varchar(255),
@.strAuditPath varchar(255),
@.strDefaultBranding varchar(16),
@.strIPAddress varchar(23)
as
declare @.strStep varchar(32)
declare @.trancount int

Set XACT_ABORT ON
set @.trancount = @.@.trancount
set @.strStep = 'Start of Stored Proc'

if (@.trancount = 0)
BEGIN TRANSACTION mytran
else
save tran mytran

/* start insert sp code here */

set @.strStep = 'Write VFE to MSM'

update
ACSMSM.msmprim.msm.VFECONFIG
set
DESCRIPTION = @.strDescription,
VFEVIRTUALROOT = @.strVFEVirtualRoot,
VFEPHYSICALROOT = @.strVFEPhysicalRoot,
AUDITPATH = @.strAuditPath,
DEFAULTBRANDING = @.strDefaultBranding,
IPADDRESS = @.strIPAddress
where
VFEID = @.strVFEID;

set @.strStep = 'Write VFE to PSM'

update
ACSPSM.psmprim.psm.VFECONFIG
set
DESCRIPTION = @.strDescription,
VFEVIRTUALROOT = @.strVFEVirtualRoot,
VFEPHYSICALROOT = @.strVFEPhysicalRoot,
AUDITPATH = @.strAuditPath,
DEFAULTBRANDING = @.strDefaultBranding,
IPADDRESS = @.strIPAddress
where
VFEID = @.strVFEID

/* end insert sp code here */

if (@.@.error <> 0)
begin
rollback tran mytran
set @.strResult = 'Failure'
set @.strErrorDesc = 'Fail @. Step :' + @.strStep + ' Error : ' + @.@.Error
return -1969
end
else
begin
set @.strResult = 'Success'
set @.strErrorDesc = ''
end
-- commit tran if we started it

if (@.trancount = 0)
commit tran

return 0Steve Thorpe (stephenthorpe@.nospam.hotmail.com) writes:
> It takes several seconds to complete. (about 7 seconds not acceptable to
> us)
> This is due to the remote update - how can I improve the response time?

Have you tried moving the updates of the remote table to a stored
procedures that runs on the remote server, and then call that stored
procedure remotely? I would expect that to reduce the ping-pong over
the network.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I have found the cause of the problem:

'For linked server DELETEs or UPDATEs, SQL Server retrieves data from the
table, performs any filtering that is necessary, and then performs the
deletes or updates through the OLEDB rowset. This processing can result in a
round-trip to the remote server for each row that is to be deleted or
updated. '

The solution is:

EXEC remserver.master.dbo.sp_executesql
@.stmt = N'DELETE sometable WHERE col1 = 10 OR col1 = 20'

EXEC localserver.master.dbo.sp_executesql
@.stmt = N'DELETE sometable WHERE col1 = 10 OR col1 = 20'

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns94004CF4CD0FYazorman@.127.0.0.1...
> Steve Thorpe (stephenthorpe@.nospam.hotmail.com) writes:
> > It takes several seconds to complete. (about 7 seconds not acceptable
to
> > us)
> > This is due to the remote update - how can I improve the response time?
> Have you tried moving the updates of the remote table to a stored
> procedures that runs on the remote server, and then call that stored
> procedure remotely? I would expect that to reduce the ping-pong over
> the network.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Remote connection question

Hi

I have a server with windows 2003 and SQL Server 2005 installed.

When I use the client on the server to connect to the database (using windows authentication) it does this no problem. And when remotely connecting (using SQL authentication) it 'seems' to do it no problem as well.

However in "SQL Sevrer Management Studio" on the server when I connect it displays the server name with a nice green circle with a little arrow inside. On the remote computer it 'seems' to connect but I don't get the green circle with the arrow next to the server (just get a white circle) - Hope this all makes sense.

I get the feeling I can 'see' the server but can't connect properly. Is this common behaviour (white circle) or have I made a 'school boy error' somewhere along the way!?

Any help/suggestions would be much appreciated. Thanks in advance!

Regards

Toby

Would you go to Surface Area Configuration to enable Remote Connection?|||

Surface Area Configuration is setup correctly (on the server):

Database Engine/Remote Connections => Local and Remote Connections - using both TCP/IP and named pipes.

|||

Just to clarify where I am and what I have tried

Basically on the remote computer I log in with "sa" and it says it connects, but it doesn't look/behave like its connected. And on the server I log in with windows authentication and it connects no problem with full functionality.

The server is setup to accept remote connections. It uses both windows and sql authentication. I have tried playing around with the TCP/IP port numbers. I've stopped the windows firewall in administrative tools/services on both the server and remote machine. Also added sql drivers under the ODBC Data Source Administrator for both the client & server.

I am pretty much out of ideas and the problem is really starting to bug me. I can't be the only person getting this bug/problem!?

Thanks in advance
Toby

remote connection problem

Hi

I'm having a problem connecting to a remote sql server 2000, A, database using asp.net 2.
Im able to connect to another remote database, B, fine but when i connect A i get the following error:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.)

I figure it might be a server problem. I tried using all sorts of connection strings.

Thanks

Hi,

Did you manage to solve this problem? I have the same problem and I am also finding it hard to find anyone to help me.

Thanks

|||

I'm also having this issue, Server is 2003 IIS 6.0. .NET 2.0 I am writing an application hosted on this server that connects to another 2003 server running sqlserver 2005 standard. There is no Firewall enabled on the db server. Remote connections are enabled on the dbserver and allowed through 1433. I've added local service to the security permissions too as well as the username and password that i'm using in the connection string.

I've gone through various posts regarding this error but to no avail. here's the full error as seen in my browser and the connection string i'm using in web.config

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.)

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.)

web.config

<connectionStrings>
<add name="ConnectionString" connectionString="data source=dbserver;Network=DBMSSOCN;initial catalog=ius_hr;Trusted_Connection=yes;user id=username;password=password;packet size=4096"/>
</connectionStrings>