Cannot connect Power BI with SQL server

[Rolina]

Hi, I found issues with connecting Micsrosft SQL Server with Power BI. I do not have any experience in such connections, so I am blindly doing different steps but I simply don't uderstand what I am doing, hence I am not able to know what's going wrong.

So, I read I need gateway installed, and I did. Install an on-premises data gateway | Microsoft Learn

Then I set up gateway name and key based on another article.

And then, what's next? I found this article Add or remove a gateway data source - Power BI | Microsoft Learn but I am unable to folllow steps, I can't find setting button. Do I need any additional version or access to be able to complete this?

I tried to put in Power Bi credintials that theye were given to me around the server, but no luck with any connections. Instead of Server name, I have Port number, but I reckon this is the same. I put passowrd and user's name into Power BI server query, but I encounter error message, hence I think this gateway needs some further steps I haven't completed yet?

Thanks!

[v-jianpeng-msft]

Hi, @Rolina 

I wish you all the best. Previously we have provided a solution to help you solve the problem. Since we haven't heard back from you yet, I'd like to confirm if you've successfully resolved this issue or if you need further help?
If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

Best Regards

Jianpeng Li

[Rolina]

hi, unfortunately I haven't heard from data base owner yet, hence I couldn't check the solution yet.

[v-jianpeng-msft]

Hi @Rolina 

Thank you for your feedback, and we look forward to you getting connected to your database leader sooner rather than later and then trying out this solution.

Best Regards

Jianpeng Li

[v-jianpeng-msft]

Hi, @Rolina 

For more information about gateway configuration, you can check out my article below:

Solved: Connection Error with On-Premises Data Gateway - Microsoft Fabric Community

Next, I'll show you how to establish a gateway connection to SQL Server in Power BI service:

Next, configure this gateway connection for your semantic model:

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Rolina]

Hi, I went through your steps, thanks so much for such a detail instruction. I set up connection in Power BI online, but unfortunately this does not resolve the problem, that I am unable to connect with this data base in Power BI Desktop in the first place, so I can create my dashboard from scratch. In my other response in this trail, I shared error message I got.

[v-jianpeng-msft]

Hi, @Rolina 

In a computer we can have many port numbers.
When we want to access certain services, we need to have the database server IP/name, port number to access the contents. When we only have a port number, Power BI and other applications don't know which computer to go to access the database that corresponds to the port number you provide.
For example, I have VM1, VM2, VM3, VM4. When we enter the port number directly in VM4 to access the database, this causes the computer to not know which machine to access the database. Typically, the server name/IP is used to identify a computer.

If you only have a port number, you need to communicate with your IT to get the IP/name of the SQL Server server.

Best Regards

Jianpeng Li

[Rolina]

I will reach out to data base owner and ask for additional information. I will accept your solutions if I get luck with connection. Thanks for your support!

[v-jianpeng-msft]

Hi, @Rolina 

If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

Best Regards

Jianpeng Li

[Rolina]

I am awaiting additional information from data base owner when it comes to server name. When I have it and won't struggle with connection anymore, i will accept replies I got as Solution. Thanks!

[v-jianpeng-msft]

Thank. We look forward to hearing from you.

Best Regards

Jianpeng Li

[divyed]

Hello @Rolina ,

Follow below steps :

1. Open Power BI => Get Data => SQL Server 

=> Fill in details and choose correct mode

=> Select objects and click on Ok

and you are done.

Gateway is required to referesh data from power bi service. you can configure refresh schedule as per need :

You can select the refresh frequency on a regular schedule, e.g. daily, or customize to several time slots.

Enabling the scheduled refresh feature also allows you to perform a manual refresh on-demand

I hope this helps.

Cheers

[Rolina]

Hi, I mentioend that I got no luck with this connection. I tried houndrets times to go through it (as this is very similar to any other Power BI getting data step, which I do on a daily basis). The problem is with connection:

1. Open Power BI => Get Data => SQL Server 

but different errors appears. E.g. I have port number instead of server name (but I read this is the same and it does not matter), so when I put numers only, this is what I get:

So i tired putting like "port:12345", this brings me to the next step, of adding cretindials.

I have been given user and password so I put them:

again this is what I got:

[v-jianpeng-msft]

Hi, @Rolina 

I think you have to have the sql server name and port number. Here I take the sql server of my VM1 (port 1433) machine as an example.

Here is the SQL Server server name to look for vm1:

Now I'm connecting to VM1's sql server in my other machine:

Format:

servername,portNumber

If you can connect to this sql server in SSMS, there is no problem. Next, let's connect it in Power BI desktop:

Power BI desktop accesses our database as normal. In summary, you need to have the SQL Server server name and the corresponding port number, which is required.

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.