C# - Connect to remote SQL Server securely?

Question

I know it's easy to connect to a SQL Server database however I'm not sure how I'm supposed to do it remotely and on the same time.. with a secure way .

SqlConnection sqlConnection = this.sqlcon();

SqlCommand insertCommand = new SqlCommand("use " + database_telecaster.ToString() + " SELECT Top 1  sid from dbo.Item order by sid desc", sqlConnection);

sqlConnection.Open();
insertCommand.ExecuteNonQuery();

SqlDataReader reader = insertCommand.ExecuteReader();

while (reader.Read())
{
    MaxSid = (reader.GetInt64(0) + 100).ToString();
}
reader.Close();
sqlConnection.Close();

SQL Server con function :

public SqlConnection sqlcon()
{
    var doc = new XPathDocument(Application.StartupPath + "/DBConn.xml");
    var navigator = doc.CreateNavigator();

    var serverName = navigator.SelectSingleNode("//appsettings/servername");

    var username = navigator.SelectSingleNode("//appsettings/username");
    var password = navigator.SelectSingleNode("//appsettings/password");
    var database = navigator.SelectSingleNode("//appsettings/database");

    object[] objArray = new object[] {
            serverName , database, username , password 
    };

    return new SqlConnection(string.Format("Data Source={0};Initial Catalog={1};User Id={2};Password={3};MultipleActiveResultSets = True", objArray));
}

Assuming that the SQL Server is on a Windows VPS installed and I'm going to give my software to different people and I want them all to access that SQL server... how I can do that without opening the ports of the SQL Server ? Because as far as I know opening the ports will lead to getting hacked since all people will be able to connect remotely to that server .

Answer 1

This questions reminds me of me when I was getting started...

Whatever you do, do not connect to the database directly, because to connect directly you would have to store the database connection strings (and passwords) within your application... you could obfuscate it, make it as obscure as you like, it wont make a difference... You'll essentially hand over the keys to the castle.

Instead, you need to start learning how to create an API, that authenticates the client and connects to the data layer on the clients behalf, performs the operations requested and then returns the result.

Personally, I would use ASP.NET Web API, it's the right tool for the job. There is a slight learning curve to it but just stick with it and you'll have it figured out in a few days. Start off with these PluralSight videos, they're an excellent resource which is completely free thanks to Microsoft, and they'll certainly keep you busy!