Why am I not able to establish a gateway connection with my SQL Database?

[HamidBee]

I am trying to create a connection with my local SQL Server DB via the gateway but I keep getting an error. Here is a screenshot:

 

 

 

Does anyone know why I am getting this error?

 

[lbendlin]

I assume you are using a personal gateway for that.  Most likely your PC has a firewall rule preventing inbound access to port 1433.

Note: Your setup may be ok for proof of concept, but it is unsuitable for a production environment.

[HamidBee]

Cool. I followed the following steps:

1. Turned off all firewalls (Temporarily)

2. Restarted the gateway

3. Tried to test the connection in Power BI Service

Unforunately, I received the same error. Unless I'm entering something above incorrectly. Is the Host Name seen above supposed to match the server name?. Is there anything in the images above that doesn't seem right?. Thank you.

[lbendlin]

On your PC does "(localdb)"  actually resolve to localhost (or to 127.0.0.1) ?

[HamidBee]

Apologies I may not have understood the question. Are you asking if (localdb) is equibalent (replacable) to localhost?

[lbendlin]

Yes, as I said the gateway need to "see"  your data sources. In your case the gateway doesn't seem to understand that (localdb) means on the same machine.

[HamidBee · Accepted solution]

It definitely doesn't understand localdb. Quick update (with the gateway already installed):

1. I uninstalled SSMS and SQL LocalDB

2. I then installed SQL Express and reinstalled SSMS

3. I launched SSMS

3. I connected to the desktop server using the syntax DESKTOP-NAME\SQLEXPRESS

4. I reset the password for sa login and enabled it (correct me if I'm wrong but connecting to a gateway via the BI Service requires some sort of authentication)

4. I created a database and added a table, created a draft copy of an rdl report and published it onto the power bi service

6. On report -> manage -> added the details for the gateway

7. Opened the report. All good.

I will never use localdb, I've definitely learnt my lesson. 

[HamidBee]

I see. Perhaps it may be helpful if I run through how I created the local db. I followed the instructions seen on this video:

https://www.youtube.com/watch?v=elNmNDoHhqY

1. Opened command prompt

2. Typed:

sqllocaldb create "local"

The command above then created a local db with the server name: "(localdb)\local

I'm not sure if this is the traditional way of creating a local db. You may be right it may be this that's causing the issue. 

[HamidBee]

I've added some more information above, perhaps it may be helpful to my problem. I'd kindly like to ask if their is anything you would suggest me trying. I set up the localdb on my own personal laptop and at the moment I am the only user to the db. Thanks.

[lbendlin]

All gateway cluster members must be able to see both the on-premise data source and the Azure cloud. In your case one of your cluster members cannot see the database server. This might be due to network issues, firewalls, or configuration mismatch.