How do I properly connect to SQL Server 2014 database through SqlClient / SqlConnection?

5gfr0r5j 于 5个月前发布在 Perl

关注(0)|答案(2)|浏览(61)

I am converting a Java application to .NET and C# and I am in the process of moving away from JDBC to SqlClient . I'm testing the database connection and it's failing due to one of the 2 errors shown below (sometimes it's the one, sometimes it's the other):

//Debug info
Loading filename: BREConfig.txt
Loading properties from file: BREConfig.txt
Properties Loaded: 23
Setting Config Values...
Server=tcp:###;Initial Catalog=Wc3Online;User ID=####;Password=####;TrustServerCertificate=True; Encrypt=False;

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=13952; handshake=1056;

//Debug info
Loading filename: BREConfig.txt
Loading properties from file: BREConfig.txt
Properties Loaded: 23
Setting Config Values...
Server=tcp:####;Initial Catalog=###;User ID=####;Password=###;TrustServerCertificate=True; Encrypt=False;

Error

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

I am able successfully connect to the database through RDC using SQL Server authentication as shown here:

I have some code using SqlClient that connects to this database server:

SqlConnection testConn = BREDBConnectionUtil.GetConnection();
Console.WriteLine(BREDBConnectionUtil.GetConnectionString());  

try
{
    testConn.Open();
    Console.WriteLine("Database server connected, using " + testConn.Database);
    testConn.Close();
    Console.ReadLine(); 
} 
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
    Console.WriteLine(ex.StackTrace);
    Console.ReadLine();
}

// connection string values pulled from text file and stored in objects
private static SqlConnection con = new();
private static readonly BREConfiguration config = BRESession.GetConfiguration();
private static readonly Logger logger = new();

// Connect to SQL Server 2014
private static readonly string connectionString = "Server=tcp:" + config.GetDbConnection() + ";Initial Catalog=" + config.GetDbName() + ";User ID=" + config.GetDbUser() + ";Password=" + config.GetDbPassword() + ";TrustServerCertificate=True; Encrypt=False;";

// returns a SqlConnection to connect to SQL Server 2014 database
[MethodImpl(MethodImplOptions.Synchronized)]
public static SqlConnection GetConnection()
{
    con.ConnectionString = connectionString;      

    try 
    {
        if (con.ConnectionString.Length == 0) 
        {
            con = new SqlConnection(connectionString);
        }
    } 
    catch (Exception e) 
    {
        //print err
    } 

    return con;
}

sql-server

来源：https://stackoverflow.com/questions/77572991/how-do-i-properly-connect-to-sql-server-2014-database-through-sqlclient-sqlcon

2条答案

按热度按时间

k10s72fa1#

The connection string should look like this:

//Connect to SQL Server 2014
 private static readonly string connectionString = $"Server={config.GetDbConnection()};Initial Catalog={config.GetDbName()};User ID={config.GetDbUser()};Password={config.GetDbPassword()}";

You probably don't want to specify tcp: at the front of the string for the SqlClient provider. Also, Encrypt=False is already the default, and if you are not doing encryption (why not?), you don't need to trust any server certificates.

One other important thing to understand with database connections is the C# ADO.Net API does connection pooling for you as part of the provider. This means you should NOT try to create and re-use one connection object in your app. Rather, it really is better (a LOT better) to create and promptly dispose a new connection object for most queries... ideally as part of a using block.

With that in mind the GetConnection() method should look more like this:

public static SqlConnection GetConnection()
{
    return new SqlConnection(connectionString);
}

No need to over-think it. You'll generally have higher-level code handling exceptions already anyway, and giving yourself a new object each time also means you don't have to worry about synchronization anymore.

We could then test the connection like this:

Console.WriteLine(BREDBConnectionUtil.GetConnectionString());  
using var testConn = BREDBConnectionUtil.GetConnection();
            
try
{
    testConn.Open();
    Console.WriteLine($"Databse Server Connected, using {testConn.Database}");
    // no need to call .Close(). 
    // The using directive takes care of it automatically **and more safely**
    // at the end of the scope block
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
    Console.WriteLine(ex.StackTrace);
}
// but if you DO want to call .Close() manually, or not have a using directive, 
// it should be HERE, as part of a finally block
// (hint: this was true in Java, too)
Console.ReadLine();

But again: this is only a test. Since we create a new connection object for each query, there's not a lot of point in testing or opening the connection up front.

赞(0）回复(0）举报 5个月前

ikfrs5lh2#

This issue seems to be an incompatibility with security protocols. Our SQL Server uses TLS 1.2 and my laptop I am trying to connect with uses TLS 1.3.

My guess is that the TCP handshake is successful while the TLS handshake is failing, and SQL server is being blamed for it resulting in a TCP error, (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

It will require further testing to be sure though.

赞(0）回复(0）举报 5个月前

我来回答

How do I properly connect to SQL Server 2014 database through SqlClient / SqlConnection?

2条答案

相关问题

热门标签

最新问答