none
How c# app connect to sql server on another pc in local network?

    Question

  • How c# app connect to sql server on another pc in local network?
    Is connection string same when server on local pc?
    Is there diversity if server is sqlexpress?

    Thanks for any help.
    Thursday, April 12, 2007 6:29 AM

Answers

  • Did you enable the TCP/IP (also Named Pipes) protocol in Remote SQL Express (normally those are disabled). Sometimes this be the cause for above mentioned problem

     

    To enable these options, go to:

     

    Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Configuration Manager -> SQL Server 2005 Network Configuration -> Protocols for SQLEXPRESS

     

    [You can do the same using SQL Server Surface Area Configuration too]

     

    Check following link also:

    http://www.sql-server-performance.com/faq/sqlviewfaq.aspx?topicid=1&faqid=143

     

    Kusala

    Sunday, April 15, 2007 7:18 PM

All replies

  • Hi Milicica,

    Well, Here is the simple way to connect to any SQL server in the LAN :

    Just create a .udl file say test.udl.
    Open it and it will show you tab view with 4 tabs.
    Go to Provider tab and select the provider you want to connect to.
    For SQL server, select Microsoft OLE DB Provider for SQL Server.
    Go to Connection tab and see the values in the server name combobox.
    It will be filled with the available servers on the LAN.
    Select the one you want to connect to.
    Enter username and password.
    If there is a password, check allow saving password.
    Select the database on the server.
    And Test the connection.
    Click OK and the dialog will close.
    Now Open that file in the notepad.
    And you will have the connection string you can use to connect to the specified database.

    This way you can create the connection string without the help of any IDE or other applications.


    I created the connection string while writing this :

    Provider=SQLOLEDB.1;Persist Security Info=False;User ID=[Username];Password=[Password];Initial Catalog=[Database Name];Data Source=[Computer Name]\[Server Instance Name]

    I think there is no change in the connection string if the Server is local or on LAN besides the Computer Name and Server Instance Name part.

    And I dont think there is any diversity if the Server is SQL Express.

    Just Replace the plcaeholders with according details.

    I hope it helps.

    Jay Joshi,
    decyclone@gmail.com
    Thursday, April 12, 2007 7:30 AM
  •  milicica wrote:
    How c# app connect to sql server on another pc in local network?
    Is connection string same when server on local pc?
    Is there diversity if server is sqlexpress?

    Thanks for any help.


    Answers:

    1) The same way the operating system communicates over the network tcp/ip.

    2) The connection string format will be the same, but just like anything local vs remote, the server name or ID (whether local or remote) has to be resolved, either by name or an IP. That doesn't mean one couldn't reference a local box by its own IP or naming conventions. It depends on how nice DNS is to you whether one has to decorate a database server name with domains and other items.

    3) What do you mean by diversity? The express version is just a trimmed down version of the 2005 database. In most repects it is the same as its more robust cousin.
    Friday, April 13, 2007 4:26 AM
  • Well it certainly will be a bit different. I think a comparison on two scenarios would be good to comprehend.

     

    Calling an SQL connection from a local machine:

    connectionString="Data Source=local;Database=your_database;UID=your_username;PWD=your_password"

     

    Calling an SQL connection from a remote machine:

    connectionString="Data Source=remote_machine;UID=your_username;PWD=your_password;Initial Catalog=your_database"

     

    The main difference is the connection type the system uses. While connecting to a remote machine you need SQL Server Authentication and while connecting to a local server Windows authentication would be enough. So you might even consider omitting the username and password part while connecting to a local machine.

    Friday, April 13, 2007 9:46 AM
  •  utkuozan wrote:

    While connecting to a remote machine you need SQL Server Authentication and while connecting to a local server Windows authentication would be enough.



    That is an excellent observation. Due to the complexities of impersonation, the remote connections are different.....that has been the case for my database connections.
    Friday, April 13, 2007 2:08 PM
  • However, if the connections are in a Domain environment, then Windows Authentication works even remotely assuming the servers are set up properly.  This is exactly how I use SQL Server.  The server sits on one machine, and the client runs on a PC.  It uses Windows Authentication, but you need to set Integrated Security = true.
    Saturday, April 14, 2007 8:20 PM

  • Thanks for your answers.

    I use below conection string, but there's appear error in remote connection. On same pc connection is OK.

    mConnection = new SqlConnection(@"data source=servername\sqlexpress;" +
                "database=databasename;" +
                "UID=user;PWD=pass;");

    Error mesage is:

    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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)


    Server Authentication is SQL Server and Windows Authentication mode.
    Allow remote connections to this server is set.

    What may cause this mistake?

    Sunday, April 15, 2007 11:49 AM
  • Did you enable the TCP/IP (also Named Pipes) protocol in Remote SQL Express (normally those are disabled). Sometimes this be the cause for above mentioned problem

     

    To enable these options, go to:

     

    Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Configuration Manager -> SQL Server 2005 Network Configuration -> Protocols for SQLEXPRESS

     

    [You can do the same using SQL Server Surface Area Configuration too]

     

    Check following link also:

    http://www.sql-server-performance.com/faq/sqlviewfaq.aspx?topicid=1&faqid=143

     

    Kusala

    Sunday, April 15, 2007 7:18 PM

  • Thanks a lot Kusala, there was a problem.
    Monday, April 16, 2007 2:29 PM
  • -First try a connection like this;
     ConnectionString = "Data Source=ComputerName\\SQLEXPRESS ;Initial Catalog=yourDatabaseName;Persist Security Info=True;User ID=yourID;Password=yourPassword;TrustServerCertificate=False;User Instance=False";

    -Second make sure that the server's FireWall is closed or allows sql connections.

    -Third make sure that SQLEXPRESS and SQL SERVER BROWSER is  in running state.

     -Fourth make sure that TCP/IP option in sql sever configuraton part is ENABLED. and also
    right click on this part and you will see the ip addresses part and just enable the IP1

    - Lastly change the registry "HKLM\\SOFTWARE\\MICROSOFT\\Microsoft SQL Express\\
    MSSQL.1\\MSSQLSEVER\\LoginMode=2" because login Mode 1 is windows authentication.

    So in the client part application connection string is normally read only. It look likes this;
     public string YourConnectionString {
                get {
                    return ((string)(this["YourDBConnectionString"]));
                }       
            }

    To Enable this, just add a set part;
     public string YourConnectionString {
                get {
                    return ((string)(this["YourDBConnectionString"]));
                }    
                 set
                {
                    this["YourDBConnectionString"] = value;
                } 
            }

    i hope this helps you.
    Mehmet Altan Toksöz
    Sunday, June 17, 2007 10:29 PM