Tag Archives: Azure SQL

Sharing a Microsoft Access Database Online With Multiple Users

Today, I thought I’d briefly touch upon a more and more common question, which is:

How can we share a Microsoft Access database online between multiple users?

In today’s world, sharing data is simply a necessity! So it is only natural to have the need to share Microsoft Access data with people outside of your network, over the Internet…

I previously posted about this issue in my article:

Access Back-End Location - WAN, Online Server, OneDrive, DropBox, ...

There have been a few recent threads, some of which I’ve been involved in: Access Database on OneDrive for Business corruption Unrecognized database format Splitting MS Access on a LAN work well, but on a wan run very slow. Wan communications has tested positive very good. Why would MS access 2007 run slow over a wan. Slow MS Access DB over WAN in which people are inquiring about running a standard MS Access database over a WAN, an Online Server, Shared drives (OneDrive, DropBox, …) and the likes. The Reality of MS Access Databases Microsoft Access databases are only meant…

Continue reading →

Today, I wanted to continue the discussion a little more.

So How Can We Do It Exactly?

Continue reading →

Add An IP Address To An Azure SQL Database Firewall

Azure, Azure SQL Databases are quite secure and try to limit access to minimize security risks. This, is a great thing. That said, as developers, we often need to remotely access such tools to be able to perform our work.

So how can we do so?

The solution, in the case of Azure SQL Databases, is to create an exception for your IP address in the Firewall rules. Once you authorize your IP address, assuming you have proper login credentials, you will be able to go about your business and connect remotely to that Azure resource.

Continue reading →

Wanna Try Azure SQL Database For Free!!!

I recently came across an announcement from Microsoft and thought I’d share it here.

It would seem that Microsoft has finally made it possible for anyone to try out Azure SQL Database and even perform development for free!

You can read all the details at:

Continue reading →

SQL Statements, To Square Bracket Or Not?

So what exactly is the story with square brackets in SQL Statements?

What’s the difference between

SELECT [FirstName] FROM [Contacts]

and

SELECT FirstName FROM Contacts

Today, I’m going to explore this for a moment.

What are Square Brackets Use For In SQL?

First off, when we mention ‘Square Brackets’ we are talking about [ and ].
Continue reading →

Listing Azure SQL Database Users and Roles

In a continuation on yesterday’s article

Adding New Users To An Azure Database

Recently, I’ve been doing much more work involving Azure SQL databases. In one instance, I was reviewing the security setup of a client’s existing web application and notice that they were using the default Microsoft Azure account to connect to the database. I’ve seen this multiple times. It is quite natural to setup the resource,…

Continue reading →

I thought we should touch upon how we can retrieve a list of users and their assigned roles from an Azure SQL Database Instance.

None of this is rocket science, it is more a question of being aware of what system tables and stored procedures exists. So let’s look at each aspect:

List of Azure SQL Database Users
List of Database Users by Role

Continue reading →

Adding New Users To An Azure Database

Recently, I’ve been doing much more work involving Azure SQL databases.

In one instance, I was reviewing the security setup of a client’s existing web application and noticed that they were using the default Microsoft Azure account to connect to the database. I’ve seen this multiple times. It is quite natural to setup the Azure Resource, deploy the Azure Database and simply use the account you have by default. That said, there is a security concern for me with such a setup, for a couple of reasons, mainly:

Such a user can actually alter any aspect the database itself (and the master database as well) and no user should ever have such capabilities.
If ever compromised, say a hackers gains access to the app’s config file, they can actually log into the Azure Portal and do anything they want.

So I logged in and created a new SQL Login User just for their web application to use, thus limiting, as much as possible, any potential damage that could occur through the web app itself.

Creating a New Azure SQL Database User

It’s actually remarkably easy to create a new login user and only takes 3-4 simple SQL commands.
Continue reading →

Access – Connecting to an Azure SQL Database

More and more we want/need to be able to use an Access database and not be restricted to the confines of a local office LAN. One potential solution can be to migrate the back-end (the tables) to Microsoft Azure and relink the front-end to the Azure SQL database, thus creating what is known as an Access hybrid database.

The beauty here is the back-end database is hosted in the cloud (in this case in the Microsoft cloud) and accessible to anyone that can run Access and whom has an Internet connection. The drawback here is performance, although query tuning can greatly improve this aspect as well as using Azure SQL database views, and pass-through queries in Access.

The Overall Process

There are 2 key steps to this process:

Creating an initial File DSN
Linking the tables in Access

Continue reading →

Azure SQL – Automate Database Maintenance

Recently, I have been knee deep in doing a MySQL migration to Azure SQL. I quickly learnt that Azure SQL required a lot more attention than its MySQL counterpart. So an Index and Statistic maintenance plan was needed, and urgently to keep things humming along smoothly.

Much to my amazement, Azure SQL did not offer the typical SQL Server Agent! Everything in Azure is extra!!!

So I started doing a little digging and found a couple options. One potential solution which I was exploring was to create an Automation Account to run a Maintenance Stored Procedure on a daily/weekly basis. I only mention this as it may be an avenue worth exploring for some.

In my case, because the client I was helping already was using flow and was already using the premium SQL Server connector, I decided to go the Power Automate Flow route to automate the maintenance. Thus providing a solution at no extra cost to them.

The Overall Idea

My process is very straightforward:

Create an Azure SQL Stored Procedure that performs the necessary index and statistic optimization
Use Power Automates Flow to run it on the desired schedule

Continue reading →