Why can't Power BI connect to my cloud source???

Aug 16, 2023

Why can't Power BI connect to my cloud source???

You have a cloud data source like Azure SQL Database. Why can’t Power BI connect to it? They are both in the cloud! Adam shows you some things to consider and options you have!

What is an on-premises data gateway?
https://docs.microsoft.com/data-integ…

What is a virtual network (VNet) data gateway?
https://docs.microsoft.com/data-integ…

📢 Become a member: https://guyinacu.be/membership \r
\r
\r
\r
Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.\r
\r
🎓 Guy in a Cube courses: https://guyinacu.be/courses\r
\r
\r
LET’S CONNECT!\r
*******************\r
\r
— http://twitter.com/guyinacube\r
— http://twitter.com/awsaxton\r
— http://twitter.com/patrickdba\r
— http://www.facebook.com/guyinacube\r
— https://www.instagram.com/guyinacube/\r
— https://guyinacube.com\r
\r
Gear\r
🛠 Check out my Tools page - https://guyinacube.com/tools/\r
\r
#PowerBI #Network #GuyInACube

Content

0.353 -> - Yooo! Adam Saxton with Guy in a Cube, and in this video,

3.66 -> we're gonna look at why you might need a gateway

6.54 -> for a cloud data source.

7.98 -> Does that even make sense?

9.12 -> Power BI's in the cloud too, why can't they?

12.72 -> Let's take a look.

14.092 -> (upbeat music)

19.5 -> If your finding us for the first time,

20.82 -> be sure to hit that subscribe button

22.17 -> to stay up-to-date with all the videos

24.09 -> from both Patrick and myself.

25.77 -> Cloud data sources, what do I even mean by that?

27.99 -> So this could be Azure SQL Database,

30.39 -> Azure Analysis Services,

31.95 -> maybe something that's in Google's cloud or Amazon's cloud,

35.25 -> just a data source that resides

37.26 -> in one of those big three cloud infrastructures

40.29 -> of some kind.

41.123 -> Let's take Azure SQL Database as an example.

42.93 -> They're both in Microsoft's data center,

45.54 -> and so you would think if I've got an Azure SQL Database,

48.15 -> I can just connect using Power BI without needing anything.

51.36 -> They're both in the cloud; they're both in Azure.

53.76 -> The challenge is the network security and the network path.

57.72 -> Can they talk to each other?

59.37 -> So enough all this talking.

61.29 -> You know how we like to do it here in Guy in a Cube.

63.03 -> Let's do what? Let's head over to my machine.

65.07 -> So I've got a data set

66.9 -> that's up in the Power BI service itself.

69.18 -> We can see here

70.013 -> that my data source name is My Adventure Works,

72.69 -> and it's sitting on giacdemodata.database.windows.net.

76.53 -> It's just an Azure SQL Database.

78.03 -> And so I'm just gonna go and put in my credentials here,

80.58 -> and we'll see that we get an error, right?

82.59 -> So it's telling me, "Look, this IP address isn't allowed

85.41 -> into the actual database

87.24 -> and you gotta go run some firewall rules."

89.1 -> If your organization is serious about security,

91.86 -> at the minimum,

92.79 -> they've probably got a firewall sitting right there.

94.86 -> So they're not just gonna let anyone connect

97.08 -> to the database, right?

98.25 -> You've gotta be on an approved list.

99.87 -> But what about Power BI?

101.13 -> Power BI is not part of my local network subnet

105.48 -> or coming from my IP,

107.1 -> and so how do I get Power BI

110.13 -> to actually be allowed to connect to this database?

113.19 -> Most data sources will have this option

115.68 -> from an Azure perspective where I can just say,

117.907 -> "Allow Azure services and all resources."

120.51 -> I can save that.

121.44 -> Power BI should be covered by this.

123.15 -> I will put a disclaimer that not every single thing will

127.29 -> or will not be in this list,

128.73 -> so it may or may not work depending

130.71 -> on what you're dealing with.

131.67 -> For Azure SQL Database, this should work.

133.86 -> So let's go ahead and try this again.

135.24 -> Bam, and now I'm in.

136.8 -> I've allowed Power BI to actually use this data source,

140.22 -> at which point, I can go and refresh my database,

143.16 -> and everything will be fine.

144.57 -> So let's try it again. Bam, it succeeded.

147 -> What if your data source is in a different cloud?

149.01 -> Again, it's a network path thing.

150.54 -> Can Power BI talk to that data source,

153.72 -> and the answer there's gonna be no

155.1 -> if there's some firewall or something in place

157.32 -> in that other cloud provider,

158.82 -> so we need a gateway to go do that.

160.77 -> And you can configure the gateway

162.33 -> to use those cloud sources in most cases.

165.27 -> Let me talk about the most common thing

167.1 -> that I see when talking to customers.

168.99 -> From the Azure side of this,

170.49 -> it's going to be a VNet or virtual network.

173.52 -> I have Azure SQL Database that's also inside of a VNet,

176.85 -> and from that perspective, it can't connect directly.

179.94 -> There is no direct endpoint.

181.47 -> I've actually disabled public access to this database.

184.53 -> It can only be connected to from the private endpoint,

187.47 -> so let's take a look at that.

188.303 -> So I've got this data set that is published,

190.26 -> and it's connecting to the Azure SQL Database

192.57 -> that's inside of the virtual network.

194.34 -> And we can see here, it's a different server name.

196.41 -> So let me go and edit credentials here, we'll do OAuth.

198.9 -> And we'll see here that we get an error basically saying,

200.977 -> "Look, we've denied public network access.

203.85 -> Power BI cannot get to this."

205.53 -> This one's a little different

206.52 -> because it's not a firewall rule.

208.59 -> It's not like I can go just check

210.48 -> Allow Azure services to connect.

212.76 -> That won't work here because it's in a virtual network.

215.07 -> There is no public access allowed,

217.17 -> and Power BI by default's gonna be coming in

219.66 -> through that public endpoint.

220.89 -> And in this case, we've got two options here.

222.84 -> One is I can actually use the on-premises data gateway

226.2 -> in a virtual machine that's inside of that virtual network.

229.02 -> The other option we have is the VNet gateway

231.63 -> that's available through Power BI as well,

233.67 -> and I can allow that VNet gateway

236.25 -> to be a part of the VNet

237.84 -> that the SQL database is inside of as well.

241.05 -> And that's actually what I've done in this case, let's look.

242.91 -> We'll go to Gateway,

244.05 -> and I'll choose on-premises data gateway, and you'll see,

245.97 -> I've got this giacvnet-powerplatform VNet gateway,

250.02 -> so that's what that icon is.

251.19 -> It's an actual VNet gateway.

252.78 -> And I've configured my data source there.

254.67 -> Let's go and apply that.

255.75 -> And now if I come back and refresh,

257.82 -> there it is, it works successfully.

259.86 -> And let me know in the comments

260.88 -> if you want a video on how to even set up the VNet gateway.

263.58 -> There are some steps to get that going,

265.53 -> and some of it is not necessarily intuitive, right?

268.35 -> It's Azure networking.

269.52 -> And so the big thing here is the network path itself.

272.7 -> Power BI has to be able to connect to that data source.

275.22 -> And so without a gateway,

276.78 -> there's no other way for Power BI to talk to it.

279.03 -> It's gotta be public access points,

280.71 -> and most organizations lock that down,

283.53 -> and they won't allow that

284.79 -> because it's not a secure approach for data sources, right?

287.61 -> There's hackers out there.

288.69 -> There's malicious-intent folks

290.4 -> that wanna get to your data,

291.96 -> and so you wanna have something in play

293.82 -> that protects the data.

295.11 -> Typically, that's why organizations disable public access

298.17 -> and you have to go through specific network routes

300.54 -> to get there.

301.41 -> Let's say you're using Snowflake that's in AWS

304.2 -> or you're using BigQuery that's in Google Cloud.

306.9 -> In those cases, if you're using a VPC

309.54 -> or virtual private cloud on either Google or Amazon,

312.27 -> there's no way for Power BI to directly integrate with that.

315.6 -> So we have the VNet gateway, but that's specific to Azure.

318.6 -> There is nothing for Google Cloud or AWS.

321.99 -> And so in that case, no matter what you do,

323.7 -> you're gonna need the on-premises data gateway sitting

325.89 -> in a VM or a bare metal machine somewhere

329.16 -> that's actually running,

330.12 -> and that's how Power BI is gonna be able

331.65 -> to get to your data.

332.64 -> Now you can place that VM anywhere, right,

334.92 -> in Google's cloud itself or in AWS's cloud,

337.8 -> and it's sitting there and it has access to that database.

340.23 -> Or I've seen organizations

341.34 -> where they have network routes in place

343.14 -> that go through their actual physical corporate network,

345.84 -> a VPN of some kind.

347.4 -> Then from the gateway,

348.36 -> the gateway has to have network access to the data source.

351.84 -> So that's the big thing here you gotta remember is,

353.67 -> can I physically talk to that data source

356.52 -> from a network perspective?

357.75 -> If the answer is no, then you gotta get a gateway in place.

360.45 -> I know it's a complex topic,

361.71 -> but there are options for you to get this to work.

363.93 -> All right, I wanna hand this over to you.

365.07 -> What do you think, have you struggled

366.48 -> with understanding some of these network concepts

369.15 -> or are you wondering why you just can't use

371.49 -> your cloud data source directly with Power BI

373.95 -> and maybe this helped you?

374.91 -> Let me know in the comments below. I'd love to hear it.

377.16 -> If you liked this video,

378.03 -> be sure to hit that big thumbs up button.

379.62 -> Smash it if you so desire.

381.3 -> If it's your first time here, hit that subscribe button.

383.97 -> And as always from both Patrick and myself,

386.37 -> thank you so much for watching.

387.93 -> Keep being awesome, and we'll see you in the next video.

Source: https://www.youtube.com/watch?v=IhQVOS34QKA