-1

I query a database as follows:

string connString = "Data Source=ServerName;Initial Catalog=AdventureWorks;User 
     id=UserName;Password=Secret;";    
SqlConnection conn = new SqlConnection();
SqlCommand cmd = new SqlCommand("select * from Orders", connString);
conn.Open();

The issue is that the server may require the settings TrustServerCertificate and Encrypt

So if i run the above it will fail with error

  SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the login process. 
  (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)]

but if i have the connString as

"Data Source=ServerName;Initial Catalog=AdventureWorks;User 
 id=UserName;Password=Secret;Encrypt=true;TrustServerCertificate=true");

then it will connect without a problem and the select will run.

So I may need to change the connection string on the fly Is there a smart way I can make a modification to the above code to check that if the error is returned I then retry the select with the new , modified connection string ?

Jennifer
  • 31
  • 6
  • So you dont know in advance if the server you are connecting to require TrustServerCertificate or not? – Magnus Feb 23 '23 at 08:48
  • that ight i dont know , but if i get an exception error as above i need to readjust the connection string and try again – Jennifer Feb 23 '23 at 08:58

2 Answers2

2

Place your code into a try block and catch the SqlException. Then, change your connection string and retry the connection.

string connectionString = "Data Source=ServerName;Initial Catalog=AdventureWorks;Userid=UserName;Password=Secret;";
string queryString = "SELECT * FROM Orders;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(queryString, connection);
    try
    {
        connection.Open();
        using(SqlDataReader reader = command.ExecuteReader())
        {
           while (reader.Read())
           {
              Console.WriteLine(String.Format("{0}", reader[0]));
           }
        }
    }
    catch (SqlException)
    {
        command.Connection.ConnectionString = "Data Source=ServerName;Initial Catalog=AdventureWorks;Userid=UserName;Password=Secret;Encrypt=true;TrustServerCertificate=true";
        connection.Open();
        using(SqlDataReader reader = command.ExecuteReader())
        {
           while (reader.Read())
           {
              Console.WriteLine(String.Format("{0}", reader[0]));
           }
        }
    }
    catch (Exception ex)
    {
        Console.Write($"Error: {ex.Message}")
    }
 }
ɐsɹǝʌ ǝɔıʌ
  • 4,440
  • 3
  • 35
  • 56
  • How does that actually re-run the select cmd , it just changes the connection string ? – Jennifer Feb 23 '23 at 09:01
  • would it be better to try and connect and if it gets a certificate exception change the strig an re-run , because when it is re-run with the modification , it will work ? – Jennifer Feb 23 '23 at 09:03
  • That is what it is doing. – Magnus Feb 23 '23 at 09:04
  • ah yes sorry , you are right , thankyou – Jennifer Feb 23 '23 at 09:05
  • @Jennifer You're not running the command in your code as you are stuck when opening the connection. You'll need to send the command text to the connection and build a SqlDataReader once the connection is open. See edited answer. – ɐsɹǝʌ ǝɔıʌ Feb 23 '23 at 09:28
0

Enhancing the solution provided by @ɐsɹǝʌ ǝɔıʌ:

    string connectionString = "Data Source=ServerName;Initial Catalog=AdventureWorks;Userid=UserName;Password=Secret;";
    string queryString = "SELECT * FROM Orders;";
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand(queryString, connection);
        try
        {
            connection.Open();
        }
        catch (SqlException)
        {
            command.Connection.ConnectionString = "Data Source=ServerName;Initial Catalog=AdventureWorks;Userid=UserName;Password=Secret;Encrypt=true;TrustServerCertificate=true";
            connection.Open();
           
        }
        finally
        {
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine(String.Format("{0}", reader[0]));
                }
            }
        }
    }
Safee
  • 58
  • 4