3

I'm trying to connect to a DB on a server using C# but with no luck.

I tried using this:

public static string m_ConnectionString =
    @"Network Library=dbmssocn; Data Source=*server ip*,*port*; database=*db name*; " +
    @"User id=*db username*; Password=*db pass*;";
public static SqlConnection myConnection = new SqlConnection(m_ConnectionString);

I get this error:

Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=9343; handshake=5654;

when I used myConnection.Open();

I tried also to set the timeout to int.MaxValue and it didn't work.

Improve this question
2
  • look this link : technet.microsoft.com/en-us/library/… Commented Sep 1, 2013 at 20:11
  • 2
    Interesting: C# is the same musical note as Db. Commented Oct 22, 2014 at 11:32

2 Answers 2

Reset to default
14

A very good source for SQL Server (and many other) connection strings is http://www.connectionstrings.com/sql-server/. Depending whether you are connecting through ODBC, OLE DB or Native Client, you have to choose another connection string.

Try 

Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;

or

Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;

There are a lot of options to choose from, depending on the exact SQL Server version, the security type and many more.

UPDATE

First you have to choose a data access technology.

  • .NET Framework Data Provider for SQL Server (SqlConnection), Is the preferred way of accessing the SQL Server from .NET code. (See When to use the SQL Native Client for a comparison)

  • Native Client: Is a very fast way of accessing the SQL Server and supports the new features, as it accesses the SQL Server TDS protocol directly and works for non .NET code. It should be preferred for non .NET code.

  • ODBC: Is relatively fast and compatible to a lot of different databases. Choose this one if the data base type might change in future or if you are accessing "exotic" databases.

  • OLEDB: For SQL Server it is relatively slow and will be depreciated by Microsoft.

Then you have to choose between SQL Server Authentication (User/Password) and Windows Authentication. I would choose the latter if possible. With Windows Authentication the SQL-Server assumes that if you logged in successfully to Windows you are a trusted user. The Windows user name will then be mapped 1 to 1 to a SQL-Server user. Of course this user then must still have been granted the rights requested for the operations that he will perform on the SQL Server (like SELECT, INSERT, UPDATE, DELETE). If the DBA didn't install Windows Authentication, you will have to go with uid/pwd.

This worked for me:

string connectionString =
    "Data Source=192.168.123.45;Initial Catalog=MyDatabase;Integrated Security=SSPI;";
using (SqlConnection connection = new SqlConnection(connectionString)) {
    using (SqlCommand command = new SqlCommand(
                 "SELECT Region FROM dbo.tlkpRegion WHERE RegionID=30", connection)) {
        connection.Open();
        string result = (string)command.ExecuteScalar();
        MessageBox.Show("Region = " + result);
    }
}
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

How can I know all these? Can you tell me what exaclty do I need to know and how can I find it? Thanks.
You are missing either Integrated Security=SSPI or User Id=myUsername; Password=myPassword. Note also that the port 1433 is the standard port and can be dropped.
I got 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 - 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.). What should I do? BTW, thank you very much for helping me out. @Olivier Jacot
Two things: In order to use a IP address, the server must be allowed to answer TCP connections (Configuration Manager, Surface Area Configuration). On the client side: If the server is remote, the Client Tools must be installed. If it's local you can use "Data Source=(local);" or "Data Source=ServerName;" instead of a IP Address.
ServerName is its IP, doesn't it?
|
2

I think that Data Source=*server ip*,*port*; should be Data Source=*server ip*:*port*;, replacing , with :. But if the port is not specific, I don't think you really need it. Also you're not defining a driver, I don't know it this works without it.

Also a advice: look up LINQ to SQL or ADO.NET Entity Data Model. Those can really simplify use of databases and using LINQ you can write a query inside code which is a lot similar to sql and Visual Studio also helps with intellisense so you don't have to remember all table and column names.

Improve this answer

2 Comments

what you mean by "driver"? And it should be "," and not ":".
Look at the other question, @Olivier Jacot-Descombes already explained what I wanted to write so I won't reinvent the wheel here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.