SQL Server connection issue

  • Thread starter Thread starter Jon
  • Start date Start date
J

Jon

I feel like I've read about this issue before, but I don't recall any
specifics.

I have a service written in 2.0 that connects to a SQL server DB using an
SQL Server login.

On my machine and on one other machine it works perfectly fine. However,
when I place the service and install it on another machine I get an error
that it can't connect to the DB. Everything is the same (the exe, the
domain, the db server (SQL 2000 sp4), the database, the connection string,
the user the service logs on as), except that on this one machine it errors.

Anyone can point me to a solution?

Thanks

The error is:
A database connection could not be established.

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: Named Pipes Provider, error:
40 - Could not open a connection to SQL Server)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo,
SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64
timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean
integratedSecurity, SqlConnection owningObject)
at
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo
serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64
timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String
host, String newPassword, Boolean redirectedUserInstance, SqlConnection
owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String newPassword,
Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object providerInfo, String
newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection
owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection
owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
 
You need to tell more technical details:

1. What services did you created? web services or window services?
2. Where the service and the SQL Server run? in the same computer or in
different computers?
3. Show the ConnectionString you used.

The possible solution would be:

1. Make sure the SQL Server runs correctly. If it is in different computer,
you need to make sure it allows remote access;
2. Make sure your ConnectionString is correct. If the SQL Server uses
Windows Security only, then the userr account that runs the service must be
grant necessary access the the SQL Server/database.
 
Jon said:
I feel like I've read about this issue before, but I don't recall any
specifics.

I have a service written in 2.0 that connects to a SQL server DB using an
SQL Server login.

On my machine and on one other machine it works perfectly fine. However,
when I place the service and install it on another machine I get an error
that it can't connect to the DB. Everything is the same (the exe, the
domain, the db server (SQL 2000 sp4), the database, the connection string,
the user the service logs on as), except that on this one machine it errors.

Anyone can point me to a solution?

Thanks

The error is:
A database connection could not be established.

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: Named Pipes Provider, error:
40 - Could not open a connection to SQL Server)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo,
SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64
timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean
integratedSecurity, SqlConnection owningObject)
at
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo
serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64
timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String
host, String newPassword, Boolean redirectedUserInstance, SqlConnection
owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String newPassword,
Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object providerInfo, String
newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection
owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection
owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
Click to expand...

Are 2 computers (SQL server and client connect to it) put in the same
domain?
 
It is a windows service. The SQL server is one machine, and there are
several client machines which run the service. It works from each of the
client machines except for one. They are all configured identically and
members of the same domain.

The connection string used is the same from all the client machines; it is:
UID=user;PWD=pass;database=dbname;server=serverIP;Persist Security
Info=False;

The server is definitely configured correctly, b/c the other client machines
can all connect successfully over the network. Other programs can connect to
the SQL server, even from the machine where the service fails.

Thanks
Jon
 
OK, you might want to try to connect to the SQL Server from the problemic
box by using a *.udl file.

Open a blank NotePad file and save it to desktop as "Test.udl". Close
NotePad. Then double-click "test.udl" to start "Data Link Properties"
disloag box. Now you can use the same credential as the ConnectionString to
test your connect to the SQL Server (i.e. use SQL Server security, entering
username/password pair).

One of the possible reason could be that box' firewall, if it is running.
 
Back
Top