3

I have a UWP app that needs to connect to a SQL Server Express database. On my dev box, I don't have any issues.

On the client PC, I installed SQL Server Express, created the database, setup the users and logins.

When I run the app, and try to connect to the database, I get an error:

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: TCP Provider, error: 40 - Could not open a connection to SQL Server)

The code I'm using to connect is:

...
using (SqlConnection connection = new SqlConnection(ConnectionStringTextBox.Text.Trim()))
{
    try
    {
        connection.Open();
        MessageBox.Show("Connected successfully!");
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}
...

Using this same code on a desktop app, on the client machine, the connection succeeds.

I have set up the UWP app to use version 1809 of Windows 10.

The client and my dev box are both version 2004.

Package.appxmanifest file has the following capabilities checked:

  • Enterprise Authentication
  • Internet (Client & Server)
  • Internet (Client)
  • Private Networks (Client & Server)

Windows Firewall has the correct ports open. In SSMS, I have enabled allowing remote connections, enabled TCP.

I don't know what I'm doing wrong or what is going wrong. This same UWP app works great on my dev box.

Any advice is appreciated.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Andrew Chang
  • 109
  • 1
  • 9
  • 2
    Well, did you debug/print/msgbox the value stored in `ConnectionStringTextBox.Text.Trim()`? What is the value? What happens when you try to connect to that server/instance name from SSMS on the same machine that the UWP app is running on? Hint: this may have nothing to with the app specifically and certainly not the version of Windows. – Aaron Bertrand Jul 28 '20 at 00:58
  • Have you seen this? https://stackoverflow.com/questions/32885735/how-to-connect-to-sql-server-database-from-a-windows-10-uwp-app – Dai Jul 28 '20 at 01:00
  • I need to see the connection string. You need to use Integrated Security = true which uses Windows Credentials. First on client machine use SQL Server Management Studio to connect to database to make sure credentials work. You c# code will not work unless first SSMS works. Then once you get SSMS working c# will be easy. Integrated security requires the credentials to be the same on local and remote machine. So you need to put client and server in the same group to get windows credentials to work.The User need to be in both client and server machine. I usually create a windows user group. – jdweng Jul 28 '20 at 01:01
  • @jdweng The OP does **not** have to use Integrated Security. I don't know where you got that idea from. I agree it's more convenient and means we can avoid passwords, but outside of a controlled AD Domain environment or when connecting to a SQL Server over the Internet (which is stupid, but sometimes necessary) you have to use Login+Password authentication. – Dai Jul 28 '20 at 01:07
  • Login/Password may not work when SQL Server is on a remote machine. – jdweng Jul 28 '20 at 07:04
  • Here's the connection string that I use: ```Data Source=myMachineName,49172\SQLEXPRESS; Initial Catalog=TestDb; User Id=myId; Password=123456;``` I've also tried different port numbers... – Andrew Chang Aug 25 '20 at 00:20

3 Answers3

0

If you're trying to connect to a SQL Server instance on the same machine it won't work because UWP applications are sandboxed and cannot connect to localhost over TCP/UDP - while connections to remote machines will succeed. I think this is stupid, but it's By Design.

As a workaround, you can configure SQL Server to use Shared Memory or Named Pipes and those approaches should work.

Update:

I forgot that there is now a Capability you can add to your AppX manifest to enable local-loopback connections (only for TCP, it doesn't work for UDP).

Dai
  • 141,631
  • 28
  • 261
  • 374
  • Do you mean they can _only_ connect to localhost? If they can connect to anything _but_ localhost, that's kind of the opposite of a sandbox and, if that is the case, I agree with you - that is stupid. – Aaron Bertrand Jul 28 '20 at 01:01
  • 1
    @AaronBertrand Correct, they can connect to anything *except* `localhost`. The argument is that users' computers may be running poorly-written *non-UWP* programs which have local-loopback servers that could be exploited by a malicious UWP application (because Microsoft for some reason expected UWP to become as commonplace as web-applications and wanted to pre-emptively make the platform super-secure at the cost of usability - stupid stupid stupid IMO). – Dai Jul 28 '20 at 01:02
0

You can try this example on how to connect to sql server in uwp app, and try to do the same steps on your client pc, hope it works: https://learn.microsoft.com/en-us/windows/uwp/data-access/sql-server-databases#first-set-up-your-solution

Sizar John
  • 11
  • 2
0

I tried everything I could find online, change sql server firewall settings, sql server tcp/ip settings, change sql server browser firewall settings, none of them worked for me.

I did read someone saying about turn on loopback capabilities in UWP, but I didn't see that option for the targeted window version I had. In the end, I finally got it working by running the following command

checknetisolation loopbackexempt -a -n=YourAppPackageName

enter image description here

Tracy Zhou
  • 714
  • 1
  • 7
  • 11