Connection String Problem

[Randy]

Hi,

I am trying to connect to a MS SQL Sever 2005 via an IP address and I am
having problems. I have setup MS SQL Server to accept remote connections.
Here is the connection string:

"Data Source=198.118.0.149,1433;Network Library=DBMSSOCN;Initial
Catalog=DB1;User ID=sa;Password=test;"

I think the connection string is correct. I had a few questions about the
connection string.

1) Should the Data Source include the name of the SQL Server
(198.118.0.149\TestServer)?
2) Is the User ID and password be the SQL authentication or Windows?

Here is the 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 - 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.)

Any ideas?

 

[Frans Bouma [C# MVP]]

Hi,

I am trying to connect to a MS SQL Sever 2005 via an IP address and I
am having problems. I have setup MS SQL Server to accept remote
connections. Here is the connection string:

"Data Source=198.118.0.149,1433;Network Library=DBMSSOCN;Initial
Catalog=DB1;User ID=sa;Password=test;"

tip: don't post public ip addresses with passwords etc.

I don't think you should mention ';Network Library=DBMSSOCN' it's not
necessary. Also the port isn't necessary as the port 1433 is the
default.

I think the connection string is correct. I had a few questions
about the connection string.

1) Should the Data Source include the name of the SQL Server
(198.118.0.149\TestServer)?

Not if it's the only sqlserver instance.

2) Is the User ID and password be the SQL authentication or Windows?

if you're specifying user id and password, it's SQL authentication.
Windows authentication is done by specifying
;integrated security=SSPI
instead of
;User ID=sa;Password=test

and you then will login with the windows account the process making
the connection is running under.

Here is the 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 - 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.)

I'd make the changes suggested and try again.

FB


--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Randy]

Thanks for the post. I fixed the connection string and still have the same
error. I am starting to think that server that ms sql is on is causing the
problem.

 

[Norman Yuan]

Where do you try to connect from, the Internet, or the same network domain
as the SQL Server?

If it is from the Internet (or outside the nwtwork domain where the SQL
Server is), does the network setting allow connection to the SQL Server from
outside? Is the firewall configured correctly for this? It is rare to allow
SQL Server being accessed from outside the network, and it is asking
disaster, in most cases. You have to be sure the said SQL Server does allow
to be access from outside the network domain.

You will need specify "xxx.xxx.xxx.xxx\TestServer" as data source name, if
the SQL Server is a named instance.

You only provide user ID and Password when the SQL Server's security mode is
set as "Mixed" (SQL Server's default installation set its security mode as
"Windows Integrated", Mixed mode must be enabled explicitly).

 

[Scott M.]

www.ConnectionStrings.com

Thanks for the post. I fixed the connection string and still have the
same error. I am starting to think that server that ms sql is on is
causing the problem.

 

[Paul Clement]

¤ Hi,
¤
¤ I am trying to connect to a MS SQL Sever 2005 via an IP address and I am
¤ having problems. I have setup MS SQL Server to accept remote connections.
¤ Here is the connection string:
¤
¤ "Data Source=198.118.0.149,1433;Network Library=DBMSSOCN;Initial
¤ Catalog=DB1;User ID=sa;Password=test;"
¤
¤ I think the connection string is correct. I had a few questions about the
¤ connection string.
¤
¤ 1) Should the Data Source include the name of the SQL Server
¤ (198.118.0.149\TestServer)?
¤ 2) Is the User ID and password be the SQL authentication or Windows?
¤
¤ Here is the 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 - 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.)
¤
¤ Any ideas?

I would start with the following to make certain that you have connectivity.

http://blogs.msdn.com/sql_protocols...emote-Connectivity-Issue-TroubleShooting.aspx


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[William \(Bill\) Vaughn]

the default port is not 1433 with dynamic port assignment. Use the SQL
Configuration Manager to see what port has been assigned to the SQL Server
instance you're trying to address.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...