C # - Secure connection to remote SQL Server?

Question

I know that it is easy to connect to a SQL Server database, but I'm not sure how I should do it remotely and at the same time .. in a safe 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 SQL Server is installed on Windows VPS, and I'm going to give my software to other people, and I want them all to access this SQL server ... how can I do this without opening the SQL Server ports? Since, as far as I know, opening ports will lead to hacking, since all people will be able to remotely connect to this server.

Answer 1

This question reminds me of me when I started ...

No matter what you do, do not connect directly to the database, because for a direct connection you will need to store the database connection strings (and passwords) in your application ... you can fool it, make it as obscure as it is, as you like, it will not matter ... You will essentially pass the keys to the lock.

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

Personally, I would use the ASP.NET Web API , this is the right tool for the job. There is a small learning curve for him, but just stick to it and you will find out in a few days. Start with these PluralSight videos , they are a great resource that is completely free thanks to Microsoft, and they will definitely get you started!