blog.atwork.at

news and infos about microsoft, technology, cloud and more

Connect to a SQL Azure database with Power-BI

From time to time (yes it’s almost that time of the year again, when we use this opening statement)… user’s have difficulties to connect to a SQL Azure Database with Power-BI. So I decided to post this article as a brief hint (and as reminder for myself), how to connect from Power-BI.

First of all, allow the connection with TCP port 1433 to your client IP address in the Azure portal at https://portal.azure.com. Click “Add client IP” and add it to the firewall settings of the SQL server as in this sample here.

image

Check the SQL connection string. Here there are two methods: SQL credentials or Active Directory credentials usage. This article describes the access with SQL credentials (which are logins in the database).

Just for completeness, you can add a new user in the database itself as here (as db_owner or with another role):

CREATE USER myDBUser WITH PASSWORD = 'MyPassword';
ALTER ROLE db_owner ADD MEMBER myDBUser;

So far so good. If you already have a existing user and this is an ASP.NET web app, you find the connection string usually in web.config. This might look as here:

Server=tcp:MyDBServername.database.windows.net,1433;Initial Catalog=MyDBName;User ID=MyDBUser;Password=MyPassword;…

…where the MyDB* values represent your access data, just in case, you want to use the credentials from there.

Ok, let’s assume you have a valid username and password for the database and the Azure firewall allows the connection. You can try the connection with SQL Management Studio (or any other client) as well to ensure the connection works.

Now open the Power-BI Desktop, open the “Get Data” menu and connect to “Microsoft Azure SQL Database”.

image

Use the full Server name (tcp:MyDBServername.database.windows.net,1433) and the database name (MyDBname).

image

After “OK”, switch to “Database” and then add the credentials from before (MyDBuser and MyPassword).

image

Click “Connect”.

If the connection works, the following step allows to select the data you want to use in your Power-BI dashboard as in this sample.

image

Just as tip: We usually create an own user as above, add views and set permissions just to the report views to restrict the access for users who actually only want to do reports.

That’s it. Enjoy working with your data from your SQL Azure database in Power-BI.

Loading