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.
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.
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.
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...
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).
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.
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.
Fill out the server name tcp:tpdbserver1.database.windows.net,1433 and the login dbusertimetracking and the password.
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.
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.
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:
- the database server connection: tcp:tpdbserver1.database.windows.net,1433
- the database user: dbusertimetracking
- the database user's password: MySecret...
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.
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!