I want to make a client that connects to a SQL Server and has full access.
How do I make it so others wont see the connection string for the SQL Server and access it via other db editors? I know that .NET apps can be decompiled so I'm afraid for my server.
Here's a not-so-secure reference implementation of a simple connection string encryption/decryption mechanism.
First of all, encode your connection string by using the Base64 encoding scheme.
Unencoded Connection String:
server=localhost\SQLEXPRESS2012;database=testdb;uid=testuser;pwd=supersecret
Base64 Encoded Connection String:
c2VydmVyPWxvY2FsaG9zdFxTUUxFWFBSRVNTMjAxMjtkYXRhYmFzZT10ZXN0ZGI7dWlkPXRlc3R1c2VyO3B3ZD1zdXBlcnNlY3JldA==
After this, the corresponding line in your App.config file should look like this.
<add name="TestDb" connectionString="c2VydmVyPWxvY2FsaG9zdFxTUUxFWFBSRVNTMjAxMjtkYXRhYmFzZT10ZXN0ZGI7dWlkPXRlc3R1c2VyO3B3ZD1zdXBlcnNlY3JldA==" providerName="System.Data.SqlClient" />
Finally, modify your DbContext to be able to create database connections by using the encoded connection string.
using System;
using System.Configuration;
using System.Data.Common;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Text;
namespace TestApp
{
public class TestDb : DbContext
{
public TestDb() : base(CreateConnection("TestDb"), true)
{
}
static DbConnection CreateConnection(string dbName)
{
string encodedCs = ConfigurationManager.ConnectionStrings[dbName].ConnectionString;
string decodedCs = Encoding.UTF8.GetString(Convert.FromBase64String(encodedCs));
return new SqlConnection(decodedCs);
}
}
}
As you've noticed, this implementation uses the Base64 encoding scheme which can easily be reversed by the end users if they know what they are doing.
A publicly available client with full db rights is usually never a good idea so you might want to rethink that.
Here's a nice MSDN article referring to your problem:
https://msdn.microsoft.com/en-us/library/89211k9b(v=vs.110).aspx
If you deploy the client to Azure you could also store confidential config information separate from your app from within the portal.
Some more ideas:
Retrieve the connection string via separate remote service and handle it using a SecureString instance.
Or just don't give the client a connection string at all by hiding the entire SQL db behind a service wall.
Using certificates is always a great way to avoid having to store secrets in configuration.
SQL Azure has a new feature that allows you to use a certificate to access your database. See this link for more details: https://azure.microsoft.com/en-us/documentation/articles/sql-database-aad-authentication/ (section 7.3)
