How to connect an Azure database to third-party services
Software-as-a-Service (SaaS) encircles perhaps the most comprehensive set of solutions for software development. Databases, scheduled jobs, firewalls, DevOps, you name it. The area of SaaS is teeming with new and exciting ways to work and mainstream the delivery of our technological solutions here at DMObjects Web and Design, which is why I want to share a few tips to start using these types of services.
One of the most powerful cloud SaaS suites at the moment is Microsoft Azure. It provides:
- Databases
- Both Linux and Windows virtual machines
- Kubernetes
- Mobile apps
- VPNs
- File storage
- Machine learning
- and many more.
It’s incredible, actually. I could go on all day about the plethora of things that Microsoft knows that are of great value for the future of software development. But, because attention span is limited I’ll go ahead and teach you how to connect an Azure database to ASP.NET, Python, Power BI, and SQL Server Management Studio.
We’ll go from creating a free trial account on Azure all the way towards connecting our database and performing a query.
Creating a free Microsoft Azure account
First, let’s create a free Microsoft Azure account. You can do so here. You will need a valid credit card (which will not be charged for this tutorial) and a valid telephone number to verify your identity by SMS.
Free trial accounts have their limitations, mainly:
- 12 months of access
- 30 days of service
- $200 credit
Keep in mind that all your testing should take place within the 30-day period. After that, your data will remain read-only and you’ll need to upgrade to keep on using Azure services as a whole. Regarding the $200 credit, that gets depleted as your account’s performance requirements increase. For example, whenever you run jobs in your virtual machines, a certain amount will be deducted from your credit.
After creating your account, you should be greeted by this screen:
Now, head to https://portal.azure.com/. You will be offered a tour of the suite; you can do this later.
Creating the database
Click “SQL Databases” and then select “Add”
Create a new resource group, add a database name and create a new server. Take note of the information provided, you’ll need it later!
You will be asked to review the creation of the database, alongside the estimated cost per month of using it.
Finish the process and wait for the database to deploy.
Now, select “SQL Databases” on the left-side panel, choose your newly-created database server and click “Set server firewall.”
Get your IP address by searching “what’s my ip” on Google and take note of it.
Then, create select “ON” on “Allow access to Azure services” and create a new rule that includes your IP address.
Save your changes and once again, select “SQL Databases” and choose your new server. Now, click “Connection strings” and take note of the connection strings. Different services will require different connection strings.
Go back to “SQL Databases” and now select “Query Editor.” Put your admin credentials and we will see the SQL Query editor. Let’s create a test table called “Person” and insert “Will Williamson” as our test instance. Note that the Query Editor allows us to export data as .csv and .xml, how cool is that?
We’re ready to start connecting to our database!
Connecting to Azure SQL Database with ASP.NET
Open up Visual Studio and create a new ASP.Net Web Application (.NET Framework)
First, let’s connect our database to Visual Studio, so we can make tests in the future without having to mess around with our source code.
Click “Tools” and then “Connect to Database…”
Select “Microsoft SQL Server” and click “Continue”
Now, we must enter the credentials that we noted earlier. Keep in mind that there are three main components to this process: Server, database and username.
Now that Visual Studio is connected, the next step is to actually connect out ASP.NET app to the database. To do that, we head down to the Web.config file and we create a new block called “connectionStrings”. Inside, we must add the ADO.NET connection string, alongside our database credentials.
We can test the connection by creating a SQL Query file and running it directly from Visual Studio.
Connecting to Azure SQL Database with Python
I will assume that you are acquainted with developing in Python in your environment of choice. If not, you should read up on setting up virtual environments and installing packages.
First, install PyODBC, which is the Open Database Controller for Python, which allows it to connect to SQL Server instances. You can do so by opening a command prompt and entering:
>pip install pyodbc
After successfully installing the package, open your IDE of choice and write the following script:
import pyodbc
AZURE_STRING = <Insert your Azure SQL Server ODBC connection string here, with its required credentials>
cnxn = pyodbc.connect(AZURE_STRING)
cursor = cnxn.cursor()
cursor.execute(“SELECT * FROM Person”)
row = cursor.fetchone()
print(row)
Now, run the script and you should see our test instance appear onscreen.
Connecting to Azure SQL Database with Power BI
Open up Power BI Desktop and create a new file by clicking the “File” menu and selecting “New.” Click “Get data.”
You will be presented with all the compatible data sources for Power BI. Scroll down and select Azure SQL Database.
Now enter the name of the server and the name of the database.
Note that there are two options under “Data Connectivity mode”:
- Import: This imports the result of queries from the database. It is useful if the Power BI visualizations do not need to be updated all the time.
- DirectQuery: This performs live queries to the database, which is more intensive on processing power but allows for live updates of your data.
After clicking “OK”, you will be prompted to insert your credentials. Enter the same credentials you used in the earlier steps.
Click “Connect” and wait for the authentication process.
Eventually, you should see our test instance on the screen!
Connecting to Azure SQL Database with SQL Server Management Studio
Connecting to your Azure SQL Database with SQL Server Management Studio (SSMS) is very easy, too. You just open up SSMS and enter the server name and your admin credentials. Then, hit “Connect.”
SSMS has the issue that, when dealing with online services, it may be a tad too slow. It is a fully-equipped suite for interacting with your SQL Server databases, so it must get a lot of data from the current instance to work correctly.
When developing, it is always better to work locally, and after you’ve done your tests, then it is advised to pass on to a remote test environment. This is because query latency is oftentimes ignored, mainly because it is pretty much invisible in local testing environments. Take your previsions!
After the connection has been established, you can create a “New Query” and see our neat little test instance.
Conclusion
I hope that this little guide has been of use. Cloud-based computing is sure to provide many solutions to software developers and in the case of Microsoft Azure, opening a free trial account to test its services is just so easy. It is hard to resist the wide variety of tools they offer.
For web and app design solutions feel free to visit my company’s website at https://www.dmobjects.ca.
We offer competitive prices and we specialize in working with startups and small businesses. Our services include web hosting, corporate emails, e-commerce platforms and much more.
