Connecting to SQL Server 2005 - Not Allow Remote Connections

I use ASP.NET 2.0 and VS.NET 2005.

I have the following expression in my code:

string connectionString = "server=\'localhost\'; user id=\'sa\'; password=\'\'; Database=\'Northwind\'";

When I executed the code I have the following error message:

"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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)."

The Microsoft Access was successfully connected in the Server Explorer window.

How do I solve this problem?

Thanks,

Andy.

Try this thread for the two links you need to get it going. Hope this helps.

http://forums.asp.net/thread/1381596.aspx

|||

From your link, I found the following link http://support.microsoft.com/default.aspx?scid=kb%3bEN-US%3b914277. I have followed all the instructions from that page, but I still receive the same error message.

I am not sure which version of SQL I am using. From the "start" -> "All Programs", I can only see "Microsoft SQL Server". It was installed, when I installed Microsoft Visual Studio 2005 Professional.

Andy.

|||If you have the professional you should have the Developer edition CD separately, copy the content to your hard drive and install it as a named instance and then use the instructions for the developer edition. Hope this helps.

connecting to SQL Server 2000 from a VB.net app using ADO.net

Hi
I can connect from my app to sql server on the local lan, but I can't connect over the
Internet. I can't create a remote connection in Enterprise Manager. However, I can
ping the IP address of the server just fine.
The app uses the following connect string:
Provider=SQLOLEDB;User
ID=******;Password=******;Server=******;Database=* *****;Network Address=******
Where 'Network Address' = the IP address of the machine where SQL Server is
installed.
This connect string works great from any machine connected to the same lan as the
SQL Server machine. However, whenever I try to connect across the Internet (from my
home machine, using the exact same software and connect string), I take the
following error:
Client unable to establish connection ConnectionOpen(CreateFile()
Question 1: Does anybody have any suggestions as to why I can connect over the LAN
but not over the Internet? I suspect some networking thing, like a firewall on the
lan where I am trying to connect, but I am not a networking expert.
Question 2: Does anyone have any opinion about whether this is a valid DBIO
technique? Most databases that are accessed over the Internet have thin-client
interfaces implemented in front of them. I have never tried to do this from a Windows
fat-client, and I suspect very poor performance and excess resource utilization on
the server
Any help or article will be appriciated
In terms of connectivity issues over the Internet, firewall
issues would be my first guess. You may want to take a look
at the following article:
INF: TCP Ports Needed for Communication to SQL Server
Through a Firewall
http://support.microsoft.com/?id=287932
In terms of the application issues, it depends on the app.
The resource problems on the database server are most often
due to coding issues, not really what type of client is
used. Either can perform well or perform poorly depending on
how they are coded.
-Sue
On Thu, 15 Apr 2004 09:56:07 -0700, "Lisa Jones"
<anonymous@.discussions.microsoft.com> wrote:

>Hi
>I can connect from my app to sql server on the local lan, but I can't connect over the
>Internet. I can't create a remote connection in Enterprise Manager. However, I can
>ping the IP address of the server just fine.
>The app uses the following connect string:
>Provider=SQLOLEDB;User
>ID=******;Password=******;Server=******;Database= ******;Network Address=******
>Where 'Network Address' = the IP address of the machine where SQL Server is
>installed.
>This connect string works great from any machine connected to the same lan as the
>SQL Server machine. However, whenever I try to connect across the Internet (from my
>home machine, using the exact same software and connect string), I take the
>following error:
>Client unable to establish connection ConnectionOpen(CreateFile()
>Question 1: Does anybody have any suggestions as to why I can connect over the LAN
>but not over the Internet? I suspect some networking thing, like a firewall on the
>lan where I am trying to connect, but I am not a networking expert.
>Question 2: Does anyone have any opinion about whether this is a valid DBIO
>technique? Most databases that are accessed over the Internet have thin-client
>interfaces implemented in front of them. I have never tried to do this from a Windows
>fat-client, and I suspect very poor performance and excess resource utilization on
>the server
>Any help or article will be appriciated
sqlsql

connecting to SQL Server 2000 from a VB.net app using ADO.net

Hi
I can connect from my app to sql server on the local lan, but I can't connec
t over the
Internet. I can't create a remote connection in Enterprise Manager. However,
I can
ping the IP address of the server just fine.
The app uses the following connect string:
Provider=SQLOLEDB;User
ID=******;Password=******;Server=******;
Database=******;Network Address=****
**
Where 'Network Address' = the IP address of the machine where SQL Server is
installed.
This connect string works great from any machine connected to the same lan a
s the
SQL Server machine. However, whenever I try to connect across the Internet (
from my
home machine, using the exact same software and connect string), I take the
following error:
Client unable to establish connection ConnectionOpen(CreateFile()
Question 1: Does anybody have any suggestions as to why I can connect over t
he LAN
but not over the Internet? I suspect some networking thing, like a firewall
on the
lan where I am trying to connect, but I am not a networking expert.
Question 2: Does anyone have any opinion about whether this is a valid DBIO
technique? Most databases that are accessed over the Internet have thin-clie
nt
interfaces implemented in front of them. I have never tried to do this from
a Windows
fat-client, and I suspect very poor performance and excess resource utilizat
ion on
the server
Any help or article will be appriciatedIn terms of connectivity issues over the Internet, firewall
issues would be my first guess. You may want to take a look
at the following article:
INF: TCP Ports Needed for Communication to SQL Server
Through a Firewall
http://support.microsoft.com/?id=287932
In terms of the application issues, it depends on the app.
The resource problems on the database server are most often
due to coding issues, not really what type of client is
used. Either can perform well or perform poorly depending on
how they are coded.
-Sue
On Thu, 15 Apr 2004 09:56:07 -0700, "Lisa Jones"
<anonymous@.discussions.microsoft.com> wrote:

>Hi
>I can connect from my app to sql server on the local lan, but I can't conne
ct over the
>Internet. I can't create a remote connection in Enterprise Manager. However
, I can
>ping the IP address of the server just fine.
>The app uses the following connect string:
>Provider=SQLOLEDB;User
> ID=******;Password=******;Server=******;
Database=******;Network Address=***
***
>Where 'Network Address' = the IP address of the machine where SQL Server is
>installed.
>This connect string works great from any machine connected to the same lan
as the
>SQL Server machine. However, whenever I try to connect across the Internet
(from my
>home machine, using the exact same software and connect string), I take the
>following error:
>Client unable to establish connection ConnectionOpen(CreateFile()
>Question 1: Does anybody have any suggestions as to why I can connect over
the LAN
>but not over the Internet? I suspect some networking thing, like a firewall
on the
>lan where I am trying to connect, but I am not a networking expert.
>Question 2: Does anyone have any opinion about whether this is a valid DBIO
>technique? Most databases that are accessed over the Internet have thin-cli
ent
>interfaces implemented in front of them. I have never tried to do this from
a Windows
>fat-client, and I suspect very poor performance and excess resource utiliza
tion on
>the server
>Any help or article will be appriciated

connecting to sql server

i want to connect to an remote sql server 2005 from my office , where there remote sql server is in USA and my office is at india, i have done all the setting on the server where i have enabled remote connections and tcp/ip, i need to connect to the sqlserver using sql server management studio at my office, is it not possible to connect to an remote sql server without VPN

Few things to check

1. Does US SQL server machine has Firewall enabled? (considering opening the corresponding port)

2. Does INDIA machine has Firewall enabled?

3. If Windows Login is used to connect to US SQL server, is the login granted?

4. If you still see connection errors, you may consider to post the error message, error number and connecting scenario to "SQL Server Data Access " forum.

Thanks.

This posting is provided AS IS with no warranties, and confers no rights

connecting to sql remote database

i am trying to connect to sql server in a remote database by using windows
authentication. I tried to connect using php and cgi. The apache service
under which i am running my cgi or php is under localsystem account. If i
change it to administrator account, i am able to connect to remote sql server
using windows authentication. But due to some limitations i want the apache
server/service to be run only on localsystem account, but to connect to
remote sql database using windows authentication. ( I dont want to use mixed
mode in SQL)Is it possible to connect using cgo or php?If it is possible what
is the connection string to be used? Or is SQLAuthenticatin compulsary be
used in connectng to remote sql server?.
the connection string that i am using are given below:
"Provider=sqloledb;Network Library=DBMSSOCN;Data Source=PMP3,1433;Initial
Catalog=PMP_V3_0;Integrated Security=SSPI";
strConnection = _T("Driver={SQL Server};Server=MyServerName;"
"Trusted_Connection=yes;"
"Database=MyDatabaseName;");
You can connect to a remote SQL Server using Windows Authentication, except
when your Apache service uses the LocalSystem account. The LocalSystem
account can not connect to any resources that are on other computers. The
best solution would be to create a network account with the necessary
permissions, and run the Apache service using that account. Then you can use
Windows Authentication only.
Jacco Schalkwijk
SQL Server MVP
"uday" <uday@.discussions.microsoft.com> wrote in message
news:1C3054A7-5B6F-4510-91DF-80EB5BC5873D@.microsoft.com...
>i am trying to connect to sql server in a remote database by using windows
> authentication. I tried to connect using php and cgi. The apache service
> under which i am running my cgi or php is under localsystem account. If i
> change it to administrator account, i am able to connect to remote sql
> server
> using windows authentication. But due to some limitations i want the
> apache
> server/service to be run only on localsystem account, but to connect to
> remote sql database using windows authentication. ( I dont want to use
> mixed
> mode in SQL)Is it possible to connect using cgo or php?If it is possible
> what
> is the connection string to be used? Or is SQLAuthenticatin compulsary be
> used in connectng to remote sql server?.
> the connection string that i am using are given below:
> "Provider=sqloledb;Network Library=DBMSSOCN;Data Source=PMP3,1433;Initial
> Catalog=PMP_V3_0;Integrated Security=SSPI";
> strConnection = _T("Driver={SQL Server};Server=MyServerName;"
> "Trusted_Connection=yes;"
> "Database=MyDatabaseName;");
>
>
>

connecting to sql remote database

i am trying to connect to sql server in a remote database by using windows
authentication. I tried to connect using php and cgi. The apache service
under which i am running my cgi or php is under localsystem account. If i
change it to administrator account, i am able to connect to remote sql serve
r
using windows authentication. But due to some limitations i want the apache
server/service to be run only on localsystem account, but to connect to
remote sql database using windows authentication. ( I dont want to use mixed
mode in SQL)Is it possible to connect using cgo or php?If it is possible wha
t
is the connection string to be used? Or is SQLAuthenticatin compulsary be
used in connectng to remote sql server?.
the connection string that i am using are given below:
"Provider=sqloledb;Network Library=DBMSSOCN;Data Source=PMP3,1433;Initial
Catalog=PMP_V3_0;Integrated Security=SSPI";
strConnection = _T("Driver={SQL Server};Server=MyServerName;"
"Trusted_Connection=yes;"
"Database=MyDatabaseName;");You can connect to a remote SQL Server using Windows Authentication, except
when your Apache service uses the LocalSystem account. The LocalSystem
account can not connect to any resources that are on other computers. The
best solution would be to create a network account with the necessary
permissions, and run the Apache service using that account. Then you can use
Windows Authentication only.
Jacco Schalkwijk
SQL Server MVP
"uday" <uday@.discussions.microsoft.com> wrote in message
news:1C3054A7-5B6F-4510-91DF-80EB5BC5873D@.microsoft.com...
>i am trying to connect to sql server in a remote database by using windows
> authentication. I tried to connect using php and cgi. The apache service
> under which i am running my cgi or php is under localsystem account. If i
> change it to administrator account, i am able to connect to remote sql
> server
> using windows authentication. But due to some limitations i want the
> apache
> server/service to be run only on localsystem account, but to connect to
> remote sql database using windows authentication. ( I dont want to use
> mixed
> mode in SQL)Is it possible to connect using cgo or php?If it is possible
> what
> is the connection string to be used? Or is SQLAuthenticatin compulsary be
> used in connectng to remote sql server?.
> the connection string that i am using are given below:
> "Provider=sqloledb;Network Library=DBMSSOCN;Data Source=PMP3,1433;Initial
> Catalog=PMP_V3_0;Integrated Security=SSPI";
> strConnection = _T("Driver={SQL Server};Server=MyServerName;"
> "Trusted_Connection=yes;"
> "Database=MyDatabaseName;");
>
>
>

Connecting to SQL error 40

I'm trying to connect to an SQL server, but I'm unable. It suggests this is because SQL server does not allow remote connections, and points me to the named pipes provider, error 40 Could not open a connection to SQL server.

I am running SQL express service pack 2 under Vista. I am a member of SQL administrators (set during installation).

Under the SQL Server Configuration manager, SQL server and Browser are running, the protocols for sqlexpress and the native client have enabled shared memory, named pipes, and tcp/ip.

I tried removing and creating a new database. Now it sits under the database Engine section of Management Studio Express, with a white circle on the database icon. When I try to connect to this database, using either windows authentication or sql server authentication, it fails.

What else is there to try? If there was a troubleshooting guide, that would be helpful.

We can ignore this question. After 'fiddling around' it is now working. Couldn't describe what I did though. I think the error message is misleading though.

Connecting to some remote Sql Server

Hi

I've got a desktop and a notebook and both are connected to the router. On both I've got Sql Server Express and Sql Server Management Studio Express installed. I need to connect to a remote Sql Server (using user ID and password) which works fine on my desktop, but fails on my notebook: Error:0 and Error: 10060.

I checked all router settings and notebook settings, but I don't know what I am doing wrong. I got myself another notebook and installed Sql Server Express plus Management Studio, which did not work either.

Hopefully someone can help.

Thank you.

The error code is of Socket error. It seems you are not able to get connected to your SQL server.

following should help you.

http://www.dameware.com/support/kb/article.aspx?ID=300060

|||

Hi, I am able to connect to the local Sql Server Express (running on the notebook) - only remote connections (at least the one I am trying) fail.

Thanks!

|||

The remote connection may be failing because of socket error. You might like to have a look at remote connection. You might also like to look at the SQL server to check if it allows remote connections.

Connecting to SBS SQL via Remote VPN (VB Application)

Hi,
Dont really know if this is the place to ask but here goes...
I have written a VB application that talks to various SQL2000 Tables. But i
need to expand the VB App to work on a laptop thats remotely connected by VP
N.
Even though the laptop is connected via VPN and can use server applications
it cannot see the SQL server? and produces (can find it errors).
Is there anything i need to do to the sql server to allow outside
connections or to the ISA firewall etc etc.
Any help will be appreciated.
Regards
TIMIf you know that your VPN connection is working correctly, then next try
connecting to the database server using it's IP and port as the data source:
http://www.carlprothman.net/Default...derForSQLServer
If that doesn't work, then speak with your DBA and/or network admin. Perhaps
the following is relevent to your situation:
How To Use ADO to Connect to a SQL Server That Is Behind a Firewall
http://support.microsoft.com/defaul...b;en-us;Q269882
INF: TCP Ports Needed for Communication to SQL Server Through a Firewall
http://support.microsoft.com/defaul...b;EN-US;q287932
"TIM" <TIM@.discussions.microsoft.com> wrote in message
news:C10D5F9D-F628-4B57-9EF3-707C9E621DA9@.microsoft.com...
> Hi,
> Dont really know if this is the place to ask but here goes...
> I have written a VB application that talks to various SQL2000 Tables. But
> i
> need to expand the VB App to work on a laptop thats remotely connected by
> VPN.
> Even though the laptop is connected via VPN and can use server
> applications
> it cannot see the SQL server? and produces (can find it errors).
> Is there anything i need to do to the sql server to allow outside
> connections or to the ISA firewall etc etc.
> Any help will be appreciated.
> Regards
> TIMsqlsql

Connecting to remote SQL server problems

I'm trying to run a report in VB 6 and having a heck of a time. I think my major problem right now is that it doesn't know where I'm trying to connect to and with which credentials. I've looked thru k.babu's sticky thread, but haven't found a solution.

Here is the code I have right now:

Private Sub Form_Load()
Screen.MousePointer = vbHourglass
CRViewer1.ReportSource = Report
Screen.MousePointer = vbDefault
With Report
.ParameterFields(1).ClearCurrentValueAndRange
.ParameterFields(1).AddCurrentValue "KL"
.Database.LogOnServer "p2ssql.dll", "tsg2170", "tableName", "username", "password"
End With
CRViewer1.ViewReport
End Sub

I don't have a clue what to do. BTW, this is my first attempt at anything with crystal in VB.

The error I'm getting is:
Login Failed.
Details: ADO error code: 0x80040e4d
Source: Microsoft OLE DB Provider for SQL Server
Description: Login failed for user 'username'.
SQL State: 42000
Native Error: 18456

I know the username/password are correct.Here is my solution

Private Sub Form_Load()
Dim CRXReport As CRAXDDRT.Report
Dim CRXDb As CRAXDDRT.Database
Dim CRXTbl As CRAXDDRT.DatabaseTable
Dim init As Integer

Set CRXReport = Report
Set CRXDb = CRXReport.Database

CRXReport.DiscardSavedData

init = 1
Do Until init = CRXReport.Database.Tables.Count + 1
CRXReport.Database.Tables.Item(init).SetLogOnInfo "tsg2107", "sedonaMM", "username", "password"
init = init + 1
Loop

With frmMain
.Show
CRXReport.ParameterFields(1).AddCurrentValue "LH"
With CRXReport
.ExportOptions.DestinationType = crEDTDiskFile
.ExportOptions.DiskFileName = "C:\LHReport.pdf"
.ExportOptions.FormatType = crEFTPortableDocFormat
End With
.CRViewer1.ReportSource = CRXReport
CRXReport.Export False
End With

Set CRXReport = Nothing
End Sub

Connecting to remote sql server 2005

Dear All,

I am quite new to sql server 2005. Now I am going to develop a new asp.net application connecting to sql server 2005 remotely. So what must I have install in order to connect to a remote sql server and also to enable me build the tables too? What are the softwares that I need to install before I can proceed on this ? If possible I wnat to avoiding installing the whole server into my local machine as I dont need it. Thanks.

You might find these to be good resources to get you started out.

http://asp.net/learn/dataaccess/default.aspx?tabid=63

http://asp.net/learn/videos/default.aspx?tabid=63

http://msdn.microsoft.com/vstudio/express/sql/learning/default.aspx

|||

Dear Arnie,

Thanks for the link. Thus that means minimally I must install the sql express is it? Thanks.

|||

No, you do not 'have' to install SQL Express.

However, you might benefit from having a local copy to learn with and explore.

Minimally, you will need a client tool or environment that will connect to the SQL Server.

That client environment 'could be' Visual Studio.

It could also be the SQL Server Management Studio Express client tool.

|||

Dear Arnie,

I dont mind installing the express version. But I just want to know is it the same like the orginial sql server 2005 ? Thanks.

|||

Every thing that you can do in SQL Express, you can do in the other editions. SQL Express is the same product code base.

However, there are a few 'enterprise' level features that are not included in SQL Express. Integration Services and Replication are a couple that come to mind.

You can compare the editions here to get a better idea. (However, for learning how to use SQL Server to support a web site, SQL Express will be a good tool for you.)

SQL Server 2005 Features, Version Comparison
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

|||

Dear Arnie,

Thank you a lot of man all your information was very helpful. So I will start working with it. So in case I am stuck then I will get back to you. Really appreciate you a lot.

|||

Dear Arnie,

Ok I have download both the sql express and also the sql server managment studio express. I all working i can log into my database. The problem is that it is using the windows authentication. I would like to make my own account and create a user name and password. I dont know how to go about ? Can you please help me on this ? Thanks.

|||

You might find the tutorial videos on these sites to be well worth your time and effort.

For SQL Server (especially Lesson Seven):

http://msdn.microsoft.com/vstudio/express/sql/learning/default.aspx#1

For ASP:

http://asp.net/learn/videos/default.aspx?tabid=63

Connecting to remote SQL Server 2005

I have set up a SQL Server 2005 to use for testing before moving to another production server. The server works fine when I connect to it with a program on the same computer but not when accessed remotely.

I have set it to to allow remote connections and use TCP IP only and have opened TCP port 1433 on the Windows XP firewall and am using the Teredo service on Port UDP 3544 Also the Server Browser service is running. The server is connected directly to the internet on a static IP (eg. 67.156.18.167) I have set the server to Windows authentication.

When I try to connect to the database from a data grid in Visual Studio on another computer it fails.

I am pretty surethat the problem is that I am not giving the correct information when asked to fill in the information for the data source to find the server as I am new to remote connections in SQL Server.

Thanks for any insightHow are you trying to authenticate the remote system? I could be wrong, but I was of the impression that Windows authentication was only for local access, while remotely you need to be using SQL Server authentication with login / password.
|||

Hi,

Could you please provide the full error message and the connection string? Also, please take a look at this tutorial on enabling remote connections: http://support.microsoft.com/default.aspx/kb/914277

Thanks,

Il-Sung.

|||

I did look at the tutorial on remote connections and made the suggested additions.

I then tried to connect but still received erorrs. I will include them below. Is this possibly happening because I originally set up the server for Windows Authentication not Mixed Mode. If so is there a way I can reset it Mixed Mode Authentication now. I can't seem to locate any way.

--

The name of the server and instance is something like rawtech-7d1b544

The computer Static IP is something like 64.123.16.142

User name is UB using Windows Authentication

Table name in the database is Players

Tried this connection string -
Data Source=64.123.16.142;Initial Catalog=Players;Integrated Security=True

Got this error message -

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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Tried this connection string -
Data Source=64.123.16.142\rawtech-7d1b544;Initial Catalog=Players;Integrated Security=True

Got this error message -
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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Tried this connection string -
Data Source=vantage-7d1b669;Initial Catalog=Players;Integrated Security=True

Got this error message -
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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Thanks for the help

|||

If you are connect to named instance, "Data Source=64.123.16.142\rawtech-7d1b544" is correct. the error message indicates that sqlbrowser is not on or can't be contacted by the client driver. Please make sure sqlbrowser is on you can use SQL Server Surface Area Configuration to do so, besides, you also need to make a firewall exception for udp port 1434. Also make sure remote protocol is enabled and TCP port number, if it means to be enabled and it is different from default port 1433 if it is name instance (shown in errorlog), is unblocked by firewall setting. If you are still not be able to conneciton, try the guildline on in this forums for step by step testing of your connectivity and post which step actually fails, I can give more specific instruction on how it can be resolved.

Let me know if this helps.

|||

SQL Browser is running. Made a general except on port UDP 1434

Remote protocal is enabled as TCP/IP only Made exemption in firewall to port TCP 1433

Also still using Windows Authentication

Same error messages.

Thanks Ush|||Ush, if your instance is a named instance, the TCP port normally is different from TCP port (1433) of a default instance. Please take a look in the error log to see what is the port number, or use sql server configuraiton manager to see server side configuration. -HTH|||

Hi Ush,

I would recommend setting a firewall exception for the sqlservr.exe executable as the KB article recommends. Then you don't need to worry which port your named instance is listening on.

Il-Sung.

|||

Done but still no success

|||

Ush,

Can you try this connection string and let me know the error message.

"Data Source=tcp:64.123.16.142, tcp_port_number;Initial Catalog=Players;Integrated Security=True"

|||

I tried this connection string

Data Source=tcp:64.123.16.142,1433;Initial Catalog=Players;Integrated Security=True

And this was the error message

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 - A connection attempt failed because the connected party did not properly respond
after a period of time, or established connection failed because connected host has failed to respond.)

|||

1. Are you sure that your named instance is listening on 1433, the default port. In most cases, it doesn't unless you explicitly specify to do so.

2. this error message indicates, in most cases, that your firewall is blocking your connections.

-HTH

sqlsql

Connecting to Remote SQL server - time out expired?

Hello

Im connecting to a remote SQL Server in Enterprise Manager Via VPN,

we checked the connection and everything but still o cant connect

to the remote server, Error message says "Time Out Expired",

we checked the firewall config, ADSL connection and it's all Ok

what seems to be the problem here??Hello

Im connecting to a remote SQL Server in Enterprise Manager Via VPN,

we checked the connection and everything but still o cant connect

to the remote server, Error message says "Time Out Expired",

we checked the firewall config, ADSL connection and it's all Ok

what seems to be the problem here??

There are oh so many possibilities...

1. Can you telnet to the remote server on the correct port?
2. Is there another firewall at the other end (I assume so since it's a VPN)? If so, can you verify that the traffic is getting beyond that firewall?
3. Are both firewalls set to handle the "hand-off" that occurs when incoming traffic on port 1433 is handed off to a negotiated port? This is usually automatic, but on some older firewalls, it must be manually configured.
4. Are you using hostname or IP address to connect? If hostname, what is the name resolution service running (I assume DNS)? If DNS, are you sure about the IP Address resolution?
5. Are you NAT'ing the internal IP addresses? Are the NATs configured correctly.

Look at your firewall logs carefully (hopefully there's a nice GUI to help you). The answer is likely in there somewhere. It may not be easy to find, but it's in there.

Regards,

hmscott

Connecting to Remote SQL Server

Hi all,

I have been encountering problems in connectiing to an sql server remotely. I have a SQL Server installed on a machine with a public IP. I am, running an application on one of my other Servers having a Public IP as well. Now i want to access the SQL Server Database of my first machine from my second machine, but i cannot get connected to it. I give the IP address of the machine in the connection string but it fails. I have also tried it with the port number. What could possibly be the solution for a problem like this ?

Regards.

Hi there,

Maybe It caused by your windows firewall ! the initial status of Windows Firewall is on.
Check the status by turning off your windows firewall on SQL Server machine.

Babak Izadi
LotraSoft Ltd.

|||

By default, SQL Server 2005 does not allows remote connections. To configure SQL Server 2005 to allow remote connections, complete all the following steps:

1.Select the SQL Server Surface Area Configuration tool from the Configuration Tools and then click Surface Area Configuration for Services and Connections option. expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply and Ok for restarting the Database engine service.

2. Try adding sqlservr.exe (present in MSSQL.1\MSSQL\Binn directory for the default instance and \MSSQL$instance_name\Binn directory for the named instance) in the list of allowed firewall exceptions on the Windows 2003 box.

Regards,

Rajesh

|||

Can the two servers ping each other?

from serverA run in a command prompt: "ping serverB"

from serverB run in a command prompt: "ping serverA"

If one of the two fails, you probably have a network problem

Else look at the above post's (Rajesh) instructions

|||Can you check whether there are any issues with WINS issue on the network, ifyou are not able to register with name as explained you might try PING between the boxes and also check the firewall exception in this case to avoid any other barrier.

Connecting to remote SQL Server

I have SQL Server 2000 (Developer) on my computer. So I have SQL
Server Enterprise Manager + Tools (such as Query Analyzer).
I am doing some work on a remote server, mostly ASP pages (not
ASP.NET). There is a SQL Server database on that server. I would like
to use Enterprise Manager or Query Analyzer to access that database.
My main development environment is Dreamweaver MX2004. In Dreamweaver,
I was able to setup the database (on the databases tab) using a
"Custom Connection String", listed below (some info obfuscated for
security):
"Provider=SQLOLEDB.1;Persist Security Info=False;Initial
Catalog=funeral;Data Source=xx.xx.xx.xxx;Locale
Identifier=1033;Connect Timeout=15;Use Procedure for Prepare=1;Auto
Translate=True;Packet Size=4096;User Id=XXX;PASSWORD=XXXXXXXX;"
I don't know if it is possible to use the other tools to manage this
database. I've tried the following:
1. In Enterprise Manager, I've tried just putting the ip address as
the server, and "Use SQL Server authentication" from the user ID and
Password above, but I get the following error:
"SQL Server registration failed because of the connection failure
below... Cannot open user default database. Login failed"
2. Created a udl file. In the Provider, selected the "Microsoft OLE DB
Provider for SQL Server" Entered teh IP address and username/password
on the Connection Tab. I get the same error as #1 above.
Any insight would be greatly appreciated.
"Bryce Fischer" <spamtrap@.berzerker-soft.com> wrote in message
news:3btpo01us397pf1gmfl4bt9c8k9dcrv05e@.4ax.com...
> I have SQL Server 2000 (Developer) on my computer. So I have SQL
> Server Enterprise Manager + Tools (such as Query Analyzer).
> I am doing some work on a remote server, mostly ASP pages (not
> ASP.NET). There is a SQL Server database on that server. I would like
> to use Enterprise Manager or Query Analyzer to access that database.
> My main development environment is Dreamweaver MX2004. In Dreamweaver,
> I was able to setup the database (on the databases tab) using a
> "Custom Connection String", listed below (some info obfuscated for
> security):
> "Provider=SQLOLEDB.1;Persist Security Info=False;Initial
> Catalog=funeral;Data Source=xx.xx.xx.xxx;Locale
> Identifier=1033;Connect Timeout=15;Use Procedure for Prepare=1;Auto
> Translate=True;Packet Size=4096;User Id=XXX;PASSWORD=XXXXXXXX;"
> I don't know if it is possible to use the other tools to manage this
> database. I've tried the following:
> 1. In Enterprise Manager, I've tried just putting the ip address as
> the server, and "Use SQL Server authentication" from the user ID and
> Password above, but I get the following error:
> "SQL Server registration failed because of the connection failure
> below... Cannot open user default database. Login failed"
>
User XXX has a login on the remote server, but does not have a user in the
database he is configured to use. To administer the remote database, User
XXX must be a member of the sysadmin role on the remote server
http://snodland.blogspot.com
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.788 / Virus Database: 533 - Release Date: 01/11/2004
|||On Sat, 6 Nov 2004 22:04:54 -0000, "Bob Simms"
<bob_simms@.somewhere.com> wrote:

>User XXX has a login on the remote server, but does not have a user in the
>database he is configured to use. To administer the remote database, User
>XXX must be a member of the sysadmin role on the remote server
Thanks for the reply.
Is it possible to use Query Analyzer or Access to connect to the
server given the information in my original post? I don't need
administrator-like functionality. As I mentioned, in Dreamweaver, I'm
able to create a connection, and view the tables and data (not to be
confused with creating the connection on the ASP pages, but in
Dreamweaver itself.) Dreamweaver lets you browse the tables, and data,
it doesn't let you run SQL Statements, which is why I'd like to use
other tools I have.
TIA.
|||On Sat, 06 Nov 2004 20:21:58 -0500, Bryce Fischer
<spamtrap@.berzerker-soft.com> wrote:

>On Sat, 6 Nov 2004 22:04:54 -0000, "Bob Simms"
><bob_simms@.somewhere.com> wrote:
>
>Thanks for the reply.
>Is it possible to use Query Analyzer or Access to connect to the
>server given the information in my original post? I don't need
>administrator-like functionality. As I mentioned, in Dreamweaver, I'm
>able to create a connection, and view the tables and data (not to be
>confused with creating the connection on the ASP pages, but in
>Dreamweaver itself.) Dreamweaver lets you browse the tables, and data,
>it doesn't let you run SQL Statements, which is why I'd like to use
>other tools I have.
Well... After some persistence, I figured it out. In Access, and
Visual Studio, I was able to bring up the Data Link Properties dialog,
and enter the information, and the one step I was not doing, was
entering in the catalog in the "Select the database on the server:"
field.
I was able to accomplish the same thing in Visual Studio, Server
Explorer, Data Connections.

Connecting to remote SQL Server

I have SQL Server 2000 (Developer) on my computer. So I have SQL
Server Enterprise Manager + Tools (such as Query Analyzer).
I am doing some work on a remote server, mostly ASP pages (not
ASP.NET). There is a SQL Server database on that server. I would like
to use Enterprise Manager or Query Analyzer to access that database.
My main development environment is Dreamweaver MX2004. In Dreamweaver,
I was able to setup the database (on the databases tab) using a
"Custom Connection String", listed below (some info obfuscated for
security):
"Provider=SQLOLEDB.1;Persist Security Info=False;Initial
Catalog=funeral;Data Source=xx.xx.xx.xxx;Locale
Identifier=1033;Connect Timeout=15;Use Procedure for Prepare=1;Auto
Translate=True;Packet Size=4096;User Id=XXX;PASSWORD=XXXXXXXX;"
I don't know if it is possible to use the other tools to manage this
database. I've tried the following:
1. In Enterprise Manager, I've tried just putting the ip address as
the server, and "Use SQL Server authentication" from the user ID and
Password above, but I get the following error:
"SQL Server registration failed because of the connection failure
below... Cannot open user default database. Login failed"
2. Created a udl file. In the Provider, selected the "Microsoft OLE DB
Provider for SQL Server" Entered teh IP address and username/password
on the Connection Tab. I get the same error as #1 above.
Any insight would be greatly appreciated."Bryce Fischer" <spamtrap@.berzerker-soft.com> wrote in message
news:3btpo01us397pf1gmfl4bt9c8k9dcrv05e@.
4ax.com...
> I have SQL Server 2000 (Developer) on my computer. So I have SQL
> Server Enterprise Manager + Tools (such as Query Analyzer).
> I am doing some work on a remote server, mostly ASP pages (not
> ASP.NET). There is a SQL Server database on that server. I would like
> to use Enterprise Manager or Query Analyzer to access that database.
> My main development environment is Dreamweaver MX2004. In Dreamweaver,
> I was able to setup the database (on the databases tab) using a
> "Custom Connection String", listed below (some info obfuscated for
> security):
> "Provider=SQLOLEDB.1;Persist Security Info=False;Initial
> Catalog=funeral;Data Source=xx.xx.xx.xxx;Locale
> Identifier=1033;Connect Timeout=15;Use Procedure for Prepare=1;Auto
> Translate=True;Packet Size=4096;User Id=XXX;PASSWORD=XXXXXXXX;"
> I don't know if it is possible to use the other tools to manage this
> database. I've tried the following:
> 1. In Enterprise Manager, I've tried just putting the ip address as
> the server, and "Use SQL Server authentication" from the user ID and
> Password above, but I get the following error:
> "SQL Server registration failed because of the connection failure
> below... Cannot open user default database. Login failed"
>
User XXX has a login on the remote server, but does not have a user in the
database he is configured to use. To administer the remote database, User
XXX must be a member of the sysadmin role on the remote server
http://snodland.blogspot.com
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.788 / Virus Database: 533 - Release Date: 01/11/2004|||On Sat, 6 Nov 2004 22:04:54 -0000, "Bob Simms"
<bob_simms@.somewhere.com> wrote:

>User XXX has a login on the remote server, but does not have a user in the
>database he is configured to use. To administer the remote database, User
>XXX must be a member of the sysadmin role on the remote server
Thanks for the reply.
Is it possible to use Query Analyzer or Access to connect to the
server given the information in my original post? I don't need
administrator-like functionality. As I mentioned, in Dreamweaver, I'm
able to create a connection, and view the tables and data (not to be
confused with creating the connection on the ASP pages, but in
Dreamweaver itself.) Dreamweaver lets you browse the tables, and data,
it doesn't let you run SQL Statements, which is why I'd like to use
other tools I have.
TIA.|||On Sat, 06 Nov 2004 20:21:58 -0500, Bryce Fischer
<spamtrap@.berzerker-soft.com> wrote:

>On Sat, 6 Nov 2004 22:04:54 -0000, "Bob Simms"
><bob_simms@.somewhere.com> wrote:
>
>Thanks for the reply.
>Is it possible to use Query Analyzer or Access to connect to the
>server given the information in my original post? I don't need
>administrator-like functionality. As I mentioned, in Dreamweaver, I'm
>able to create a connection, and view the tables and data (not to be
>confused with creating the connection on the ASP pages, but in
>Dreamweaver itself.) Dreamweaver lets you browse the tables, and data,
>it doesn't let you run SQL Statements, which is why I'd like to use
>other tools I have.
Well... After some persistence, I figured it out. In Access, and
Visual Studio, I was able to bring up the Data Link Properties dialog,
and enter the information, and the one step I was not doing, was
entering in the catalog in the "Select the database on the server:"
field.
I was able to accomplish the same thing in Visual Studio, Server
Explorer, Data Connections.

connecting to remote SQL Server

I have recently taken over as webmaster at my company and need help connecting to our remotely hosted SQL server. The company who is hosting it will charge me "support time" to explain how to connect to it.

The database is used to store data and application information for our customer service center. So it is ALL database driven...

i have installed SQL Server Enterprise Manager on my machine, but have no ideas on where to even being trying to connect. I have the name of the server, password....etc. But do not know where to configure a connection.

ALSO, when i install my Enterprise Manager the first options askes me whether i want to create a new instance of SQL server on a local or remote machine....i am guessing my local machine right...?

any insight or direction is greatly appreciated.

tony.

Don't you just love hosting companies...First - I am assuming that you know the actual SQL Server name and the sa password. Get the IP address of the server (G-d I hope they don't charge you for THAT!) and try to ping the server by name and IP address. If you can ping by name, open Enterprise Manager and expand Microsoft SQL Servers, then right click on SQL Server Group and select New SQL Server Registration. Check the box that says I dont want to use the freakin' wizard anymore. Now enter the name of the SQL Server - not the name of the actual Windows server (unless they are the same - which they will be if it was a standard installation of a single instance). Check Use SQL Server Authentication, then enter the Login Name as 'sa' and the Password as what ever it is. If you hosting company has not blocked port 1434 this should work fine. To keep from writing an episitle, let me know if this works. There is a different procedure is the SQL instance name is different from the Windows Server name.

Brad
Ex Nihilo, Nihil Fit
|||

hahaha

isn't it suppose to be port 1433?

most hosting company have a web-based database administration tools

which you can work on. All you need to know to move on to the development

is to have the connections string.

sqlsql

Connecting to remote SQL Server

I have SQL Server 2000 (Developer) on my computer. So I have SQL
Server Enterprise Manager + Tools (such as Query Analyzer).
I am doing some work on a remote server, mostly ASP pages (not
ASP.NET). There is a SQL Server database on that server. I would like
to use Enterprise Manager or Query Analyzer to access that database.
My main development environment is Dreamweaver MX2004. In Dreamweaver,
I was able to setup the database (on the databases tab) using a
"Custom Connection String", listed below (some info obfuscated for
security):
"Provider=SQLOLEDB.1;Persist Security Info=False;Initial
Catalog=funeral;Data Source=xx.xx.xx.xxx;Locale
Identifier=1033;Connect Timeout=15;Use Procedure for Prepare=1;Auto
Translate=True;Packet Size=4096;User Id=XXX;PASSWORD=XXXXXXXX;"
I don't know if it is possible to use the other tools to manage this
database. I've tried the following:
1. In Enterprise Manager, I've tried just putting the ip address as
the server, and "Use SQL Server authentication" from the user ID and
Password above, but I get the following error:
"SQL Server registration failed because of the connection failure
below... Cannot open user default database. Login failed"
2. Created a udl file. In the Provider, selected the "Microsoft OLE DB
Provider for SQL Server" Entered teh IP address and username/password
on the Connection Tab. I get the same error as #1 above.
Any insight would be greatly appreciated."Bryce Fischer" <spamtrap@.berzerker-soft.com> wrote in message
news:3btpo01us397pf1gmfl4bt9c8k9dcrv05e@.4ax.com...
> I have SQL Server 2000 (Developer) on my computer. So I have SQL
> Server Enterprise Manager + Tools (such as Query Analyzer).
> I am doing some work on a remote server, mostly ASP pages (not
> ASP.NET). There is a SQL Server database on that server. I would like
> to use Enterprise Manager or Query Analyzer to access that database.
> My main development environment is Dreamweaver MX2004. In Dreamweaver,
> I was able to setup the database (on the databases tab) using a
> "Custom Connection String", listed below (some info obfuscated for
> security):
> "Provider=SQLOLEDB.1;Persist Security Info=False;Initial
> Catalog=funeral;Data Source=xx.xx.xx.xxx;Locale
> Identifier=1033;Connect Timeout=15;Use Procedure for Prepare=1;Auto
> Translate=True;Packet Size=4096;User Id=XXX;PASSWORD=XXXXXXXX;"
> I don't know if it is possible to use the other tools to manage this
> database. I've tried the following:
> 1. In Enterprise Manager, I've tried just putting the ip address as
> the server, and "Use SQL Server authentication" from the user ID and
> Password above, but I get the following error:
> "SQL Server registration failed because of the connection failure
> below... Cannot open user default database. Login failed"
>
User XXX has a login on the remote server, but does not have a user in the
database he is configured to use. To administer the remote database, User
XXX must be a member of the sysadmin role on the remote server
http://snodland.blogspot.com
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.788 / Virus Database: 533 - Release Date: 01/11/2004|||On Sat, 6 Nov 2004 22:04:54 -0000, "Bob Simms"
<bob_simms@.somewhere.com> wrote:
>> 1. In Enterprise Manager, I've tried just putting the ip address as
>> the server, and "Use SQL Server authentication" from the user ID and
>> Password above, but I get the following error:
>> "SQL Server registration failed because of the connection failure
>> below... Cannot open user default database. Login failed"
>User XXX has a login on the remote server, but does not have a user in the
>database he is configured to use. To administer the remote database, User
>XXX must be a member of the sysadmin role on the remote server
Thanks for the reply.
Is it possible to use Query Analyzer or Access to connect to the
server given the information in my original post? I don't need
administrator-like functionality. As I mentioned, in Dreamweaver, I'm
able to create a connection, and view the tables and data (not to be
confused with creating the connection on the ASP pages, but in
Dreamweaver itself.) Dreamweaver lets you browse the tables, and data,
it doesn't let you run SQL Statements, which is why I'd like to use
other tools I have.
TIA.|||On Sat, 06 Nov 2004 20:21:58 -0500, Bryce Fischer
<spamtrap@.berzerker-soft.com> wrote:
>On Sat, 6 Nov 2004 22:04:54 -0000, "Bob Simms"
><bob_simms@.somewhere.com> wrote:
>> 1. In Enterprise Manager, I've tried just putting the ip address as
>> the server, and "Use SQL Server authentication" from the user ID and
>> Password above, but I get the following error:
>> "SQL Server registration failed because of the connection failure
>> below... Cannot open user default database. Login failed"
>>User XXX has a login on the remote server, but does not have a user in the
>>database he is configured to use. To administer the remote database, User
>>XXX must be a member of the sysadmin role on the remote server
>Thanks for the reply.
>Is it possible to use Query Analyzer or Access to connect to the
>server given the information in my original post? I don't need
>administrator-like functionality. As I mentioned, in Dreamweaver, I'm
>able to create a connection, and view the tables and data (not to be
>confused with creating the connection on the ASP pages, but in
>Dreamweaver itself.) Dreamweaver lets you browse the tables, and data,
>it doesn't let you run SQL Statements, which is why I'd like to use
>other tools I have.
Well... After some persistence, I figured it out. In Access, and
Visual Studio, I was able to bring up the Data Link Properties dialog,
and enter the information, and the one step I was not doing, was
entering in the catalog in the "Select the database on the server:"
field.
I was able to accomplish the same thing in Visual Studio, Server
Explorer, Data Connections.

connecting to remote server

Hi friends
we've C# app that connects a sql server 2005. we've new requests from client that when users go offsite they still want use our app and connect to sql server.
my question is how can we connect to sql server remotely ?
is it using TCP/IP protocol ? if so how do we do that ?
Thanks for ur help.

Use SQL Server Configuration Manager, select protocols for your instance. On the right side, enable TCP protocol. You need restart the sql server. If you have firewall, don't forget to put your tcp port into exception, (also UDP port 1434 for sql browser if you have a named instance.)

|||Thanks for that Hong.
i've one more question on this.
what should my connection string contain ? should it contain IP address for server name ?
Thanks again|||btw we use patterns and practices library for our database connections.|||Either IP address or server name should work if your DNS works correctly.
Thanks.|||

Thank you very much for ur replies Hong.

still need some clarification as am not fully aware how DNS works .

currently in our connections string we specify our sql server name as "mallu" and other info like database etc.,(btw we use enterprise library for data access)

are you saying that once we open port on our server and allow remote connects abv connection string just works ? i mean say if we our server is in new zealand and user goes to say australia and he still be able connect ?

Thanks for clarification .

much appreciated you can point me to some resource on these issues on net

|||

Hi prk,

DNS is the Domain Name System -- essentially the directory service that translates your hostnames into IP address. Your network probably has one and your network admin should be able to give you more info.

Regardless of the geography, if your server is configured to listen to a remote protocol and the firewall (if any) is configured properly, you should be able to connect from your client app. The BOL contains some great information on how to do so:

http://msdn2.microsoft.com/en-us/library/ms190608.aspx
http://msdn2.microsoft.com/en-us/library/ms187853.aspx

Also, check out this tutorial on connecting to the database:

http://msdn2.microsoft.com/en-us/library/ms345318.aspx

Il-Sung.

|||Il-Sung
wonderful mate. thank you very much for those links.
am looking something like that.will go thru those articles.
Thanks again.

Connecting to remote named instance

Hi,
I need some help connecting to a remote named instance of SQL Server 2000
Lets say the server has fqdn: "sqlsrv1.dk.mydomain.com" and is installed as
a named instance with name: "COPENHAGEN"
Trying to connect from another location using
"sqlsrv1.dk.mydomain.com\COPENHAGEN" will fail, but connecting from the
location in question using "sqlsrv1\COPENHAGEN" will succceed.
How do I do this ?
In advance, thanks...
Soren
Soren,
If the servers are on the same domain, you should be able to connect to the
remote server using
sqlsrv1\COPENHAGEN
Benny
P.S. Where in Denmark
"Soren Staun Jorgensen" <no@.spam.com> skrev i en meddelelse
news:O8BBndMOEHA.1456@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I need some help connecting to a remote named instance of SQL Server 2000
> Lets say the server has fqdn: "sqlsrv1.dk.mydomain.com" and is installed
as
> a named instance with name: "COPENHAGEN"
> Trying to connect from another location using
> "sqlsrv1.dk.mydomain.com\COPENHAGEN" will fail, but connecting from the
> location in question using "sqlsrv1\COPENHAGEN" will succceed.
> How do I do this ?
> In advance, thanks...
> Soren
>
>
|||It might be name resolution problem.
Ping sqlsrv1.dk.mydomain.com\COPENHAGEN
and
Ping sqlsrv1
is it returning the same ip address.
And also check your MDAC version.
Regards.