C# Application: Remote Connection to SQL Server 2000 issues

[dfarney]

I have created a program in c# .net 2.0 that connects to a remote sql 2000 server. Connection for all our users/clients all over the US is fine, execpt one. Im thinking that it his firewall, but when XP prompts him he clicks "allow access". Here the the error message he gets:

 
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(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() 
at ColeyClientSystem.Utils.DataManager.ExecuteScalar(String query)
at ColeyClientSystem.Login.AttemptLogin()

I had him 'ping' the address and he gets a response...

Also here is my connection string:

Data Source=xxx.xxx.xxx.xxx;Persist Security Info=True;Password=xxxxxx;User ID=xxxxx;Initial Catalog=coley

[Frinavale]

I have created a program in c# .net 2.0 that connects to a remote sql 2000 server. Connection for all our users/clients all over the US is fine, execpt one. Im thinking that it his firewall, but when XP prompts him he clicks "allow access". Here the the error message he gets:
...
I had him 'ping' the address and he gets a response...

Also here is my connection string:

Data Source=xxx.xxx.xxx.xxx;Persist Security Info=True;Password=xxxxxx;User ID=xxxxx;Initial Catalog=coley

Have him check that the SQL Sever is set up to accept remote connections.

Also make sure that the type of credentials you are providing in your connection string matches with the type of authentication that the server expects.

Also make sure that his firewall allows your program to connect on the port that your server is using.

[HughJass]

I guess the original poster hasn't replied whether this worked or not. I'm getting the exact same error and have tried all three suggestions above to no avail. I'm running a C# ASP.NET web application.

My IIS application is on a different domain than the SQL server. From the IIS server I can connect to the database through Query Analyzer as well as any wizards within .NET but anytime I try connecting through code I get the above error. I can connect to Databases within my domain.

My DBA tells me that the user trying to login comes in as ASPNET instead of the username/password combination I specify in my connection string. The other thing that concerns me is the mention of SQL Server 2005 in the error message since I'm connecting to SQL server 2000. Are there any options in ASP.NET 2.0 (aka Visual Studio 2005) to specify the version of database you are connecting to?

Visual Studio was configured to access a 2005 express server so I'm wondering if may I have the wrong drivers.

Any help would be appreciated.

[Frinavale]

I guess the original poster hasn't replied whether this worked or not. I'm getting the exact same error and have tried all three suggestions above to no avail. I'm running a C# ASP.NET web application.

My IIS application is on a different domain than the SQL server. From the IIS server I can connect to the database through Query Analyzer as well as any wizards within .NET but anytime I try connecting through code I get the above error. I can connect to Databases within my domain.

My DBA tells me that the user trying to login comes in as ASPNET instead of the username/password combination I specify in my connection string. The other thing that concerns me is the mention of SQL Server 2005 in the error message since I'm connecting to SQL server 2000. Are there any options in ASP.NET 2.0 (aka Visual Studio 2005) to specify the version of database you are connecting to?

Visual Studio was configured to access a 2005 express server so I'm wondering if may I have the wrong drivers.

Any help would be appreciated.

It doesn't matter whether your connecting to MSSql 2000 or 2005, the code should be the same.

Your DBA guy is correct in saying that your application runs under the ASPNET user account. If you are using windows integrated security to connect to your sever then this might explain the problem. You would have to set up your application to use impersonation if this is the case.

If you aren't using windows integrated security to connect to your server than the problem is something else.

Could you please post your connection string (omitting user names, passwords, and IP address)

-Frinny

[HughJass]

<add name="Connectio nString3" connectionStrin g="Data Source=servername;Initial Catalog=database;Persist Security Info=True;User ID=username;Password=password"
providerName="S ystem.Data.SqlC lient" />

This is how the connection string looks in my web.config. I've used many variations of a connection string with no luck. When I do a test of the connection string it pulls up data correctly, when it's run through the web page it gets the before mentioned error. Is I use the same connection string in Classic ASP it works fine. I can remove Integrated security but I had planned on using it in place of a login page in my application.

I didn't think the server version mattered but the SQL server 2005 text in the error message bothered me nonetheless.

HTH

[Frinavale]

<add name="Connectio nString3" connectionStrin g="Data Source=servername;Initial Catalog=database;Persist Security Info=True;User ID=username;Password=password"
providerName="S ystem.Data.SqlC lient" />

This is how the connection string looks in my web.config. I've used many variations of a connection string with no luck. When I do a test of the connection string it pulls up data correctly, when it's run through the web page it gets the before mentioned error. Is I use the same connection string in Classic ASP it works fine. I can remove Integrated security but I had planned on using it in place of a login page in my application.

I didn't think the server version mattered but the SQL server 2005 text in the error message bothered me nonetheless.

HTH

Hmmm, have you checked to make sure that any proxy or firewalls that your database server might be behind are set to allow for connections?

What type of authentication is your database server set up with?
What type of authenticating credentials are you providing to the server?

Some thing's blocking you...we just need to figure out what.
:)

-Frinny

[HughJass]

Man, I wish this site showed the post I'm replying to on the reply page.

Anyway, my DBA says there are no firewalls on the database server and that the two domains are trusted.

Authentication is set to mixed, although I've tried connecting with SQL credentials as well as windows both getting the same error.

I agree something is blocking me, my DBA seems to insist it's at my end and I'm not allowed to take a look at the server (they are in a different building than me on the other side of town).

Thanks for you help, I'll keep plugging away at this. And I PROMISE to post a solution once it's solve.. nothing worse than seeing an open ended problem in a forum.

[Frinavale]

Man, I wish this site showed the post I'm replying to on the reply page.

Anyway, my DBA says there are no firewalls on the database server and that the two domains are trusted.

Authentication is set to mixed, although I've tried connecting with SQL credentials as well as windows both getting the same error.

I agree something is blocking me, my DBA seems to insist it's at my end and I'm not allowed to take a look at the server (they are in a different building than me on the other side of town).

Thanks for you help, I'll keep plugging away at this. And I PROMISE to post a solution once it's solve.. nothing worse than seeing an open ended problem in a forum.

There is a way to see what you're replying to...instead of clicking the yellow button at the bottom of the post click the "reply" link at the bottom of the post you're replying to...this will take the post and place it in [quote] tags so you can see what you're replying to.

I'm not sure what else to try for your database problem though.
Since you're application is running under the ASPNET user account I would recommend using the SQL authentication mode for simplicity sake.

Sorry I couldn't be more help.

-Frinny

[dfarney]

I guess the original poster hasn't replied whether this worked or not. I'm getting the exact same error and have tried all three suggestions above to no avail.

Sorry I havn't gotten back on here in a while....

Frinavale is right; we're getting blocked somewhere it's just a matter of finding out where...In my situtation my client was getting blocked by his network within the building he lived/worked in.

You connection string looks good as did mine -- try going to Control Panel -> Windows Firewall -> Exceptions -> Add Port... and add the the ports 1433 and 1434.

Hope this helps.

Derek

[Plater]

In my experiance:
If you are not using windows IDs as username/password for the sql connection, you will need to include this:

Integrated Security=False;

In your connection string, since it's "True" be default and it will ignore the userid/pass you provide in the string.

Although the "server did not respond" message usually means it's not even MAKING it to talk to the server.

[HughJass]

Well, my problem has been solved unfortunately I don't have the specifics. Yesterday afternoon my DBA sent out an email that he applied a bunch of patches to the server in question and all of a sudden everything worked fine. I was ecstatic to say the least.

Sadly I was unable to pry any information from my DBA about the patches or what was done. I suppose it was because his previous position was that the problem was at my end and he vehemently defended against any suggestion that the problem was at the server. I'm thinking he's probably eating crow and isn't too happy about it.

If I manage to find anything out later on I will be glad to post it.

[Frinavale]

Well, my problem has been solved unfortunately I don't have the specifics. Yesterday afternoon my DBA sent out an email that he applied a bunch of patches to the server in question and all of a sudden everything worked fine. I was ecstatic to say the least.

Sadly I was unable to pry any information from my DBA about the patches or what was done. I suppose it was because his previous position was that the problem was at my end and he vehemently defended against any suggestion that the problem was at the server. I'm thinking he's probably eating crow and isn't too happy about it.

If I manage to find anything out later on I will be glad to post it.

Hah!
I'm glad your problem's solved.
This happened to me once too, but it wasn't concerning a database, it was concerning an Emailing problem...I spent months trying to get around it...the last few weeks were the Worst because I couldn't find anything wrong with my code (but was determined that something was wrong because they kept telling me so)....

It is quite unsatisfying to have a problem resolved magically with no answer!

-Frinny

[HughJass]

The only thing I could get out of my DBA was:

I reaplied SQL SP3 & SP4 and a few other OS patches that showed up as badly installed in our patch manager

So I guess the solution is to make sure all patches are installed on your database server. I'm not sure how you know if a patch has been badly installed but then again I only use windows update myself, maybe they have a more sophisticated tool.

Frinny, I know exactly what you mean, I once wrote a wep app in classic ASP and an #include statement wouldn't work unless I put a comment on the line above it.. never found out why, all I know is that it didn't work unless a comment was there.

[Plater]

Frinny, I know exactly what you mean, I once wrote a wep app in classic ASP and an #include statement wouldn't work unless I put a comment on the line above it.. never found out why, all I know is that it didn't work unless a comment was there.

I've had that happen to me on a number of things, I have since decided that it must be related to somewhere along the lime something thought the file was encoding with unicode and was therefor looking for a byte-order-mark at the begining of the file.