DB Connection problem: C# syntax, SQL Server or Vista

[RRinTetons]

(cross posted from the SQL Server Programming forum, sorry, but I honestly don't know which place to start and they're a little more active over there.)

And I don't know which one. Either I've got something wrong with SQL Server set up, my C# syntax or Vista's #%^&&*@#$ security model is getting in the way. (I got the Vista machine, I didn't choose it!)

In C# I can successfully set up a connection string as:

SqlConnection MyConnection = new SqlConnection("server=(local);database=pubs;Trusted_Connection=yes")

to connect to the pubs demo db on my local machine and all is well.

The production server I want to connect to is accessed remotely. I can connect to it with SSMS using server 'myserver.mydomain.com', myUserID 'myUserID', password 'myPassword' and default DB 'myDB'.

When I try to do it from C# with the connection string definition:

SqlConnection MyConnection = new SqlConnection("myserver.mydomain.com;database=myDB;UID=myUserID;PWD=myPassword")

I get back an error from the .NET Framework when the query tries to execute (the point at which the connection is actually opened for the first time. It's the generic:

"An error occurred while establishing a connection to the server..."

I'm guessing I've got something crossed up in the connection string, but I haven't found a good example of building the string for an external data source using SQL Server authentication.

?????



-
Richard Ray
Jackson Hole Mountain Resort

 

[B00gyeMan]

1. You did not specify what version of SQL server do those two machines run (yours and the remote one).

2. For a list of how the connection strings should look, check this out:

http://www.connectionstrings.com/

3. You successfully connected to your local server by including in the connection string the Trusted_Connection=yes. That means Windows authentication, not SQL authentication as you're trying with the remote server.

4. The connection string for the remote server should look something like: SqlConnection MyConnection = new SqlConnection("Data Source=myserver.mydomain.com;Initial Catalog=myDB;User Id=myUserID;Password=myPassword;");. This connection string is for SQL server 2005 but it should work for SQL server 2000 as well.

5. Are you trying to connect to a SQL Server Express or a named instance? In that case the connection should look like: SqlConnection MyConnection = new SqlConnection("Data Source=myserver.mydomain.com\instance_name;Initial Catalog=myDB;User Id=myUserID;Password=myPassword;"); where instance_name is the name of the instance or SQLEXPRESS for a SQL Server Express installation (tipically).

6. Have a look at the inner exception and eventually error codes that are specified in those exceptions (and their inner exceptions and so forth and so on...) and if still nothing helps you, post them here.

 

[RRinTetons]

Based on example code from connectionstrings.com I tried

SqlConnection MyConnection = new SqlConnection("server=rtpsql.jacksonhole.com;database=JHMR_Custom;UID=sa;PWD=t3t0ns;Trusted_Connection=False");

Adding the "Trusted_Connection=False" at the end seemed to have made the difference.

Then I tried it again, and got the error. Then I got it to work again!

Huh?

On a first test I get the Vista dialog asking whether I want to allow the application to connect to the Internet (to reach the data server). Even though I approve the connection I get the error. If I run the application again (without rebuilding the solution) I don't get the Vista dialog, and the connection, and the query, succeeds.

As a matter of fact, I can change it back to the original syntax (without the explicit 'Trusted_Connection...' element) and it STILL works, so I think the syntax question is moot. I'm guessing the application is getting some sort of recognition from Vista on the first attempt. The recognition remains until I rebuild the solution and the exe is no longer OK with Vista.

So, is there a way to tell the OS that my app is cool? Is that handled by an installer making some registry tweaks?



-
Richard Ray
Jackson Hole Mountain Resort