Azure SQL Database Application

Question

I am building a desktop application that requires a SQL Database. I wish to offer clients 2 options: On Premises and Cloud database. For cloud databases I intend to use azure sql server. The requirements are:

I wish to have some sort of "Azure DB Credentials" for clients to use, that can only access their database (which I will provide).
I do not wish for the clients to have to install any other software to use the application.

My problem is, in testing I have run into the "IP Address not allowed to connect to server". This poses a problem:

Clients will have many different IP addresses, therefore I cannot allow individual IP addresses.
I do not want to open all IP Addresses due to security concerns.

As bizarre as this sounds, I cannot find a ready-to-use solution to this problem. I am a new programmer, and maybe have not googled enough... That being said, this seems like a simple problem without a simple obvious solution.

The best solution I have come up with is an embedded Open-VPN Client within the application. However, that seems unnecessarily complicated. Is there a better way?

Did you think about the Azure firewall? To add the client IP or IP address range to limit the access of Azure SQL database. — Leon Yue
– Leon Yue, Commented Jul 15, 2019 at 0:50

Alberto Morillo · Accepted Answer · 2019-07-16 13:48:57Z

Use token-based authentication on your application for simplicity.

public async Task<string> GetAccessTokenAsync(string clientId, string clientSecret, string authority, string resource, string scope)
{
    var authContext = new AuthenticationContext(authority, TokenCache.DefaultShared);
    var clientCred = new ClientCredential(clientId, clientSecret);
    var result = await authContext.AcquireTokenAsync(resource, clientCred);

    if (result == null)
    {
        throw new InvalidOperationException("Could not get token");
    }

    return result.AccessToken;
}

Creating a SQL Connection using token.

public async Task<SqlConnection> GetSqlConnectionAsync(string tenantId, string clientId, string clientSecret, string dbServer, string dbName)
{
    var authority = string.Format("https://login.windows.net/{0}", tenantId);
    var resource = "https://database.windows.net/";
    var scope = "";
    var token = await GetTokenAsync(clientId, clientSecret, authority, resource, scope);

    var builder = new SqlConnectionStringBuilder();
    builder["Data Source"] = $"{dbServer}.database.windows.net";
    builder["Initial Catalog"] = dbName;
    builder["Connect Timeout"] = 30;
    builder["Persist Security Info"] = false;
    builder["TrustServerCertificate"] = false;
    builder["Encrypt"] = true;
    builder["MultipleActiveResultSets"] = false;

    var con = new SqlConnection(builder.ToString());
    con.AccessToken = token;
    return con;
}

You don't even need to worry about token expiration since AzureServiceTokenProvider takes care of caching.

Learn about it on this article.

Community · Accepted Answer · 2020-06-20 09:12:55Z

0

Did you think about the Azure firewall? To add the client IP or IP address range to limit the access of Azure SQL database.

Set server firewall on portal:

Firewall settings: to add the client IP or IP address range to provides the access the database.

Fore more details, please see:

Hope this helps.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jul 15, 2019 at 1:04

Leon Yue

16.5k1 gold badge17 silver badges26 bronze badges

1 Comment

Jeremy Morren Over a year ago

I am aware, however my problem is that the client's IP addresses will be changing (some will be roaming etc). therefore it is impractical to specify the IP Addresses, and I do not want to open all addresses.

Collectives™ on Stack Overflow

Azure SQL Database Application

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related