On my data engineer way … like from song DJ Tiesto – On My Way …. It’s cool, doesn’t it?
OK, in this article I am going to show you how to:
- set custom firewall rule
- create SQL Database logins
- set database-level permissions
Ok, enough. Let’s go!
Creating a SQL Database
Go to Azure SQL databases and create WineDB.
You need to follow this path and set the instance of database on your server:
Home > SQL databases > Create SQL Database
After that open Query editor (to open you need to set firewall rules, read next paragraph )
copy, paste and execute my code:
https://github.com/aiflops/LocalDBMigToAzure/blob/master/Create-WineDb – to create tables
You can see WineDb and tables after quick refresh.
Set firewall rules
SQL Database Firewall will prevent all connections to SQL Database unless explicitly allowed via a
firewall rule. This includes your attempts to connect to SQL Database even using the SQL Database
Follow these steps to add your IP address to the server-based firewall rules:
- Log in Microsoft Azure at https://portal.azure.com/
- Click SQL Databases in the vertical navigation pane on the left.
- Choose WineDb.
- Click “Set server firewall” in the horizontal navigation pane on the right.
- You can choose Add client IP, it automatically adds your IP address in Firewall settings
- In the RULE NAME text box beneath the list of existing rules, type Home Office. Note that the
rule name cannot contain either forward slash (/) or backslash (\) characters.
In the START IP ADDRESS and END IP ADDRESS boxes, type the IP address range of your home
Click Save button
Create SQL Database logins
First of all we need to copy full Server Name. We can find it in SQL database in the top. Figure below.
Then open SSMS, in the dialog window paste Sever Name from Azure, secondly type your root login and password.
Once connected, your SQL Database server will be listed in the Object Explorer pane.
Right-click on the server name, and choose New Query.
This opens a new query window connected to the master database.
Type the following T-SQL statement:
CREATE LOGIN DbLogin WITH PASSWORD='<Password>’
You have now created a new SQL Database server login; however, this login isn’t authorized to do
anything with the SQL Database server. You now need to create a user for this login and then either
grant the user server-level or database-level permissions.
Creating read only user
When using SQL Database, you don’t want to grant all users server-level permissions.
If you have an application that only needs to read data from a database, you don’t want to give the user the application is connecting as permissions to write data.
In this case, you can create a new user who has read-only database permissions.
Right-click the WineCloudDb database, and choose New Query. This opens a new query window connected to the WineCloudDb database.
Type the following T-SQL statements into code window connected to WineCloudDb:
CREATE USER DbUser FROM LOGIN DbLogin
EXEC sp_addrolemember ‘db_datareader’, ‘DbUser’
When you want to connect database again to avoid error you need to type in dialog window DbLogin credentials. Then Options – > Connection Properties -> Connect to WineDB
Click the Connect button. SSMS connects successfully
You have now connected to the WineCloudDb SQL Database using SSMS with a limited access
account that has only db_datareader permissions. As explained, this is useful when you need to give a
team member read-only access to a production database to diagnose issues or conduct some analysis
on the data.
Ok, so in this article you’ve done a great job:
Now you know how to:
- create database,
- insert data,
- create user login
- add user permission
- login on new login to Azure SQL form local machine.
We’ve made next step on our Data Engineer ROAD.
Thank for your attention!
Please put more smile in digital world