Connect to SQL server

Like (0) Share

Report

Posted on 6 Jul 2016 12:21:54 by PiebePost

Dear users,

I want to connect my SQL Server Express 2014 to PowerApps, but I can't manage. I found some old articles on the internet, amongst which:

https://social.technet.microsoft.com/Forums/en-US/1cb2a545-0174-412d-9dde-8eab326b8a82/powerapps-connection-with-an-sql-server?forum=PowerApps

But these articles give only outdated information.

When trying to add a SQL Azure connection, I'm asked for a SQL Connection String. What's further worth mentioning is that, although the connection is named "SQL Azure", the whole explanation in the connection box speaks of "SQL Server":

Microsoft SQL Server is a relational database management system developed by Microsoft. Connect to SQL Server to manage data. You can perform various actions such as create, update, get, and delete on rows in a table.

What should I do?

Categories:

Connecting to Data

I have the same question (0)

Summarized by AI

AI generated content may be incorrect

Did you find this summary helpful?

All responses (7)

Answers (0)

arisprabhawa Microsoft Employee on 10 Oct 2016 at 04:07:59

Like (0)

Report

Re: Connect to SQL server

Hi,

I had the same issue here, after it shows "default" database, it provide error stating "No table found". Here is the more detail:
Invalid Mashup Expression using supplied values.
inner exception: Microsoft SQL: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Was this reply helpful? Yes No
PiebePost 60 on 09 Jul 2016 at 17:35:20

Like (0)

Report

Re: Connect to SQL server

The "default" database gave an error stating "No tables found". But hey, good news! Interesting enough, the description of the connection I made with my SQL server already changed from Azure SQL to SQL server.

Was this reply helpful? Yes No
hintbw-abc 117 on 08 Jul 2016 at 21:30:39

Like (1)

Report

Re: Connect to SQL server

A couple of notes:

I found that I had to click on default in order to get to the tables I had in my database (though I was using the SQL Azure connection). If you haven't already, try clicking on the default that shows up when you try connecting and see what message is returned.

Additionally, it sounds like you are trying to connect to a local version of SQL Server (which is a different "connection" than the Azure SQL DB) - unless that SQL Server is exposed to the Internet (unlikely and not recommended). The good news is that PowerApps just announced support for connecting to on-premise (i.e. local, not Azure SQL DB) SQL Servers. For your SQL Server Express source you mentioned, you may want to look at this blog post and walkthrough - https://powerapps.microsoft.com/en-us/blog/connect-to-your-on-premises-data-sources-using-on-premises-data-gateway-from-powerapps/

Was this reply helpful? Yes No
hngdev 304 on 07 Jul 2016 at 14:23:10

Like (1)

Report

Re: Connect to SQL server

Unfortunately, that's what I am thinking.
I never tried to use Microsoft SQL Server on local network for PowerApps before so I can't give you the correct answer.
This is the connection string format I used to connect to my Azure SQL.
Server={your_server},1433;Data Source={your_data_source};Initial Catalog={your_database};Persist Security Info=False;User ID={your_username};Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;
You can try it out.

Was this reply helpful? Yes No
PiebePost 60 on 07 Jul 2016 at 10:50:53

Like (0)

Report

Re: Connect to SQL server

Hello hngdev, thanks again for your reply.

Does that mean that I will be unable to connect to my SQL server directly? In the meanwhile, I did find a connection string that made me connect to my server but it won't let me show the databases (it only shows the "default" database, which is a non-existing database).

Was this reply helpful? Yes No
hngdev 304 on 06 Jul 2016 at 17:09:51

Like (0)

Report

Re: Connect to SQL server

I think you may have some misunderstandings here.
As far as I've known, you are trying to connect to MS SQL Server and what the studio offers is SQL Azure (a cloud-based database, if I am correct).
They are two different things.
If you're using SQL Azure, you can login to your portal.azure.com > SQL database > [pick your database] > there will be an option to show connection strings

Was this reply helpful? Yes No
PiebePost 60 on 06 Jul 2016 at 12:22:55

Like (0)

Report

Re: Connect to SQL server

Here is a screenshot, for clearification:

Was this reply helpful? Yes No