C#-App: Cannot connect to SQL Server

[dfarney]

So I've created a Windows based application in C# that uses data from our databases on a service provider (crystaltech). The application has been deployed and has been working great on all our computers and all our clients computers, except for one... When every they try to login/communicate with the database it returns the following error:

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 - 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.)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup)
   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()
   at ColeyClientSystem.Utils.DataManager.ExecuteScalar(String query)
   at ColeyClientSystem.Login.AttemptLogin()

I've searched google for hours and tried just about every variation of a connection string using both sql and oledb connections. The current connection string causing this error is:

Data Source=xxx.xxx.xxx.xxx,1433;Network Library=DBMSSOCN;Initial Catalog=xxx;User ID=xxx;Password=xxx

and

Provider=SQLOLEDB;Data Source=xxx.xxx.xxx.xxx,1433;Persist Security Info=True;Password=xxx;User ID=xxx;Initial Catalog=xxx

I've also used:

Data Source=xxx.webcontrolcenter.com;Type System Version=SQL Server 2000;Integrated Security=False;Persist Security Info=True;Password=xxx;User ID=xxx;Initial Catalog=xxx

Everyone else connects fine and the software runs great, except this one user. I've also had them add the port in windows firewall in network connection settings, and nothing.

Here is the exact function causing the error:

public static String ExecuteScalar(string query)
{
    string connectionString = "Data Source=xxx.xxx.xxx.xxx,1433;Network Library=DBMSSOCN;Initial Catalog=xxx;User ID=xxx;Password=xxx";

    SqlConnection connection = new SqlConnection(connectionString);
    connection.Open();
    try
    {
        SqlCommand sqlcmd = new SqlCommand(query, connection);
        return sqlcmd.ExecuteScalar().ToString();
    }
    catch
    {
        return "";
    }
    finally
    {
    if (connection.State == ConnectionState.Open)
        connection.Close();
    }
}

The user is running Windows XP 5.1, and like I said only one user is getting this error. Thanks for you time.

Derek

[Plater]

XP 5.1? I've never heard of that?

[Curtis Rutland]

XP 5.1? I've never heard of that?

Open up a command line and type "winver" and you'll see the actual version of the windows kernel you're using. XP is 5.1, Vista is 6.0, etc.

[Plater]

Huh how about that.
I've always gone with "XP Pro SP1" or "SP2" (or I guess now it could be "SP3")

Anyway, Is there anything else different about this PC/User? Are they on/not on a domain? Do they have different credential security levels?
Do they have some version of SQL Server (or the freebee ones) running locally on their PC.

I see that it looks like you use an OLEDB driver in your connection string, is that setup as a userDSN/SystemDSN/fileDSN or whatever is correct? Does that connection have the correct default settings and permissions?

[dfarney]

Its really hard to tell what is different on the machine being that they are in Utah and I'm in Texas. From what I understand she is not on a network, talked to the ISP and they said it wasn't anything they were blocking on their end. As far as having a version of SQL Server on the machine, I highly doubt it...

I guess my question is what else could be blocking her access to our data servers/our servers blocking her? My connection strings are good and other people from all over the US dont have any issues. cmd -> ping the database ip gets a response.

I'm not quite sure what you mean by "Do they have different credential security levels?", and the olebd connection is generated by VS and i got that connection string from the app.config file.

Thanks for your help guys.

Derek

[Curtis Rutland]

I see that it looks like you use an OLEDB driver in your connection string, is that setup as a userDSN/SystemDSN/fileDSN or whatever is correct?

OleDb doesn't rely on DSNs. It just needs the provider to be installed on the machine. ODBC is the one that uses DSNs.

[Plater]

OleDb doesn't rely on DSNs. It just needs the provider to be installed on the machine. ODBC is the one that uses DSNs.

Hehe I get them mixed up all the time, don't like 'em.

I wasn't aware that the computer located elsewhere, i thought we were talking like "withen an office complex" type situation. So I guess none of that stuff applies.

Does that user have a slow internet connection? Connection timeout is listed in the error message as a possible source of the problem (although I've almost never seen it be the actual problem).

Could you perhaps generate a really small/light-weight program that just attempts to connect to the database. Perhaps fill it with debug code and send it off to them to see if it can tell you anything more? Give it like super long timeout for connecting and such?

[dfarney]

Could you perhaps generate a really small/light-weight program that just attempts to connect to the database. Perhaps fill it with debug code and send it off to them to see if it can tell you anything more? Give it like super long timeout for connecting and such?

I basically did that...I put a function at the beginning of the application that tested like 10 different connection string that work for me, and every one returned the same error message as above. I dont think that its a time out issue because I was talking to her over then phone when she ran it and the application started up in about the same amount of time, but Ill give it a try anyways. I did something like this:

string connString = ...;
SqlConnection conn = new SqlConnection(connString);
try conn.Open();

The only thing that I really gathered was ErrorCode -2146232060 for the sql and ErrorCode -2147467259 for the oledb.

I really think its like a firewall or something else blocking the port, can you give me some info on disabling these thing? Until then, back to google...

Derek

[Plater]

Sometimes anti-virus software has their own firewall (Norton Security is one I think) that could be blocking it?
Is there anyone else at that location that can use it fine?

[madankarmukta]

Could you once try with bypassing the mentioned IP address of the DataSourse in the connection string from the proxy ?
Or else just write the hostname of the Ipaddress in the DataSource attribute and bypass hostname from the proxy.

Thanks!

[Sumachechi]

Hi

i was facing the error when opening connection using sqlconnection

nw got solved

http://sqlservererror-info.blogspot.com/2009/07/error-has-occurred-while-establishing.html

http://aspnetmembershipprovider.blogspot.com/2009/02/error-has-occurred-while-establishing.html

See This