Sql Server 2005 localhost System DSN not working

Question

Hi,

I've got an C# .Net 3.5 executable that runs on a local machine and makes database calls to a server running SQL Server 2005. I've added a System DSN using the Data Sources manager on the local machine called "localserver" (with the driver set to SQL Server). When I create the DSN, I can test the connection to the server successfully.

I've added localserver to my connection string in my executable, and I get an error "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"

Here is my connection string

SqlConnection mainConnection = new SqlConnection(@"Data Source=localserver;database=Closing_Balance;User id=foo;Password=bar;");

The server I am connecting to does allow for remote connections. When I replace localserver with the server name, it connects fine, but this program will be at multiple locations with multiple server names, so I need to make the data source dynamic.

Any ideas as to why this is happening?

Thank you,

Aaron

EDIT:

I've also tried using "localserver,1433" as my data source, but I get this error: "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.)"

EDIT:

Thank you for your responses. To solve my problem, I made a small method to gather the servername using an odbc connection.

private string getServerName()
    {
        OdbcConnection conn = new OdbcConnection(@"DSN=localserver;Uid=foo;pwd=bar");            
        string cmdText = "select @@servername";
        OdbcCommand cmd = new OdbcCommand(cmdText, conn);
        conn.Open();
        string server = cmd.ExecuteScalar().ToString();
        conn.Close();
        conn.Dispose();
        return server;
    }

I can then send the servername to the SqlConnection. Thanks again for your input.

Answer 1

SqlClient (ie. SqlConnection) has absolutely nothing to do with ODBC. As such using an ODBC Data source Name in the SqlClient connection string will get you nowhere fast.

You can configure the server name in app.config and build the connection string using SqlConnectionStringBuilder. At deployment, you change the exe's or the user's .config file appropriately.

Answer 2

As Remus said, DSN has nothing to do with SqlConnection. Instead use this connection string:

http://www.connectionstrings.com/sql-server-2005#1

Also read this:

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx

An Excerpt from the above post:

To connect to a local computer, specify "(local)" for the server. If a server name is not specified, a connection will be attempted to the default instance on the local computer.

I would repeat that SqlConnection has nothing to do with DSN,