How to restrict access and to connect to a single SQL Azure database only

2018-01-27 | Toni Pohl

As of today, many organizations use various Microsoft Azure services. SQL Azure databases are popular among them. Often, IT Administrators want to allow departments or vendors full access to just one specific SQL Azure database and to restrict their access to other resources. See here how this works.

Use SQL Management Studio or SQL Operations Studio

When working with SQL databases, installing the SQL Management Studio (SSMS) is very helpful. You can get the latest version from Download SQL Server Management Studio (SSMS) for free. Alternatively, you can use the new lightweight and cost free SQL Operations Studio.

Need a SQL Azure server or a new database? Create one!

If you don’t already have a SQL Azure server running, login to the Azure Portal. Create a new SQL server and add one or more databases, similar as here. In this sample, I created new resource as playground. Otherwise, skip this step.

image image

The goal

In this sample, I want to allow access for other users (as departments, apps or vendors) just for a specific database. In this sample we see that on the SQL server tpdbserver1 and there are two databases running: AdventureWorks and TimeTracking.

image

The goal is to create a new connection just for accessing TimeTracking. That can be done by creating a new user in the TimeTracking database. So, let’s open SSMS on my local machine and then connect to server tcp:tpdbserver1.database.windows.net,1433 with the SQL server admin user.

image

Add your client’s IP address to the SQL server firewall

Since the SQL server firewall forbids unknown locations, I need to add my client’s IP address to the allowed firewall rules. Kindly, SSMS takes care of that and I just need to sign-in with the entitled Azure subscription user to add that…

image

After signing-in, I can add the local IP (range) address. See also Add your local IP address to a SQL Azure Firewall easily.

Full access with the SQL server administrator

Now I have full access with the SQL service administrator. In SSMS, we see the two databases and have full access to the database server (as we would have with a database administrator and a local installed SQL server).

image

Perfect. Now let’s create the new user for handing that one over to the vendor.

Create a new user for one specific database

So, I select the TimeTracking database and press STRG + N (or click the "New query" icon) and add the two TSQL statements for creating a new user named dbusertimetracking with a custom password in that database and for assigning the db_owner role to that user as follows:

CREATE USER dbusertimetracking WITH PASSWORD = 'MySecretPassword123';
ALTER ROLE db_owner ADD MEMBER dbusertimetracking;

Press F5 or Execute these commands.

image

Of course, this can be done in the UI as well, but this is faster… So, we are done here

Try it

First, let’s try it out. In SSMS, open a new Database Engine connection.

image

Fill out the server name tcp:tpdbserver1.database.windows.net,1433 and the login dbusertimetracking and the password.

image

Since that user has just (full) access to the TimeTracking database, don’t forget to click Options and type the Connect to database name TimeTracking as shown here.

image

Now click Connect. If everything works, you should be connected to the specific database.

The result

As we see, this new connection is made with the dbusertimetracking user. This user sees just this one database TimeTracking - and AdventureWorks is NOT visible.

image

In the other connection on top, the SQL server administrator user sqladmin can manage the full SQL server and all databases as shown above.

Hand over the new connection data

For your vendor, you can now hand over the following data:

Don’t forget that each client needs to be added to the SQL server firewall. So, every location’s IP address must be added as well (as seen above and here).

Need a connection string for an app?

If the database connection is used for an app, check out the database’s Connection strings. The Azure Portal delivers that for various database connectors as here.

image

In our sample for an ADO.NET connection, we just would need to replace the placeholders with our dbusertimetracking and the password.

Server=tcp:tpdbserver1.database.windows.net,1433;Initial Catalog=TimeTracking;Persist Security Info=False;
User ID={your_username};Password={your_password};
MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

You then can hand over the full connection string to the vendor (again, remember the required IP firewall settings).

Summary

With SQL Azure the platform provides Database-as-a-Service. One SQL server can handle multiple databases. Each database should have it’s owner and - for controlled access - restricted permissions. Often, using a SQL server elastic pool can be useful as well. This article shows how to implement security for handing over a user or a connection string just for a specific database resource. Go for it!

Categories: Azure, Cloud, Developer, English, Microsoft, SQL Server, Security

Source: https://blog.atwork.at/post/2018/01/27/How-to-restrict-access-and-to-connect-to-a-single-SQL-Azure-database-only