How to validate a user entered Sql Instance name entered in c#

Question

Service app project in Visual Studio Pro 2019. Using .NET Framework 4 Client Profile and C#

The user enters the sql instance name in the config file using key/pair value of SQL_INSTANCE "SQLEXPRESS".
Now, the user can set the SQL_INSTANCE to something else if required.

I have googled and most of the result discuss using SQL Configuration manager to see SQL_INSTANCE.
I need to be able to programmatically check it in the Service app to verify if the user entered Instance is
valid. How do I do that ? Didn't see any discussion on how to accomplish this.

Thanks.

Answer 1

You could do a brute force connect with a time-out.

Setup a CancellationTokenSource, here to time-out in two seconds

private static int _timeOut = 2;
private CancellationTokenSource _cancellationTokenSource =
    new CancellationTokenSource(TimeSpan.FromSeconds(_timeOut));

Method to test connection

public class Operations
{
    public static async Task<(bool success, Exception exception)> Connect(string connectionString, CancellationToken cancellationToken)
    {

        try
        {
            await using var cn = new SqlConnection(connectionString);
            await cn.OpenAsync(cancellationToken);
            return (true, null);
        }
        catch (Exception e)
        {
            return (false, e);
        }

    }
}

Call it and deconstruct the results

var (success, exception) = await Operations.Connect("user connection string goes here",_cancellationTokenSource.Token);

Side note, there is always a permission issue, connection string may be good but not assessible to the user.

Answer 2

There is no way to validate the connection string without attempting to connect to the server.

Answer 3

I need to be able to programmatically check it in the Service app to verify if the user entered Instance is valid.

Also for remote SQL Server (instances)? Open a SQL Server connection to the entered instance name and if it fails you know the name isn't valid. That's the way most application works, like SSMS.

Answer 4

@Darryl Hoar , Welcome to Microsoft Q&A. you could get the instance name from the registry then you could validate the user;s input.

Here is a code example you could refer to:

 private void button1_Click(object sender, EventArgs e)  
        {  
            RegistryView registryView = Environment.Is64BitOperatingSystem ? RegistryView.Registry64 : RegistryView.Registry32;  
            List<string> list = new List<string>();  
            using (RegistryKey hklm = RegistryKey.OpenBaseKey(RegistryHive.LocalMachine, registryView))  
            {  
                RegistryKey instanceKey = hklm.OpenSubKey(@"SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL", false);  
                if (instanceKey != null)  
                {  
                    foreach (var instanceName in instanceKey.GetValueNames())  
                    {  
                        Console.WriteLine(instanceName);  
                        list.Add(instanceName);  
                    }  
                }  
            }  
            string instancetext = textBox1.Text;  
            if(list.Contains(instancetext))  
            {  
                MessageBox.Show("The instance name is correct");  
            }  
            else  
            {  
                MessageBox.Show("The instance name is wrong");  
            }  
        }  

Best Regards,
Jack

---

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.